January 22, 2010 at 8:43 am
Hi,
I have recently upgraded from SQL Server 2005 (v9.0.4262) Standard Edition 32-bit to SQL Server 2008 (v10.0.2531) Enterprise Edition 64-bit. I have a job which used to email a notification. This worked fine on the old server, but fails on the new. The part that fails is:
*************************************************************************************
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'abc@xyz.com',
@subject = 'SQL job step failures from the Warehouse load this morning',
@body_format = 'HTML',
@execute_query_database = 'DW_Mart',
@query = 'SELECT * FROM DW_Mart.dbo.SQL_job_step_failures;',
@query_result_header = 1,
@query_result_width = 200;
*************************************************************************************
The error message returned is:
Message
Executed as user: XXXX\yyyyy. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
The user this job runs under (exact same properties & permissions as the old server as far as I can see) is a member of the DatabaseMailUserRole on the msdb database.
When I run the above SQL (between the '*********'s) in a query window, it executes fine and sends the mail I expect. The issue would appear to be in the query, rather than permissions, but I'm not sure what it could be when it is a valid query which hasn't changed and does return data when run manually in a query window. I have tried adjusting the query_result_width, but with no luck. I've also deleted and re-done the job from scratch - still no luck.
Looking through the various SQL Server logs, I can't see any additional information that sheds any futher light on why this is failing. I've tried putting Database Mail in verbose logging mode and then running the job again (with it failing, obviously) but nothing to do with this error seems to be logged at all.
Apologies if there is a ready answer somewhere out there and I've missed it. I've tried looking around for any similar cases, but the responses don't seem to be anything that will resolve this.
Would appreciate any advice, suggestions.
Regards,
Chris Stride
February 10, 2010 at 3:58 pm
I had the same issue with sp_send_dbmail and the @query parameter called from a SQL job. I could successfully execute sp_send_dbmail/@query param' from the Query Analyzer but it would fail executing from inside a job. Drove me nuts ! I eventually discovered if I explicitly added to SQL Server the domain account the SQL Agent was running under, xp_loginfo would now return a result set for the SQL Agent account and my problem with sp_send_dbmail executing successfully from a job was fixed. In my environment the SQL Agent account was originally configured to access SQL Server through a windows domain group; that doesn't work. I had to explicitly add our SQL Agent account to SQL Server and assign Admin privileges. Very frustrating problem. Hope this helps all my fellow DBAs ! -CqlBoy
ps: There's a somewhat related problem configuring DB Mirroring. You must explicitly add the SQL Agent account from the partner server to get DB Mirroring to configure and work properly. You can't use a domain group with SQL permissions.
CQLBoy
February 11, 2010 at 11:26 am
Please chack the following links:
http://social.msdn.microsoft.com/Forums/en/sqltools/thread/bac7ecee-a156-4313-b532-ba15813a0700
http://relatedterms.com/ViewThread.aspx?t=1628286
If this does not adress y9our problem I would sugesst that y9ou google and I;m sure that you will find a solution to your problem.
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 12, 2010 at 1:20 pm
I have a DB that has 5 jobs that send about 300 emails on the 15th of each month. These would fail occasionally and it would drive me nuts figuring it out. What I did was use the return value from xp_sendmail within my sp to check for failure and if so use
exec master..xp_logevent to log the failure with information from the point of failure.
won't solve your problem, but it might give you an idea on how to pin it down.
February 18, 2010 at 10:12 am
Hi all,
Thanks for your responses - appreciated. After spending a fair amount of time researching on Google etc... (and coming across those links mentioned by Welsh) prior to posting the initial problem, I was eventually able to resolve it by explicitly granting the account SQL Server is running under access to the user database referred to in the query.
Strange though, as I hadn't needed to do this at all with the previous server. Might be a 2005 to 2008 thing as I am as sure as I can be that all other aspects of permissions were identical between new and old servers.
Anyway, thanks once again for the help.
Regards,
Chris
February 18, 2010 at 10:45 am
Hi,
I should have mentioned that SQL Mail and those Extended Stored Procedures are depreciated and they require the use of MAPI.
In SQL Server 2000 & prior I used an ActiveX Script Task to use SMTP Mail. It is much better.
SQL Server 20005 introduced the Database Mail Tasks which uses SMTP Mail
I found the Database Mail Task to be limited.
If you want to get tricky you can use a combination of the SQLCLR and Script task to develop a more flexible and robust solution.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply