Primary Keys and Identity Columns
-
I wonder if anyone can help.
I've spent the weekend setting up Transactional Replication and, in the main, successful, although I have 2 outstanding points:
1. Primary Keys: Having ensured that all tables on the Publisher have PKs (essential) I replicated to a Subscriber and all appeared good. If I use Enterprise Manager (EM) on the Publisher and "Design Table", the PK is shown as a yellow key. This is very useful to ensure that one is there, and to differentiate it from other indexes. However, on the Subscriber, EM doesn't show anything, although "Manage Indexes" shows it to be there. When I then create one in EM, it creates a second index. There is no difference in the Key construction (using Edit SQL).
2. Identity Columns: I ensured that these are all "Yes (Not For Replication)" on the Publisher. Following the MS instructions, I created a script for these tables for the Snapshot and indeed all went well "at the other end" - Yes (Not For Replication) passed over to the Subscriber. Data from my Applications on the Publisher is replicated nicely for Insert and Delete operations but fails on Update with tables containing Identity columns, even though I am not updating that column. I have read about 2 solutions which have drawbacks: a. Remove the lines that update the Identity column in the generated Stored Procedures (sp_MSupd_HMS_Call on the Subscriber). eg {set "col1" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "col1" end} where col1 is the Identity column. This is naff, as surely the Transactional Replication process should recognise that it's not being updated. b. Change the property of the Subscription Identity columns to just int. This is unsatisfactory, as it would need to be changed back to "Yes (Not For Replication)" in a role reversal scenario.
Any help would be appreciated.
Thanks,
Andrew
-
Andrew
1. The terms "primary key" and "index" are not synonymous. A primary key is a constraint on a column or group of columns such that the value(s) in the column(s) uniquely identifies the row it is in. SQL Server creates an index on the column(s) in a primary key: that index can be clustered (the default) so that the index is a physical ordering of the data according to the primary key, or non-clustered so that the index is an ordered set of pointers to the data. Any purists reading this, please forgive the over-simplification. It seems that the process of setting up replication has created an index but not a primary key constraint on the subscription tables.
2. If you want the subscription database to update its identity columns with the next value in the sequence (rather than specifying the number to insert into the identity columns) then you should not use the NOT FOR REPLICATION option. What that option does is to tell SQL Server to replicate the value from the publication table instead. If a non-replication user inserts a row into the table on the subscriber, the next identity value would be used. For more information, see the topic Using NOT FOR REPLICATION in Books Online.
Hope that helps
John
-
Thanks John,
Andrew
1. The terms "primary key" and "index" are not synonymous. A primary key is a constraint on a column or group of columns such that the value(s) in the column(s) uniquely identifies the row it is in. SQL Server creates an index on the column(s) in a primary key: that index can be clustered (the default) so that the index is a physical ordering of the data according to the primary key, or non-clustered so that the index is an ordered set of pointers to the data. Any purists reading this, please forgive the over-simplification. It seems that the process of setting up replication has created an index but not a primary key constraint on the subscription tables.
It would appear to be thus. I'd like to know how to make the process produce PK constraints.
2. If you want the subscription database to update its identity columns with the next value in the sequence (rather than specifying the number to insert into the identity columns) then you should not use the NOT FOR REPLICATION option. What that option does is to tell SQL Server to replicate the value from the publication table instead. If a non-replication user inserts a row into the table on the subscriber, the next identity value would be used. For more information, see the topic Using NOT FOR REPLICATION in Books Online.
I want the Subscriber to be identical to the Publisher. I don't want anyone to get their hands on the Subscriber, other than (read-only) Reporting. I just need the transactions from the Publisher to go into the Subscriber's tables, regardless of whether it's an Identity Column or not. I also want the Subscriber database to be usable as a role change should the Publisher go down. Hence, I wanted to keep the properties of all the columns identical.
-
Andrew
1. There's an option in the properties of the Snapshot Agent to run a script before and/or after the snapshot is run. I think the only way to do what you want is to script out your primary key constraints and run this script in the snapshot agent.
2. If you want the exact same values in the two tables then you should use the NOT FOR REPLICATION option. This will allow the actual values from the publisher to be inserted into the subscriber, overriding the identity property. Don't remove the identity column from the update statement in sp_MSupd_HMS_Call. Is it only updates that it's failing on, or inserts as well? An update to a PK column is treated as a delete followed by an insert, so if the stored procedure sp_MSdel_HMS_Call doesn't exist or has been "tampered" with then that may be the explanation. (Why would you want to be updating a value in an identity column, anyway?) Please post any error messages you get and I or somebody else will try to help you.
John
-
Thanks John.
1. I'll experiment with that. It looks like a good thing to try. Thanks
2. In my original post I made the point that I was not updating the Identity Column in the update query. Other columns are being updated, but the self-generated Stored Procedure seems to want to update that column as well. Very peculiar binary SQL is used for the process, and the distribution database table MSrepl_errors shows {CALL sp_MSupd_TABLE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,193,0x00008000)} followed by Cannot update identity column 'col1' where col1 is the Identity column. The 5 is the value to be updated in one of the cols, and the 193 is the value in the Identity column. The only way to "make it work" is to delete the offending line update "HMS_Call" set "col1" = case substring(@bitmap,1,1) & 1 when 1 then @c1 else "col1" end which I'm not very happy about. Inserts are OK.
-
Andrew
You're not going to like this, but I'm afraid it looks like your only choice is to hack your stored procedure and remove the identity column from it. This seems to be a "design feature". If you never update the identity values, then this will be safe. Looking at how mine is set up, I see that I have commented out the identity column line from the update SP. But then my rows are never updated at all in that table - just inserted. I don't know whether it's the same in SQL Server 2005.
John
-
Oh well. Thanks for all your efforts, John.
This feature does seem peculiar
I'll comment it out (luckily there are only 3 tables involved in this particular database (so far)) and make a note for myself... One good thing I suppose is that a separate Stored Procedure is created for each table, so can easily be checked out. I would never update an Identity column in a process. That exercise is something you would do manually if it's usage were in some way changed, or you wanted to get rid of "holes" for your own satisfaction...Cheers,
Andrew
-
If you are having issues with updating rows that have IDENTITY columns here's something you can do:
•Get the script from MASTER db for the stored proc sp_scriptdynamicupdproc
•Compile the stored proc on the publisher DB
•Get the article-Id from sysarticles for the table in question.
•Execute the stored proc "EXEC sp_scriptdynamicupdproc @artid = "
•The stored proc will generate a script for “update” procedure.
•Compile the generated script on the subscriber database.
This basically generates the same sp_MSupd_table stored proc that you have on subscriber but it removes the section where the identity column is being updated. This is not a hack and is suggested by an article from MS.
Also you can write up some dynamic sql to generate the stored procs for all articles that have identity columns rather than script out for each table and compile it on subsctiber.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Thanks Dinakar,
I'm alright for the first 5 steps, but fail abysmally on the 6th
If I understand you, I need to run the generated script from step 5 in SQL Query Analyser on the subscriber DB in question. This is what I get:
if exists (select * from sysobjects where type = 'P' and name = 'sp_MSupd_HMS_Call') drop proc [sp_MSupd_HMS_Call]
go
create procedure [sp_MSupd_HMS_Call]
@c1 datetime,@c2 int,@c3 varchar(25),@c4 varchar(50),@c5 varchar(50),@c6 int,@c7 int,@c8 varchar(5),@c9 varchar(50),@c10 varchar(50),@c11 int,@c12 varchar(5),@c13 varchar(25),@c14 varchar(1000),@c15 int,@c16 varchar(8),@c17 varchar(75),@c18 varchar(50),@c
,@bitmap binary(6)
as
declare @stmt nvarchar(4000), @spacer nvarchar(1)
select @spacer =N''
select @stmt = N'update [HMS_Call] set '
if substring(@bitmap,1,1) & 2 = 2
begin
select @stmt = @stmt + @spacer + N'[Call ref #]' + N'=@2'
select @spacer = N','
end
if substring(@bitmap,1,1) & 4 = 4
begin
select @stmt = @stmt + @spacer + N'[Service]' + N'=@3'
select @spacer = N','
end
etc etc
but this fails on execution:
Server: Msg 170, Level 15, State 1, Procedure sp_MSupd_HMS_Call, Line 3
Line 3: Incorrect syntax near ','.
Server: Msg 137, Level 15, State 1, Procedure sp_MSupd_HMS_Call, Line 8
Must declare the variable '@bitmap'.
Server: Msg 137, Level 15, State 1, Procedure sp_MSupd_HMS_Call, Line 13
Must declare the variable '@bitmap'. etc etc
What is this variable @bitmap I need to declare?
Maybe I'm doing this all wrong
-
Get the "Results to Text" and set the width to at least 1200. The stored proc gets trimmed off under normal results.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Hi Dinakar,
Fantastic
The sp generated perfectly, and more importantly, the table replicated perfectly. Interestingly, the resultant SQL is utterly different to the original.However, I have discovered a bug in the process when a table contains a Formula:
There are 2 sections to the stored procedure:
1. A declaration of the variables and their attributes -
@c1 int,@c2 varchar(25) etc
2. The corresponding definitions of the columns - eg for c2:
if substring(@bitmap,1,1) & 2 = 2
begin
select @stmt = @stmt + @spacer + N'[Patients forename]' + N'=@2'
select @spacer = N','
end etc
These are married up at the end -
exec sp_executesql @stmt, N'
@1 int
,@2 varchar(25) etc, followed by -
',
@pkc1
,@c2 etc
If however one of the columns contains a Formula, then 1. ignores this column, and numbers the next (non-Formula) column as 1 more than the previous (non-Formula) column. 2. skips that variable. The Create procedure then fails. Eg supposing col 2 of a 6 column table is a formula. In 1. @c1,@c2,@c3,@c4,@c5 is declared, whereas it should be @c1,@c3,@c4,@c5,@c6. In 2. it correctly defines @1,@3,@4,@5 and skips @2
In my table, I have an Age column, which is calculated from getdate() and the "Date of Birth" column.
The automatically-generated sp from the Transactional Replication process is actually OK in this case.
-
Interesting find. Fortunately we dont have computed columns..The replication has gone through a major improvement in SQL 2005. We are currently upgrading to 2005 and I am testing the new T-Rep and it works amazingly nice without having to mess with dropping constraints/screwing up the IDENTITY columns. Infact, I restored the publisher copy on the subscriber and had my replication up and running in 15-20 mins and not having to even open a query analyzer.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply