sp_send_dbmail fails with query result attached as file

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:

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@sqlplayer.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@sqlplayer.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@sqlplayer.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@sqlplayer.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.

Previous Last week reading (2017-12-24) aka. Merry Christmas!
Next Last week reading (2017-12-31) & Happy New Year!

About author

Kamil Nowinski
Kamil Nowinski 84 posts

Blogger, speaker. Data Platform MVP, MCSE. Senior Data Engineer & data geek. Member of Data Community Poland, co-organizer of SQLDay, Happy husband & father.

View all posts by this author →

You might also like

TRUNCATE PARTITION in older version

Certainly, you have heard about TRUNCATE. On a table. Also probably you’ve already heard about TRUNCATE on partitions. This feature is in the latest version (latest, I mean 2016 as

CSV reader from SDU Tools

Greg Low from SQL Down Under released a new version of his tool called “SDU Tools”. The tool contains many interesting and useful functions and stored procedures you can use

DevOps workshop during the SQLDay 2018 – SQLPlayer Team

Good news! We (Kamil and I ) are going to have a full day workshop during the upcoming SQLDay 2018 in Wrocław. The workshop is dedicated to DevOps in SQL Server.

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Reply

+ 47 = 55

Protected with IP Blacklist CloudIP Blacklist Cloud