December 15, 2008 at 8:04 am
What process is doing the insert? If you control the code doing the insert I would recommend creating the XML document within the application code, not in SQL Server.
It would not suprise me if the issue is that the user in the insert context does not have rights to execute xp_cmdshell. from BOL:
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
I would not do this in a trigger. Doing anything that requires resources outside the database within a trigger is usually a recipe for disaster. I would recommend looking in to Service Broker or creating your own queue tables and a schedule djob or windows service that reads the queue table(s) and creates the XML document.
Please check out this article[/url] on triggers.
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 post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply