This is a common error and most search results on the web point out the most common cause for this error (which is also documented in the official Microsoft documentation):
The following error may occur when setting @query_result_header to 0 and setting @query_no_truncate to 1:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147024809.
Microsoft: sp_send_dbmail (Transact-SQL)
However, I recently came across another scenario that can cause [almost]* the exact same error and leave you scratching your head over something that is really just a simple mistake.
*If you look closely the Msg number is the same but the error number is different: -2147467259 vs -2147024809
Scenario
I enabled database mail and tried to execute the following query:
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'dbamail',
@recipients = '[email protected]',
@subject = 'Test',
@body = 'File attached',
@query = 'select count(*) from dbo.members;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MemberCount.csv';
And got the following error:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147467259.
Like everyone else I searched online, came across the blog posts as well as the official Microsoft documentation and added the appropriate parameters (even though they should have been the defaults since I did not explicitly set them before).
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'dbamail',
@recipients = '[email protected]',
@subject = 'Test',
@body = 'File attached',
@query = 'select count(*) from dbo.members;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MemberCount.csv',
@query_result_header = 1, @query_no_truncate = 0
But got the same error:
Msg 22050, Level 16, State 1, Line 12: Failed to initialize sqlcmd library with error number -2147467259.
Solution:
If you look at my code you’ll notice that there is an optional parameter not specified: @execute_query_database
[ @execute_query_database = ] 'execute_query_database' Is the database context within which the stored procedure runs the query. The parameter is of type sysname, with a default of the current database. This parameter is only applicable if @query is specified.
Microsoft: sp_send_dbmail (Transact-SQL)
I’d like to point out the line that says “with a default of the current database” because when I checked I had forgotten to change the current database context in SSMS to my user database instead of master. However, when I changed the context to my user database it was still failing with the same error.
I couldn’t successfully send my databasemail with attachment until I specified this parameter explicitly:
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'dbamail',
@recipients = '[email protected]',
@subject = 'Test',
@body = 'File attached',
@execute_query_database = 'myDB',
@query = 'select count(*) from dbo.members;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MemberCount.csv',
@query_result_header = 1, @query_no_truncate = 0;
Specifying the database name in the bindings of my select query also seemed to work:
EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'dbamail',
@recipients = '[email protected]',
@subject = 'Test',
@body = 'File attached',
@query = 'select count(*) from myDB.dbo.members;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MemberCount.csv',
@query_result_header = 1, @query_no_truncate = 0;
Conclusions
My philosophy is to be as verbose as possible if doing so doesn’t increase the complexity of the problem you are trying to solve. In this case I shouldn’t have relied on MSSQL Server or SSMS to predict the database I was trying to execute against.
SSMS can also be unreliable for these types of things because of the way it asynchronously changes or updates it’s GUI. My suspicion is that even though I changed the database context with the drop down menu, I never clicked back into the query editor window and so the session wasn’t updated. I could have gone back and tested this theory but it seemed moot point since the best solution is obviously to just specify the database in my query.
