Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Email failure - query formatting Expand / Collapse
Author
Message
Posted Friday, January 22, 2010 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:12 AM
Points: 23, Visits: 137
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
Post #852110
Posted Wednesday, February 10, 2010 3:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 4:03 PM
Points: 6, Visits: 146
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
Post #863702
Posted Thursday, February 11, 2010 11:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 4,175, Visits: 4,257
Please chack the following links:

http://www.phwinfo.com/forum/ms-sqlserver-server/340709-db-mail-error-formatting-query-probably-invalid-parameters.html

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/

Post #864206
Posted Friday, February 12, 2010 1:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, April 7, 2014 11:41 AM
Points: 703, Visits: 4,519
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.
Post #864916
Posted Thursday, February 18, 2010 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:12 AM
Points: 23, Visits: 137
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
Post #868228
Posted Thursday, February 18, 2010 10:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:00 PM
Points: 4,175, Visits: 4,257
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/

Post #868263
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse