Is SQL Compatibility mode a factor?

A few days ago I posted a problem I am having with push, one way, transactional replication failing to replicate updates to the subscriber. The identity field is set to Not For Replication on the subscriber and the inserts work, however, my updates are fa
iling with a Cannot Update Identity column. On the publisher, the stored procedure is not updating the identity field so I am at a loss as to why I am getting this error.
Today I had a thought, both the publisher and subscriber are running these databases in 65 compatibility mode. The distribution database is running in 80 compatibiltity mode. Could this be the problem? We are running SQL 2000 standard, but until we can re
visit all of our code we are forced to stay in this compatibility mode.
For some reason the update Stored Procedure that was created by the replication Wizard creates the following line
if substring(@.bitmap,1,1) & 1 = 1
which is validating as true, then it tries to specifically update the identity field. If this were to be false I am sure the update would succeed. What is being stored in this @.bitmap variable?
Now I am really confused.
I edited the one update stored procedure created by the replication wizard that gets called when an update occurs on the table in question on the publisher. For each part of the IF Statement (if substring(@.bitmap,1,1) & 1 = 1) I added an Insert to insert
a value into a test table to determine which part (True or False) was being ran during the replication update. The false part is being ran, which means it will never try to update the identity field, however, replication is throwing an error stating it ca
nnot update the identity field. Why is SQL doing this? Is this a bug?
|||if you are replicating to a sql server using the dbcompatibilty mode of 6.5
you should
1) configure a DSN to the SQL Server
2) enable the subscriber through the DSN
3) configure your publisher to replicate to hetergeneous subscribers
4) create a push subscription to the DSN.
"Denny" <anonymous@.discussions.microsoft.com> wrote in message
news:A9D577AF-B8B4-437D-B8DA-60749358BDF7@.microsoft.com...
> Now I am really confused.
> I edited the one update stored procedure created by the replication wizard
that gets called when an update occurs on the table in question on the
publisher. For each part of the IF Statement (if substring(@.bitmap,1,1) & 1
= 1) I added an Insert to insert a value into a test table to determine
which part (True or False) was being ran during the replication update. The
false part is being ran, which means it will never try to update the
identity field, however, replication is throwing an error stating it cannot
update the identity field. Why is SQL doing this? Is this a bug?
|||Denny,
Look at the table on the publisher/subscriber and verify that the identity
column on
the table has the NOT FOR REPLICATION option set. Based on the error, I
would say it does not. Try turning this on and see if that resolves the
problem.
Regards,
Paul
|||Paul,
Thank you for your reply. The tables do have the Not For Replication setting set for the identity fields. The inserts work just fine however the update is failing. The funny thing is the update SP (sp_MSupd_RezStationLog) has two different updates that ca
n be called depending on the a value of "if substring(@.bitmap,1,1) & 1 = 1". If true it tries to update the identity column along with the other fields. If false it only tries to update the other fields (not the identity column). The expression is evaluat
ing to false so I'm not sure why it's generating that error being it's not trying to update the identity column. Even thought the true part is not being called I commented out the line that updates the identity field for the true part and everything works
as it should. Sounds like a bug to me, but I'd expect everyone else would have this problem too.
Hilary,
On our test systems I changed the compatibility mode to 80 and I am still getting the same error. Should I still try the ODBC method or does this sound like some other problem?
Thank you,
Denny
|||The databases on both servers is set to DBCompatibility mode 6.5.. Since this is all being done on test servers I went ahead and changed both of them to DBCompatibility mode 8.0 via EM. I bounced the SQL service on both servers and am still getting the sa
me error.
|||Hi,
I have the same problem. I am sure the Publishing DB table has the Not for replication option, so does the subscribing DB table.
Insertion is replicated, but updates cannot be replicated because of "Identity" column problem.
I manually synchronize the published DB and apply the scripts generated by sp_scriptpublicationcustomprocs to the subscribing DB.
Please help!!
Charles
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Charles,
I can't see the original post so I'm a little blind on this one. It seems
that you are using Transactional replication but what I really need is the
error message and the update that causes the error.
Just a guess but if this update affects a unique/clustered index, it may be
replicated as a delete/insert pair and for singleton updates a trace flag
can be used to avoid the issue
(http://support.microsoft.com/default...microsoft.com
:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1).
Regards,
Paul Ibison