Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Replication (2008)  Issues with sp_AddScriptExec

Author  Topic  wdkirby Starting Member 1 Post Posted - 2011-03-05 : 14:54:34 Migrating DDL and DML changes through a multi tiered Merge replication. I think I have found an issue with the merge replication maintaining its order of operations when using the Store procedure SP_AddScriptExec. My upgrade requires multiple DDL , DML, and then more DDL changes to remove dependent references. The order of the changes must stay in sink. I have compiled a script placing the sp_addscriptexect commands in execution order along with my DDL changes. The upgrade on the server goes fine however if I have a re-publisher that is not replicating at the time of the upgrade it appears all of the SP_addscriptexec dml scripts are applied first and then the DDL changes are grouped together. This is evident due to the replication errors I am receiving related to the inability to complete the DDL changes due to no data existing in a field when the script tries to alter the column to no longer except NULLs. When I started using sp_addscript exec in SQL Server 2000 I had read a Microsoft article clearly stating the scripts added to a publication will be executed in order with other updates. I am now using SQL Server 2008 r2 and the functionality does not appear to work the same way. Has anyone seen where the functionality of SP_Addscriptexec has changed in SQL Server 2008 r2? Does anyone have any suggestions on how to apply ddl and dml changes in a particular order when upgrading a multi tiered merge replication environment?