I ran into the same issue.
Stored procedure that references a linked server...
I saw (here or in the knowledge base I forget now) that it is due to the transaction level and being a linked server.
The only way I could get it to work was to adjust the project options so that it would not use transactions in synch scripts.
I know that it was stated this would NOT be changed going forward, and I understand that.
Using a transaction for these types of changes is important so you don't leave your database in a potentially messed up unknown state.
HOWEVER, that being said, is there any way to make this easier for the end user?
Perhaps it could compare table names against the linked server list and warn the end user during the compare process that these sprocs, functions, whatever reference a linked server.
Due to that it will not be able to use a transaction to commit the changes.
Perhaps it could offer to do all the changes that are not related to linked servers (if I have 10 sprocs and 3 of them use linked servers it could do the 7 of the 10) ?
Then it could offer to go back, NOT use transactions, and update the database for each of the sprocs or functions that use a linked server. Performing the update one by one and appending it to the saved script file in some way.
Basically I'd like to be able to make the change in one "session" so to speak so that when I review the change script I can see that all these changes were made.
However, perhaps SQL Compare could perform each change one by one without transactions enabled and then notify if one of the changes fails and then offer to abort the process (or mark it as failed and continue... which wouldn't seem overly wise though I suppose).
My current work around is to have two versions of the project one with "Do Not use transaction in synchronization scripts" checked and the other not checked (and a comment behind each to say which is which).
Anything to streamline this into a single project and provide a script history for documentation purposes would be great.
(Perhaps SQL Compare could offer to backup the schema of the object being changed so that in case the change fails it can roll back the structure at least?)
Not sure if that makes sense, but as I said, anything to help integrate this issue into the natural flow of the program would be greatly appreciated.
I can't imagine this is a rare problem as so many people appear to use the Linked Servers feature of SQL Server.
Thank you. / comments
- Community
- SQL Compare Previous Versions
- Testing Sql Compare, [3910] Transaction context in use by ..
I ran into the same issue.
Stored procedure that references a linked server...
I saw (here or in the knowledge base I forget now) that it is due to the transaction level and being a linked server.
...
0 votes
