4

I'm trying to use sp_send_dbmail to send the results of a query through a SQLAgent job in SQL Server 2014. I believe I have my DBMail profile set up properly but when running this:

exec msdb.dbo.sp_send_dbmail
@profile = 'TestProfile',
@recipients = '[email protected]',
@subject = 'Test',
@query = 'SELECT id FROM TestTable',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'TestValues.txt'

I get the following error message:

Failed to initialize sqlcmd library with error number -2147467259.

Googling this error message didn't turn up anything useful, likely due to the generic error number. Anyone have some insight into this error message?

Improve this question
5
  • Is your instance set up for DBMail? In SSMS right click on the instance at the top of the Object Explorer and choose Facets. Change the Facet to Surface Area Configuration and make sure DatabaseMailEnabled = True. Commented Apr 7, 2015 at 21:12
  • Great point; DatabaseMailEnabled is True for my server though. That doesn't seem to be the issue for me. I'm continuing testing today and will post anything I find that seems to help. Commented Apr 8, 2015 at 13:12
  • Hmmm, just found that if I remove the @query, @attach_query_result and @query_attachment_filename, then replace them with just a simple @body message, it works fine. As soon as I put @query back in, it fails. I could see it being a permission issue through SqlAgent but I'm just trying to run the command from a Query window with my admin user at the moment. Commented Apr 8, 2015 at 13:21
  • Is your sqlagent user account set as sysadmin? Commented Apr 8, 2015 at 13:23
  • Not sysadmin but it has high-level permissions. I found the issue though: despite my query window pointing at the correct DB, the script in my original post has no concept of database orientation; you need to tell it what context/db it should run against by either fully qualifying the table in the @query variable or specifying a @execute_query_database parameter. Either of the following fixes my issue: @query = 'select id from dbo.testDB.TestTable or @execute_query_database = 'testDB' Commented Apr 8, 2015 at 13:31

3 Answers 3

Reset to default
6

I found that despite both my query window (for testing) and SqlAgent job were pointing at my desired DB, sp_send_dbmail doesn't seem to have any database context. My original post was failing because SQL didn't know where to run SELECT * FROM TestTable. The fix is to provide sp_send_dbmail with database context by either fully qualifying your table in the @query parameter:

@query = 'SELECT id FROM testDB.dbo.TestTable'

or by providing the optional @execute_query_database parameter:

@execute_query_database = 'testDB'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Enable sysadmin server role for the account that is used to run the SQL Server Agent.Below are the screenshots.

Error

Failed to initialize sqlcmd library with error number -2147467259

Fix

Enable server role sysadmin for SQL Server Account running SQL Server Agent

Now the SQL Server Job runs without any errors and I get an email from dbmail.

enter image description here

Improve this answer

3 Comments

This was the fix for me. No combination of @execute_query_database or fully qualified table references was solving the issue for me. In my case the @query was a join across two different databases, and even if I put it all in a view, I could not get it to work until I followed this advice and gave the SQL Agent sysadmin rights.
@N1njaB0b Glad to know it worked for you. Upvote :-) ?
This turned out to be the problem for me as well... But I am wondering if there is a lesser set of permissions than 'sysadmin' that would work?
0

There's another reason why you might get this error; if the query has an issue.

In our case we had (note that it's not due to a syntax error; note the missing quotes):

DECLARE @EmailQuery varchar(max) = 'select 
                                    e.Field1,
                                    REPLACE(REPLACE(e.ExceptionReason, CHAR(13), ''), CHAR(10), '') ExceptionReason,
                                    e.UserName
                                from dbo.tblException e'

Once we corrected it as follows, it worked fine:

DECLARE @EmailQuery varchar(max) = 'select 
                                    e.Field1,
                                    REPLACE(REPLACE(e.ExceptionReason, CHAR(13), ''''), CHAR(10), '''') ExceptionReason,
                                    e.UserName
                                from dbo.tblException e'
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.