We have detected the following issue in production after upgrade from SQL Server 2012 SP2 to SQL Server 2014 SP1 with CU5: ‘Failed to initialize sqlcmd library with error number -2147024809“. This message is fired when we try to send an email using sp_send_mail procedure with the query result to be attached or in the body of the email.
No one of various suggestions found on the internet helped in our environment (security, etc.) After serious investigation, we found that the issue is presented only with specific values set to sp_send_mail procedure parameters. Following combinations of values is generating the error:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DEFAULT_DBMAIL_PROFILE', @recipients = 'test@test.com', @subject = 'Test', @body = 'Test', @query = 'SELECT 1', @query_result_header = 0, @query_no_truncate = 1, @query_result_no_padding = 0
As most important looks like the combination of @query_result_header and @query_no_truncated. After doing this change error disappeared. Following is working:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DEFAULT_DBMAIL_PROFILE', @recipients = 'test@test.com', @subject = 'Test', @body = 'Test', @query = 'SELECT 1', @query_result_header = 1, @query_no_truncate = 1, @query_result_no_padding = 0
We are generating HTML content to the email body and after changing the @query_result_header parameter from 0 to 1 all the logic is working without any change and the email body is delivered as expected.