Suggestion : pull linked server queries out of transaction
AaronBertrand
Posts: 62 New member
I use linked server queries quite a bit, but in my dev environment, I often trip on the failure where I can't start a distributed transaction in order to save changes to the procedure (or to create a new procedure) within the transaction. Unfortunately I don't have enough control over all of my deployment environments to dictate that DTC is properly configured, and even in cases where I do, getting the change turned around through IT can take several days (and this always happens when the deployment was due an hour ago).
This means I need to manually pull those procedures out of the script, and execute them separately outside of the transaction scope. To maintain proper dependency mapping, I then sometimes have to re-alter other procedures that might be calling new procedures that couldn't be created in the right order, or procedures that were altered to now call these other procedures. Kind of a bummer when this happens, because the tool makes such easy work of synchronization otherwise.
A workaround would be to enable the option "Do not use transactions in synchronization scripts", however for me this is not a viable alternative because I still want the ability to roll back the script for the set of objects that *can* be created within the scope of the transaction without issue.
Ideally, when SQL Compare parses the text of an object, and detects a 4-part name (or follows a synonym that leads to a 4-part name), it should prompt me to perhaps consider synchronizing that object separately, or just generate a script with that object outside of the transaction (or generate multiple scripts), or place those within a comment that states "execute these scripts independently"). Perhaps that could be a configurable setting of which way it would handle that case, and of course if I know that all of my deployment environments support DTC completely, I can just turn them off.
This means I need to manually pull those procedures out of the script, and execute them separately outside of the transaction scope. To maintain proper dependency mapping, I then sometimes have to re-alter other procedures that might be calling new procedures that couldn't be created in the right order, or procedures that were altered to now call these other procedures. Kind of a bummer when this happens, because the tool makes such easy work of synchronization otherwise.
A workaround would be to enable the option "Do not use transactions in synchronization scripts", however for me this is not a viable alternative because I still want the ability to roll back the script for the set of objects that *can* be created within the scope of the transaction without issue.
Ideally, when SQL Compare parses the text of an object, and detects a 4-part name (or follows a synonym that leads to a 4-part name), it should prompt me to perhaps consider synchronizing that object separately, or just generate a script with that object outside of the transaction (or generate multiple scripts), or place those within a comment that states "execute these scripts independently"). Perhaps that could be a configurable setting of which way it would handle that case, and of course if I know that all of my deployment environments support DTC completely, I can just turn them off.
Comments
I'd be curious to hear from anyone else who is experiencing similar issues.
David Atkinson
Red Gate Software
Product Manager
Redgate Software
In our case, we have non-DBAs generating the build scripts, so I would prefer an option to exclude *just* the code with 4-part naming convention from synch scripts. Ideally, the excluded objects would appear in a list so that non-transactional synch scripts could be generated for them separately.