question

chrisrdba avatar image
0 Votes"
chrisrdba asked chrisrdba commented

IDENTITY issues on replicated table.

Greetings. Today we did an application/ DB upgrade (tables/ sprocs/ etc) in a non-prod environment that is replicated. One of the goals was to see if the upgrade would tank replication or not. Once complete, I have the message below in replMon.

Upon seeing the error, I realized that myTable really doesn't need to be replicated anyways, so I remove it from both the Subscription and as an Article. I also go into the table and change the NOT FOR REPLICATION value to NO. This make it match another environment we did the same upgrade on recently that wan't configured with replication.

However, the issue persists. What can be done at this point without rebuilding replication?

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0015B8A4001502C6001000000000, Command ID: 2)
Error messages:
Error occured in myTrigger
ERROR_NUMBER: 50000; ERROR_SEVERITY: 16; ERROR_PROCEDURE: dbo.mySproc; ERROR_LINE: 179; ERROR_MESSAGE: Explicit value must be specified for identity column in table 'myTable' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 50000)
Get help: http://help/50000
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back. (Source: MSSQLServer, Error number: 3616)
Get help: http://help/3616


sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered chrisrdba commented

What do you mean with:

I also go into the table and change the NOT FOR REPLICATION value to NO.

I don't think you can do that. That is, I don't think you can use ALTER TABLE to remote NOT FOR REPLICATION. Instead you have to create a new table and copy data over.

Now, you are using a language like a table was some sort of GUI object with checkboxes, but it isn't. There is the Table Designer in SSMS which may make you think it is, but this is a dangerous tool. It can implement the rename-create-new-and-copy-over dance for you, but it is 1) very buggy 2) it may use it, even when ALTER TABLE would do the job. If you use it, always get the script and review the script carefully. They do some things very wrong there.

Anyway, on which instance did you make this change? Publisher or subscriber?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So super dumb move on my part...

My theory was that since I removed the table from replication, the whole thing should quit trying to replicate and quit throwing out the error. That didn't happen, so I made the change described on the Publisher via SSMS, which also didn't help.

First thing this morning I read your reply and it clicks I also needed to make the change on the Subscriber as well -- the issue goes away once that's done.

Apparently troubleshooting this DEV issue late on a Friday afternoon didn't result my my best efforts LOL.

It still seems to me like this shouldn't have been necessary once the table was dropped from the Publication, but oh well.

Thanks!

0 Votes 0 ·

Apparently troubleshooting this DEV issue late on a Friday afternoon didn't result my my best efforts LOL.

To avoid those kind of goofs, I tend to only work half-day on Fridays. :-)

It still seems to me like this shouldn't have been necessary once the table was dropped from the Publication, but oh well.

You masked some pieces of the error message, but I got the impression that the error comes from a trigger fired by a user-written procedure. Presumably that trigger writes to this table, and that trigger may fire as the result of another publication.



0 Votes 0 ·
chrisrdba avatar image chrisrdba ErlandSommarskog ·

That's a really good point -- both the Friday thing and the trigger thing. ;-)

Thanks again and have a good weekend!

0 Votes 0 ·