December 30, 2010 at 8:52 pm
Hi,
I have read I think every post on the web about this problem but still no luck with a solution. I keep getting the error below when executing this code in a SQL AGENT Job in 2008 R2
CODE:
BEGIN
declare @servername nvarchar(150)
set @servername = @@servername
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'
DECLARE @sqlquery nvarchar(250)
SET @sqlquery = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'xxxxx@xxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @sqlquery,
@query_result_width = 600,
@attach_query_result_as_file = 1
END
ERROR:
Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
The domain account running SQL Agent execute permissions on the msdb but that make no difference.
Any ideas? Its driving me crazy by now. Its NYE and I want to be out in the sun 🙁
December 30, 2010 at 11:48 pm
Are you able to send test mail using :-
@profile_name = 'default' and
@recipients = 'xxxxx@xxxxxxx',
And I am sure you have enabled mail in advance configration.
If the above testing works then it mean mail is working with mentioned profile and need to find out the problem in rest code.
----------
Ashish
December 30, 2010 at 11:50 pm
Please confirm if the SQL Server Agent service account has "sysadmin" privileges on the instance.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 3, 2011 at 3:43 pm
I have checked and the account that is running the SQL AGENT has sysadmin priviledges on the sql server instance and also the code run Ok if I run it in the QA.
So still not too sure why this job is failing....
January 3, 2011 at 8:14 pm
Does changing the job owner to a "sysadmin" login help?
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
January 4, 2011 at 2:41 pm
Unfortunately not 🙁 I have changed the job owner to the the same account that runs the SQL AGENT which has sysadmin privileges. The same error still persists. The whole script is shown below:
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
BEGIN
declare @servername nvarchar(150)
set @servername = @@servername
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'
DECLARE @sqlquery nvarchar(250)
SET @sqlquery = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'xxxxxxx@xxxxxxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @sqlquery,
@query_result_width = 600,
@attach_query_result_as_file = 1
END
--== Clean up our process by dropping our temporary table. ==--
DROP TABLE tempdb.dbo.ErrorLog
When I run in QA everything works prefectly - I get an email as expected - but once I but it into a job I get the errror below:
Message
Executed as user: xxxx\xxxxxx. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
The executed as user has a server role of sysadmin and is also the owner of the job.
Reallty stumped by this one and can't seem to find a solution online....
January 4, 2011 at 4:21 pm
Has the 'executed as' user's password expired?
January 4, 2011 at 4:25 pm
Sorry to ask the 'is the power on' question, but can you run this script from a query window and do you get the same error if you do?
You answered this, sorry. I missed it in my first pass through your second reply.
Break the script down command by command and find the specific point that it fails on. If it fails with a simple declare, something's wrong with the logon. If the declare works and further on it breaks, we can delve into that specific script breaking point.
Need to find out where SQL is chewing on this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 4, 2011 at 4:53 pm
thanks for the replies....
The Job falls over when it hits the
EXEC msdb.dbo.sp_send_dbmail
profile_name = 'default',
@recipients = 'xxxxxxx@xxxxxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info.',
@query = @sqlquery,
@query_result_width = 600,
@attach_query_result_as_file = 1
code. I have tried replacing the @query = @sqlquery line with the actual SQL string but it made difference - still getting the same error message.....
January 4, 2011 at 5:05 pm
I wonder if it's bogging up on the Profile name.
From here: http://msdn.microsoft.com/en-us/library/ms190307.aspx
[ @profile_name= ] 'profile_name'
Is the name of the profile to send the message from. The profile_name is of type sysname, with a default of NULL. The profile_name must be the name of an existing Database Mail profile. When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. If the user does not have a default private profile and there is no default public profile for the database, @profile_name must be specified.
I wonder if the SQL Agent user doesn't HAVE a default profile. Can you doublecheck?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 4, 2011 at 5:11 pm
Hi Craig - thanks for the reply....
In youe extract is says : "When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user."
But in my script I am specifying an actual profile called 'default' which is set up on the server and is working OK to send out mails.....so going on that msdn extract this is the profile that should be used....or am I reading it wrong?
January 5, 2011 at 2:09 am
JayK (1/4/2011)
Hi Craig - thanks for the reply....In youe extract is says : "When no profile_name is specified, sp_send_dbmail uses the default private profile for the current user."
But in my script I am specifying an actual profile called 'default' which is set up on the server and is working OK to send out mails.....so going on that msdn extract this is the profile that should be used....or am I reading it wrong?
No, I don't think so, but it's easy enough to test. Try setting up a job to send yourself an email with no query involved but the rest of the parameters the same. I may have jumped to a conclusion there, apologies. If you can nail down that it's the actual query, then you'll have to look at the sql agent's ability to access those tables directly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2011 at 5:17 pm
Eureka! So I finally solved the issue - just by trail and error.
When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error. :w00t:
I can't explain why - but it works!
Thanks for your help....
January 6, 2011 at 11:36 am
JayK (1/5/2011)
Eureka! So I finally solved the issue - just by trail and error.When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error. :w00t:
I can't explain why - but it works!
Thanks for your help....
:crazy: :blink: Appreciate you posting your solution. I'll keep that in mind if my jobs ever start acting in a similarly strange fashion. I'm not sure why that works... but hey, I'll roll with it. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply