Column name or number of supplied values...

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_dr opdistributiondb,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
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_dr opdistributiondb,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
"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...
>
|||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
"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...
>