sp_send_dbmail fails with query result attached as file

Sometimes a message coming from SQL Server engine can be misleading and confusing.
I had such cases many times and decided to put a post every time I meet it again.

Failed to initialize sqlcmd library with error number -2147467259

When I was working on one SQL Job – its constantly reporting an error:

Msg 22050, Level 16, State 1, Line 58
Failed to initialize sqlcmd library with error number -2147467259.

Although SQL Job has had only one step and called one specific stored procedure, but that stored procedure contained many nested procedures.
Therefore, at the beginning it was hard to find a culprit of the error as each step was dependent of the previous one.
Anyway, exclusion one by one brought an effect. I found a nasty step. Bastard Stored Procedure.

Culprit

It doesn’t contains too many business logic, but send alert by email in specific condition.
The send block looked like below:

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [Person].[Person]
WHERE [EmailPromotion] = 2;
';

I have confirmed that the statement was a reason of the error.
Spending some time on the research I found eventually that the root cause could be a result generated from a query.
Indeed. Giving up with the query – everything works:

EXEC msdb.dbo.sp_send_dbmail @profile_name = NULL,
@recipients = 'recipient@azureplayer.net'
@subject = @Title,
@body = 'Some email body here...';

Hence, something is bad with the query even though the query is executed successfully.

Solution #1

Then I realize that the query uses 2-part object names, so I have added database name to query giving fully qualifying table name:

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [AdventureWorks2012].[Person].[Person]
WHERE [EmailPromotion] = 2;
';

That’s work!

Solution #2

Another solution of that problem is to provide the optional @execute_query_database parameter:

EXEC msdb.dbo.sp_send_dbmail
 @execute_query_database = 'AdventureWorks2012'
,@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [Person].[Person]
WHERE [EmailPromotion] = 2;
';

Conclusion

This is because sp_send_dbmail doesn’t seem to have any database context.
Now, it’s seem to be easy. But the error message wasn’t been helping solve this “mystery” quickly.

Enjoy! I hope that post helped you.