Transactions in procedure definition in template script break clone creation
Hi,
we are enrolling the tsqlt framework as one part of our templates. All necessary scripts are packed into a single sql file.
This worked well until we updated to the current version.
Now, the first objects are created without problems, every GO in the script is handled correctly, in the SQL Server Profiler every script block is registered as a separate query. From a specific object definition on, all following GOs are taken out of the script, the script following that object is executed as a single command, resulting in many errors. If I take that object ath the beginning of the script block out of the script, the execution works fine until the next problematic object definition is found. For tsqlt, there are erroneous two objects, those are the only ones in the entire script that handle transactions. Playing around with the object definition, I found out that a "BEGIN TRAN(SACTION)" seems to be the problem.
Has anything with the template handling changed that makes it impossible to include object definitions with transactions? Is this a bug, or intended behavior? An example definition would be tsqlt.Fail.
we are enrolling the tsqlt framework as one part of our templates. All necessary scripts are packed into a single sql file.
This worked well until we updated to the current version.
Now, the first objects are created without problems, every GO in the script is handled correctly, in the SQL Server Profiler every script block is registered as a separate query. From a specific object definition on, all following GOs are taken out of the script, the script following that object is executed as a single command, resulting in many errors. If I take that object ath the beginning of the script block out of the script, the execution works fine until the next problematic object definition is found. For tsqlt, there are erroneous two objects, those are the only ones in the entire script that handle transactions. Playing around with the object definition, I found out that a "BEGIN TRAN(SACTION)" seems to be the problem.
Has anything with the template handling changed that makes it impossible to include object definitions with transactions? Is this a bug, or intended behavior? An example definition would be tsqlt.Fail.
Tagged:
Best Answer
-
ChrisHurley
Posts: 54 Silver 2
Just to follow up on this, we're working on the solution to this but it didn't quite make it in for our release today (3.1.6). However, we're aiming to release a fix for this soon in 3.1.7.
Answers
I can confirm that a fix that we did recently for another issue (in SQL Clone 3.1.2) has resulted in some problems with handling of batches in transactions - apologies for the inconvenience. We'll aim to get that sorted out for our next release.
Hi Chris,
thanks for your answers, it is good to know a fix is on the way, we can use a workaround until then!
Redgate Software