July 21, 2008 at 12:55 pm
I am developing a set of DDL Triggers for the management and replication of DDL statements to multiple sql servers at once. I am using SQL Server 2005.
It works like this :
0. An ALTER PROCEDURE script is executed, it is picked up by the DDL trigger.
1. Grab eventdata
2. ROLLBACK the trigger transaction(it is rolled back to end the transaction because if it is left open the linked server in step 3 causes the transaction to escalate to a distributed transaction and I don't want this.)
3. Send the event sql to a central server
4. The central server relays the sql to the target clients
5. Now back in the trigger, a BEGIN TRANSACTION is called so that the trigger ends with an open transaction. This prevents this warning from being caused:
Msg 3609, Level 16, State 2, Procedure Test_Procedure, Line 22
The transaction ended in the trigger. The batch has been aborted.
Normal DDL statements work fine.
The problem is that when I'm making changes in SSMS using the table design tool, the ROLLBACK and subsequent BEGIN TRANS statements are causing this error:
'TEST_TABLE' table
- Unable to modify table.
This SqlTransaction has completed; it is no longer usable.
After hitting OK on this error notification window, another warning window spawns displaying :
User canceled out of save dialog
(MS Visual Database Tools)
However, the sql statement(s) that SSMS spawns DO run to completion, the table is modified on all servers.
It is clearly an issue with the ROLLBACK because this error doesn't occur if I comment that command out.
I have no idea how to program around this, right now I'm assuming that SSMS is expecting a certain RETURN value... but apparently, I can not return a specific integer('RETURN 0') from a trigger in order to test my theory as I receive this failure message :
Msg 178, Level 15, State 1, Procedure DDL_Trigger, Line 110
A RETURN statement with a return value cannot be used in this context.
Does anyone have ANY idea on a cause or solution? Even if you have a theory please let me know.
July 21, 2008 at 5:01 pm
My suspicion is that SSMS is keeping track of the transaction identities or else is receiving notifications for the transactions that it creates and thus knows that you have canceled (rolled-back) its transaction. If that is the case, then I doubt that there is a way around it using your rollback trick.
What I would suggest, is that you use Service Broker in step #3 to disassociate the source trigger. This way when you SEND to the central server, you will not get a distributed transaction that encompasses SSMS or your source object.
Alternatively, you could use Event Notifications instead of DDL Triggers, which would accomplish the same thing.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply