| Groups | Blog | Home |
sql server replication : Column name or number of supplied values...
I think you need to either update your replication stored procedures for the
newly added pk, or update the tables at the subscriber. I think the best thing to do would be to generate a new snapshot and redistribute it. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Peter" <palace@internode.on.net> wrote in message news:uFcN5QM7GHA.1252@TK2MSFTNGP04.phx.gbl... > Greetings > > Appologies in advance as this is a bit of a newbie question as I have just > started playing with 2005 replication. > > I am getting the error > Column name or number of supplied values does not match table definition. > > I began ok by setting up replication between 2 servers on the same > network. It replicated some data across ok but I noticed that some tables > did not replicate. When I looked deeper at the articles it said that they > can't replicate because they didn't have a primary key. Ok so I modified > the table in the GUI and added the primary key (it is an identity column) > and then the replication error appeared for the stored procedure that adds > entries to that table. > > I then did some looking around and found others had had similar problems. > It looks like I went about it the wrong way (newbie.newbie), so I thought, > ok I'll make sure all the primary keys are there first and then replicate. > I did this by deleting the subscriptions and disabling Pub&Dist, added the > PK and setup replication again. Error appears again. > > I have tried numerous ways to "clean up" the Publisher/Distributor data > and remove the replication. While it all looks like it has been removed, > if I setup the replication again it seems to "know" about the previous > problem and my error message keeps appearing. I have even completely > removed the database I am replicating from the publisher and subscriber > machines, have used the procedure in the BOL to disable Pub & Dist > (sp_removedbreplication,sp_dropdistpublisher,sp_dropdistributiondb,sp_dropdistributor), > and done a full restore of the offending database, however next time I > setup the replication I get the same error. > > I maybe off track and identity columns are a no-no? > > Anyway sorry for the long winded story. Any help would be creatly > appreciated. I am thinking of doing a compete SQL Server reload as it is > only a test system, but if I don't have to..... > > Cheers > Peter Lock > PalaceSoftware, Canberra OZ >
Hi Hilary
Thank you for the prompt reply. I had already tried numerous new snapshots and reloads, however I think I have worked out where the problem is (at least it now replicates all articles without error). It looks like lazy mans T-SQL is the problem. I had a table with 2 columns ID (tinyint) as identity and ComputerName as varchar(50). I set the PK in ID (which I didn't have before because I didn't need it), and my stored procedure for adding a new entry was INSERT Computers VALUES (@ComputerName) Once I changed my stored procedure to INSERT Computers (ComputerName) VALUES (@ComputerName) the errors disappeared. I'll give it a good test tonight and see how it goes. Cheers Peter Lock PalaceSoftware, Canberra OZ [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uA%23LjfN7GHA.2288@TK2MSFTNGP05.phx.gbl... >I think you need to either update your replication stored procedures for >the newly added pk, or update the tables at the subscriber. I think the >best thing to do would be to generate a new snapshot and redistribute it. > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "Peter" <palace@internode.on.net> wrote in message > news:uFcN5QM7GHA.1252@TK2MSFTNGP04.phx.gbl... >> Greetings >> >> Appologies in advance as this is a bit of a newbie question as I have >> just started playing with 2005 replication. >> >> I am getting the error >> Column name or number of supplied values does not match table definition. >> >> I began ok by setting up replication between 2 servers on the same >> network. It replicated some data across ok but I noticed that some tables >> did not replicate. When I looked deeper at the articles it said that they >> can't replicate because they didn't have a primary key. Ok so I modified >> the table in the GUI and added the primary key (it is an identity column) >> and then the replication error appeared for the stored procedure that >> adds entries to that table. >> >> I then did some looking around and found others had had similar problems. >> It looks like I went about it the wrong way (newbie.newbie), so I >> thought, ok I'll make sure all the primary keys are there first and then >> replicate. I did this by deleting the subscriptions and disabling >> Pub&Dist, added the PK and setup replication again. Error appears again. >> >> I have tried numerous ways to "clean up" the Publisher/Distributor data >> and remove the replication. While it all looks like it has been removed, >> if I setup the replication again it seems to "know" about the previous >> problem and my error message keeps appearing. I have even completely >> removed the database I am replicating from the publisher and subscriber >> machines, have used the procedure in the BOL to disable Pub & Dist >> (sp_removedbreplication,sp_dropdistpublisher,sp_dropdistributiondb,sp_dropdistributor), >> and done a full restore of the offending database, however next time I >> setup the replication I get the same error. >> >> I maybe off track and identity columns are a no-no? >> >> Anyway sorry for the long winded story. Any help would be creatly >> appreciated. I am thinking of doing a compete SQL Server reload as it is >> only a test system, but if I don't have to..... >> >> Cheers >> Peter Lock >> PalaceSoftware, Canberra OZ >> > >
Greetings
Appologies in advance as this is a bit of a newbie question as I have just started playing with 2005 replication. I am getting the error Column name or number of supplied values does not match table definition. I began ok by setting up replication between 2 servers on the same network. It replicated some data across ok but I noticed that some tables did not replicate. When I looked deeper at the articles it said that they can't replicate because they didn't have a primary key. Ok so I modified the table in the GUI and added the primary key (it is an identity column) and then the replication error appeared for the stored procedure that adds entries to that table. I then did some looking around and found others had had similar problems. It looks like I went about it the wrong way (newbie.newbie), so I thought, ok I'll make sure all the primary keys are there first and then replicate. I did this by deleting the subscriptions and disabling Pub&Dist, added the PK and setup replication again. Error appears again. I have tried numerous ways to "clean up" the Publisher/Distributor data and remove the replication. While it all looks like it has been removed, if I setup the replication again it seems to "know" about the previous problem and my error message keeps appearing. I have even completely removed the database I am replicating from the publisher and subscriber machines, have used the procedure in the BOL to disable Pub & Dist (sp_removedbreplication,sp_dropdistpublisher,sp_dropdistributiondb,sp_dropdistributor), and done a full restore of the offending database, however next time I setup the replication I get the same error. I maybe off track and identity columns are a no-no? Anyway sorry for the long winded story. Any help would be creatly appreciated. I am thinking of doing a compete SQL Server reload as it is only a test system, but if I don't have to..... Cheers Peter Lock PalaceSoftware, Canberra OZ
OK, I take it the errors were occurring on the publisher side, and in this
case were probably not replication related. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Peter" <palace@internode.on.net> wrote in message news:ugK3chP7GHA.3452@TK2MSFTNGP05.phx.gbl... > Hi Hilary > > Thank you for the prompt reply. I had already tried numerous new snapshots > and reloads, however I think I have worked out where the problem is (at > least it now replicates all articles without error). It looks like lazy > mans T-SQL is the problem. > > I had a table with 2 columns ID (tinyint) as identity and ComputerName as > varchar(50). I set the PK in ID (which I didn't have before because I > didn't need it), and my stored procedure for adding a new entry was > > INSERT Computers VALUES (@ComputerName) > > Once I changed my stored procedure to > > INSERT Computers (ComputerName) VALUES (@ComputerName) > > the errors disappeared. > > I'll give it a good test tonight and see how it goes. > > Cheers > Peter Lock > PalaceSoftware, Canberra OZ > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:uA%23LjfN7GHA.2288@TK2MSFTNGP05.phx.gbl... >>I think you need to either update your replication stored procedures for >>the newly added pk, or update the tables at the subscriber. I think the >>best thing to do would be to generate a new snapshot and redistribute it. >> >> -- >> Hilary Cotter >> Director of Text Mining and Database Strategy >> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >> >> This posting is my own and doesn't necessarily represent RelevantNoise's >> positions, strategies or opinions. >> >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> >> >> "Peter" <palace@internode.on.net> wrote in message >> news:uFcN5QM7GHA.1252@TK2MSFTNGP04.phx.gbl... >>> Greetings >>> >>> Appologies in advance as this is a bit of a newbie question as I have >>> just started playing with 2005 replication. >>> >>> I am getting the error >>> Column name or number of supplied values does not match table >>> definition. >>> >>> I began ok by setting up replication between 2 servers on the same >>> network. It replicated some data across ok but I noticed that some >>> tables did not replicate. When I looked deeper at the articles it said >>> that they can't replicate because they didn't have a primary key. Ok so >>> I modified the table in the GUI and added the primary key (it is an >>> identity column) and then the replication error appeared for the stored >>> procedure that adds entries to that table. >>> >>> I then did some looking around and found others had had similar >>> problems. It looks like I went about it the wrong way (newbie.newbie), >>> so I thought, ok I'll make sure all the primary keys are there first and >>> then replicate. I did this by deleting the subscriptions and disabling >>> Pub&Dist, added the PK and setup replication again. Error appears again. >>> >>> I have tried numerous ways to "clean up" the Publisher/Distributor data >>> and remove the replication. While it all looks like it has been removed, >>> if I setup the replication again it seems to "know" about the previous >>> problem and my error message keeps appearing. I have even completely >>> removed the database I am replicating from the publisher and subscriber >>> machines, have used the procedure in the BOL to disable Pub & Dist >>> (sp_removedbreplication,sp_dropdistpublisher,sp_dropdistributiondb,sp_dropdistributor), >>> and done a full restore of the offending database, however next time I >>> setup the replication I get the same error. >>> >>> I maybe off track and identity columns are a no-no? >>> >>> Anyway sorry for the long winded story. Any help would be creatly >>> appreciated. I am thinking of doing a compete SQL Server reload as it is >>> only a test system, but if I don't have to..... >>> >>> Cheers >>> Peter Lock >>> PalaceSoftware, Canberra OZ >>> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |