SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email failure - query formatting


Email failure - query formatting

Author
Message
Chris Stride
Chris Stride
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 199
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
blake colson-405790
blake colson-405790
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10396 Visits: 4894
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/
Uripedes Pants
Uripedes Pants
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 4519
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.
Chris Stride
Chris Stride
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 199
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
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10396 Visits: 4894
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search