August 29, 2008 at 8:27 am
I have used broker services to create an asynchronous trigger (AS) within a database. On the receiving end, I can insert records into tables with no problem. However, attempting to send email via database mail, or shell out with xp_cmdshell doesn't work. I've isolated attempts to do both of these to separate stored procedures. When the receiving end of the AS attempts to execute these stored procedures, the stored procedures are executed, but the attempts to send email or shell out fail (without obvious error). When I execute the stored procedures by hand, outside the scope of the AS, they execute as intended.
August 29, 2008 at 11:06 am
What is the execution context (security principal) of your AS? And does that execution context have the rights to use xp_CmdShell(needs SysAdmin normally) and Database Mail?
[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]
August 29, 2008 at 11:47 am
This is being done now on a Dev server where I am logged in as a sysadmin and all procedures run under the context of my credentials. I thought about it potentialling being an issues with credentials, but don't see how that is possible when I caded all the procs the same (inlcuding the AS subscription proc) and all as "me".
August 29, 2008 at 12:04 pm
Can you post the Procs?
[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]
August 29, 2008 at 12:54 pm
Actually, I think that I have know what this is: If you are using the "EXECUTE AS.." clause in your queue's activation definition, then you should be aware that it is only valid with the current database. From BOL:
However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.
Since a Service Broker Queue is a database-scoped object, that means that the EXECUTE AS clause is only good within the database. Outside of the database, it is treated as though it were [Public]. To fix this, try using the EXECUTE AS LOGIN statement in your procedure.
[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]
August 29, 2008 at 12:55 pm
I have attached the significant files in a zip archive.
AsyncTriggerTest2.sql setup the broker serviuce and tests functionality with a testing table.
TestEmail.sql calls the AS (and does pass the appropriate data).
AsyncTriggerProc.sql despools from the broker service (the references to the audit table all over the place are for debugging). Email is added to a table and processed in batches by an external vbs script (calls to Database Mail have been removed because it wasn't working).
ProcessEmailBatch.sql is called by AsyncTriggerProc. This does occur based on Audit table updates. It should sheel out to the batch file / VBS script, but does not (the audit.txt file updated any time the batch is run is not updated, indicating that the batch doesn't run!).
The script (SendEmail.vbs) has been included, but is irrelavant to the issue, since it is never called).
Once AsyncTriggerProc populates the Email2Send table (and it does - all table DML calls succeed), even though the AS cannot successfully call ProcessEmailBatch and shell out successfully, if I execute it myself from the SSMS it works. Also, if I creae a process to run periodically (what I'm trying to avoid doing by using anAS in the first place) it works as well.
Thanks.
August 29, 2008 at 12:57 pm
The Queue definition is the only one with execute as and it is "OWNER", which is me, the sysadmin (although, it is in the master DB), so I'll look into that.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply