June 29, 2009 at 5:23 pm
I've found several articles on using the DDL Triggers in 2005 to capture Changes to the DB and I just tried implementing the take presented by Phil Factor & Robyn at Simple-Talk.com and I have once again met with failure, at least partially.
I can't figure out why the darn thing doesn't work right when the changes are made by the accounting application we use at my place of work. I know exactly what account the application is using and if I log into SSMS with that same account and make some change like a CREATE & DROP VIEW the trigger will fire as expected and write the info from EVENTDATA to the table. However when the application does this it fails.
The app has a feature where it creates user specific views and will re-create those when need be such as when a user is removed/added to the system. As soon as the thing starts up and issues the first DROP VIEW command the Trigger fires but then the application stalls when trying to create the View it just dropped. It errors out because the view never got dropped and I'm clueless as to why.
Anyone else had problems like this with DDL Triggers and has an idea on why this happens? Like I said if I imitate the app by logging in with the same user name & password (A SQL Login) everything works fine.
Thanks
Kindest Regards,
Just say No to Facebook!June 29, 2009 at 8:32 pm
I use DDL triggers on my databases here and they work great.
From what you posted, it sounds like there's something in your trigger that your application does not have permissions to do. For example, my DDL triger puts a record into a table and fires off an email each time it's run. If the user that's doing the DDL change doesn't have permissions to fire off an email (member of DatabaseMailUserRole in MSDB) the whole DDL trigger fails along with the DDL command. Albeit, I don't think any of our applications actually make DDL changes in code, this has worked great from management studio.
You could try setting up more verbose error logging or alerting to try and see what kind of error is being thrown. Have you tried running SQL Server Profiler while trying to run your app with the DDL change? It may capture the error you are trying to see.
Try these and post back here with your results.
June 29, 2009 at 8:54 pm
Could you post the DDL for your DDL triggers? Maybe we'll see something you missed.
June 30, 2009 at 8:15 am
npikes is probably correct that the application user does not have rights to do something you are doing in your DDL trigger, while when you do it in SSMS you do have rights.
It definitely would help to see the DDL of the DDL trigger as Lynn has asked.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2009 at 9:03 am
I used the code found at the Robyn Page & Phil Factor DDL Article at http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ddl-trigger-workbench/. I've included the link here instead of just re-pasting the code.
I did also try switching out the table logging part to sending an email via sp_Dend_DBMail and the results were the same. When I log into SSMS as the SQL User the app uses I can create & drop a VIEW (The only type of object I have tested this with) with no problem and I do get an email alert but when I let the application do the same it bombs out.
Thanks
Kindest Regards,
Just say No to Facebook!June 30, 2009 at 9:18 am
npikes
I use DDL triggers on my databases here and they work great.From what you posted, it sounds like there's something in your trigger that your application does not have permissions to do. For example, my DDL triger puts a record into a table and fires off an email each time it's run. If the user that's doing the DDL change doesn't have permissions to fire off an email (member of DatabaseMailUserRole in MSDB) the whole DDL trigger fails along with the DDL command. Albeit, I don't think any of our applications actually make DDL changes in code, this has worked great from management studio.
You could try setting up more verbose error logging or alerting to try and see what kind of error is being thrown. Have you tried running SQL Server Profiler while trying to run your app with the DDL change? It may capture the error you are trying to see.
Try these and post back here with your results.
Have you done any of this? Have you logged into SSMS as the application user and had the DDL trigger happen correctly?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 30, 2009 at 9:31 am
UPDATE: More Details Found
I have tested this by imittating the application by logging in under teh same SQL Login but what I did not test/do until just a few minutes ago was to reset my connection options from the default used by SSMS to match what the app uses and they do differe by a couple.
The cuplrit has been found and it's 3 SET OPTIONS that are preventing the thing from working. They are:
1) CONCAT_NULL_YIELDS_NULL
2) ANSI_WARNINGS
3) ARITHABORT
My accoutning program sets all of these to OFF and apparently all 3 must be set to ON else the DDL fails.
It would be nice to of know in advance that DDL had these SET OPTION requirements and if they are listed in BOL I did not see them.
Thanks to all who replied and sorry I wasted everyones time on this one.
Kindest Regards,
Just say No to Facebook!June 30, 2009 at 9:39 am
Well, thank you for the link, but it really isn't the same as showing us your actual code.
June 30, 2009 at 9:59 am
UPDATE #2:
I managed to get this work, at least partially. If I explcitly turn on the 3 SET OPTIONS (that my app turns off) within the definition of the Trigger itself (found this idea at another DDL web article) then I can get the DDL to work as it should. The only problem now is that the accounting app is now getting messages from SQL Server it doesn't know what to do with and so it thriws them up on the screen awaiting user input by clicking OK. This unfortyunately is still not a workable option because I can't have something that will send unexpected messages to the app else users may be get messages all the time. I'm assuming this is from the ANSI WARNINGS being set to ON.
Any thoughts on how to work-a-round this?
Thanks
Kindest Regards,
Just say No to Facebook!June 30, 2009 at 10:06 am
Could be. Problem is, we haven't seen your code.
June 30, 2009 at 10:10 am
YSLGuru (6/30/2009)
The only problem now is that the accounting app is now getting messages from SQL Server it doesn't know what to do with and so it thriws them up on the screen awaiting user input by clicking OK.
What messages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2009 at 10:24 am
YSLGuru (6/30/2009)
UPDATE #2:I managed to get this work, at least partially. If I explcitly turn on the 3 SET OPTIONS (that my app turns off) within the definition of the Trigger itself (found this idea at another DDL web article) then I can get the DDL to work as it should. The only problem now is that the accounting app is now getting messages from SQL Server it doesn't know what to do with and so it thriws them up on the screen awaiting user input by clicking OK. This unfortyunately is still not a workable option because I can't have something that will send unexpected messages to the app else users may be get messages all the time. I'm assuming this is from the ANSI WARNINGS being set to ON.
Any thoughts on how to work-a-round this?
Thanks
Turn off the Set options at the end of the trigger.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply