Suggestion : pull linked server queries out of transaction

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.

Comments

  • Could this simply be solved if we added an additional option to exclude these 4-part objects from the synchronization script?

    I'd be curious to hear from anyone else who is experiencing similar issues.

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
  • I'd actually prefer the error message. If the object is simply left out of the synchronization script, then it makes it hard for me to realize that it has been left out. Sometimes there is a four-part name hidden deep in the bowels of a stored procedure and it's not always going to be obvious that this object is being left out for that reason.
  • We experience problems with this quite a bit, since we have linked servers all over the place.

    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.
  • What's the word on this issue? I haven't run into myself but I just saw someone hit it over on AskSSC. Manually editing the transaction type "solved" the problem, but I'd be interested in seeing if something else was going on to provide better solutions within the tool.
Sign In or Register to comment.