IDENTITY issues on replicated table.

Question

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

Answer 1

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?