Custom Scripting in SQL Server 2000

Among the new features in SQL Server 2000’s snapshot replication utility is custom scripting. Here’s how it works. In the Snapshot tab of the Publication Properties dialog box, which Figure A shows, you can specify two .sql script files. The distrib.exe distributor command-line utility applies both script files to the subscription database. Distrib.exe applies the first script (the top script in Figure A) before yet another new script file named snapshot.pre, which the sch step follows. Then, the command applies the second script file (the bottom script in Figure A) right after the bcp step. Essentially, the new scripts offer the same results that you can accomplish by inserting steps before and after the Run agent step, as I described in the main article. In fact, because the new scripts are static script files instead of parameterized stored procedures, you have to rewrite them whenever the replication configuration changes and when they apply to one publication only (assuming again that each publication has its own Distribution Agent job). For performing custom tasks, I therefore recommend using Snapshot Agent steps instead of the pre- and postscripts unless the scripts are simple, static, and stable.

In addition, If the Delete all data in the existing table (with TRUNCATE) option is checked in an article’s Properties dialog box, the new snapshot.prescript file drops foreign key constraints for the destination table in the subscription database by executing a new stored procedure—sp_Msdropfkreferencingarticle—in the master database. Note that the Distribution Agent doesn’t restore these foreign key constraints by the end of the snapshot replication. The recreate step in my optimization restores all the key constraints and indexes.

Among the new features in SQL Server 2000’s snapshot replication utility is custom scripting. Here’s how it works. In the Snapshot tab of the Publication Properties dialog box, which Figure A shows, you can specify two .sql script files. The distrib.exe distributor command-line utility applies both script files to the subscription database. Distrib.exe applies the first script (the top script in Figure A) before yet another new script file named snapshot.pre, which the sch step follows. Then, the command applies the second script file (the bottom script in Figure A) right after the bcp step. Essentially, the new scripts offer the same results that you can accomplish by inserting steps before and after the Run agent step, as I described in the main article. In fact, because the new scripts are static script files instead of parameterized stored procedures, you have to rewrite them whenever the replication configuration changes and when they apply to one publication only (assuming again that each publication has its own Distribution Agent job). For performing custom tasks, I therefore recommend using Snapshot Agent steps instead of the pre- and postscripts unless the scripts are simple, static, and stable.

In addition, If the Delete all data in the existing table (with TRUNCATE) option is checked in an article’s Properties dialog box, the new snapshot.prescript file drops foreign key constraints for the destination table in the subscription database by executing a new stored procedure—sp_Msdropfkreferencingarticle—in the master database. Note that the Distribution Agent doesn’t restore these foreign key constraints by the end of the snapshot replication. The recreate step in my optimization restores all the key constraints and indexes.