Replication - PK & SP Question

If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
JLS,
you need to add PKs to tables only in the case of transactional replication. Adding an extra column to a table may cause issues with existing TSQL if it refers to the table without listing the columns - you have to look at individual cases, but insert into xxx select * from yyy where yyy is a table having had an extra column added will fail in this case. For this type of TSQL you'll need to change the syntax to use explicit column names.
Regards,
Paul Ibison
|||the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||"You only need the not for replication option when a replication process is going to be inserting an identity value"
I want to make sure I understand this, because I set all the tables in my publishing database which have identity columns to "Yes - Not for Replication".
I am doing one way transactional replication, so after reading the White Paper, I changed all my identity columns, and changed all my triggers to "Not for Replication" as well.
Did I not need to do the identity column bit? Is it correct that the rows from the publishing table will be inserted/updated to the subscriber table with no effect on the identity column, it will merely be updated or written?
As far as SP's, I don't have any inserts with
insert into table a
select * from table b
So this means I should not have any problems with SP's just because I added identity columns to be used as PK's, right?
I apologize if I seem to be asking the same questions of you, I just want to make sure I have it straight in my head before going to production.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:uiOJncIOEHA.2560@.TK2MSFTNGP11.phx.gbl...
the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||That is correct. You only need to put the not for replication switch on the publisher when a replication process is going to update the PUBLISHER and you are not using Immediate Updating susbcribers.
These cases are 1) republishing, 2) merge, 3) bi-directional transactional, 4) queued.
You will need them on the subscriber if you want to have the replication process maintain the identity ranges it is publishing to the subscriber, but the subscriber will maintain identity ranges when a user process (Ie anything but a replication process)
does the updates.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html