More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How to maintain IDENTITY property of a column on both publishing and subscribing databases in transactional replication?
If you maintain IDENTITY property on the subscribing table, distribution agent fails with the following error even after creating the publisher's IDENTITY property with NOT FOR REPLICATION option:
An explicit value for the identity column in table 't1' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Category: Data source
Source: <Server Name>
Number: 8101
Follow these steps to maintain IDENTITY property of a column on both publishing and subscribing databases:
1) Before subscribing, you must create the table at the subscriber (by default, replication will not transfer the IDENTITY property to the subscribers) and don't forget to create the IDENTITY property with NOT FOR REPLICATION option.
2) After creating the publication, go to publication properties window and choose the Articles tab. Click on the properties button (...) against the table that has IDENTITY property. Go to 'Snapshot' tab. Choose 'Keep the existing table unchanged'.
3) By default SQL Server 7.0 applies the transactions to the subscriber using stored procedures (these procedures will get created during the application of initial snapshot). Before you subscribe, you can choose to continue using stored procedures to apply the transactions on the subscriber OR you can choose to apply the transactions to the subscriber using SQL statements.
If you choose to use SQL statements, jump to step 6. If you choose to go by the default behavior (stored procedures) follow steps 4 and 5.
4) Go to the publication properties, choose 'Articles' tab, click on the properties button (...) against the table that has IDENTITY property, click on the 'Commands' tab and uncheck 'Create the stored procedures specified above during the initial synchronization of the subscribers'. Using sp_scriptinsproc, sp_scriptmappedupdproc, sp_scriptdelproc (See SQL Server 7.0 Books Online for more information) generate the scripts for these procedures.
Add a column list to the INSERT statement in the INSERT stored procedure script. In the UPDATE stored procedure script, remove the line in the UPDATE statement which tries to update the IDENTITY column. Apply these scripts on the subscriber.
5) Push or pull the subscription.
6) If you choose to use SQL statements to apply transactions to the subscriber, go to the publication properties window, choose articles tab, click on the properties button (...) against the table that has IDENTITY property, choose 'Commands' tab. Uncheck the check marks against the procedure calls and check the box against 'Use column names in the SQL statements'.
7) Push or pull the subscription.
(Continued on next question...)
