Generic failure using sp_send_dbmail in SQL Server 2014
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?
Answer
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'