article for subscription that does not exist ?!?

Hi There

I hope someone can help me asap.

I need to alter a column on a replicated table.

So i execute sp_dropsubscription and sp_droparticle for the table for all publication to that article in the database.

Usually i just alter the table and then execute sp_adddarticle and sp_addsubscription afterwards and everything is cool.

However in this case after i drop the subscriptions for the article, when i try alter the table it says it is being replicated.

I query sysobjects and see that the table has replinfo = 1 , this is snapshot replication, but i only have transactional replication ? I used to have snapshot but that was dropped long ago.

I then query sysarticles and i find the table, however the pubid (publication id) for the table is equal to the publication id of another databases publication ? I also have found in sysarticles articles with a publication id's equal to publications that do not exist, for example articles will have pubid of 2 , but if i run sp_helppublication on all user databases there is no publciation with an id of 2 ?

Please help, even after i drop all subscriptions to an article it seems that sql server thinks the article belongs to another databases publciation or publications that no longer exist?

Maybe there is some sort of cleanup sp i can run or something but it seems to me sql server has gotten articles confused with old deleted publications that no longer exist.

Therefore after dropping all subscriptions i still cannot alter tables as sql server think it is still being published but it is not!

Thanx

if you don't mind dropping the publication (script it out before dropping it), you can clean everything up by executing proc sp_removedbreplication. Let me know if this doesn't work.|||

Hi

Unfortunately we have too many publications and they are too big , so that is not an option.

However i deleted the problem entry from sysarticles and everything worked after that.

Thanx

Arithmetic Overflow error

Can someone help me with this error:

I need to change the datatype of a column from int to tinyint.

I did this :

ALTER TABLE EmailTracking ALTER COLUMN URLID tinyint

and got this error:

Msg 220, Level 16, State 2, Line 1

Arithmetic overflow error for data type tinyint, value = 74860.

The statement has been terminated.

how do i fix this?

thanks

Rookie, perhaps you could try examining the maximum value on the data. Tinyint is only up to 255. If the maximum of your data is more than 255, perhaps you would consider smallint instead, which is up to 32,000. Int on the other hand, is up to 2 billion.
Also, please check for negative values. Tinyint does not allow negative values, but smallint and int do.
Vincent