September 8, 2008 at 1:17 pm
I am trying to execute a query (procedure) which is using a linked server(mentioned inside the stored procedure) and sends out a mail and works fine when I execute manually from Management Studio , but when it runs as a scheduled job and runs as the account under which SQL agent is running , it gives the following error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
SQL agent account is sysadmin on the current server and on the linked server mentioned in the query , so it's not permissions.
Any insight on above is appreciated.
September 8, 2008 at 1:23 pm
Have you enabled the mail account for Agent? That's a different step than just setting up DB Mail.
Right Click Agent, then Properties then select Alert System and Enable Mail Profile. Agent must be restarted for this.
September 8, 2008 at 1:26 pm
Yes , Mail Profile is enabled for SQL Agent account.
September 8, 2008 at 1:29 pm
Verify that the security context under which you are logging to the Console and security context of the account running your SQL Agent are the same.
September 8, 2008 at 1:45 pm
Try modifying the call to sp_send_dmail and prefix the call with the database and schema. The call should be:
Execute msdb.dbo.sp_send_dbmail ...
The other thing to try is to make sure the database context is set to the msdb database when the agent job runs.
If neither of those work - can you post the code so we can see if there is something else that could be causing the problem?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 9, 2008 at 7:15 am
Yes DB name is prefixed : Here's the code:
(pls. note linked server is defined in proc1 and procedure works fine when logged in as a SQL authentication account or SQL agent login account , but fails when scheduled as a job. Also when linked server is changed to sql authentication in security then it works fine even from the scheduled job)
DECLARE @aDate varchar(19)
DECLARE @cmd varchar(128)
SET @aDate = (SELECT Convert(varchar(11),DATEADD(DAY, -1, getdate())))
SET @cmd = 'EXEC DB1.dbo.Proc1 @para1=''abc'', @AsOfDate = ''' + @aDate + ''''
EXEC msdb.dbo.sp_send_dbmail @recipients = 'test@test.com',
@blind_copy_recipients = 'test1@test.com',
@query = @cmd,
@subject = 'This is a test'
September 9, 2008 at 2:15 pm
Is SQL Server Agent running under a domain account or local account (e.g. Local System).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply