Problem with using sp_send_dbmail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with using sp_send_dbmail

Author

Topic

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-05 : 06:06:06

I have the following code which runs executes correctly and sends the mail if I run the query manually, but if I add it to a SQL job, it fails with the message "Error formatting query, probably invalid parameters [SQLSTATE 42000](Error 22050). The step failed."

What have I got wrong here?

[code]EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Martyn',
@recipients = 'myemail@gmail.com',
@subject = 'Service Due Report',
@query =N'select * from mcsrm_live.[MCSReports].[vwServiceDueReport] where group_name = "360"',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ServiceDueReport.csv'[\code]

Many thanks
Martyn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 12:30:08

Put the query into a variable instead and then pass the variable to the DB Mail stored procedure.

Try this:

DECLARE @sql nvarchar(500)

SET @sql = 'select * from mcsrm_live.[MCSReports].[vwServiceDueReport] where group_name = ''360'''

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Martyn',
@recipients = 'myemail@gmail.com',
@subject = 'Service Due Report',
@query = @sql,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ServiceDueReport.csv'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-05 : 12:45:37

Hi Tara

Thanks for your suggestion, I've tried it again but the job still fails with the same error. Works in the query window as before though. What else can I try?

Martyn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 13:05:05

Does a simple sp_send_dbmail query work fine in a job (no query or attachment)? I'm wondering if you're encountering a bug. What does SELECT @@VERSION show?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-05 : 17:11:38

If I remove the query and attachment, the job does run! What can I check/do next to get it working as it should?

@@VERSION:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)
Jun 11 2012 16:41:53
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 17:29:59

I would recommend installing service pack 3 for SQL Server 2008 R2. Perhaps it's a bug you are encountering. I can't think of any other reasons.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-05 : 17:44:36

I used @append_query_error to get this message with more info emailed:

Msg 916, Level 14, State 1, Server FORK-SAGE\MCS, Line 1 The server principal "NT AUTHORITY\NETWORK SERVICE" is not able to access the database "mcsrm_live" under the current security context.

Not very good with permissions, what do I need to do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 17:51:50

Provide the access to NT AUTHORITY\NETWORK SERVICE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-05 : 18:07:04

Sorry could you be a bit more specific please - I don't even see NT AUTHORITY\NETWORK SERVICE under Security > Logins on either the Job Agent or Security > Users on the mcsrm_live database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 18:08:21

Add the account to logins, grant access to the database and then grant db_reader or select on vwServiceDueReport.

The account is due to what the SQL Server service is running under. That's what is used for jobs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-03-06 : 05:37:36

I have created and granted access as per your email and all now working perfectly.

Many thanks for your help.

Martyn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-06 : 12:14:43

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources