Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Troubleshooting SQL Server sp_send_dbmail Error Failed to Initialize SQLCMD Library


By:   |   Read Comments   |   Related Tips: More > Database Mail

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

The other day I was helping someone with sending a query attachment in an email using sp_send_dbmail.  I tried to recreate what they were doing, but I was getting this error message "Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259".

I could send an email without an issue using sp_send_dbmail, but whenever I tried to include a query I kept getting the error message. 

Solution

Let me recreate what I was doing to show the error and how it was resolved.

The first thing I did was to use this tip to help setup database mail. After setting up database mail, I used SSMS and used Send Test E-Mail... to send a test email message and everything worked.

I then sent a simple email to see if sp_send_dbmail would work.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dbmail',  
    @recipients = '[email protected]',  
    @subject = 'Simple Test Email'

I got a message that said "Mail (Id: 13) queued" and I did receive the email.

Then I tried to send an email with a query using the following command.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dbmail',  
    @recipients = '[email protected]',  
    @subject = 'Test Email with query results',
    @query = 'SELECT top 10 * from sys.objects'

This is where I started to get the error message "Msg 22050, Level 16, State 1, Line 14 Failed to initialize sqlcmd library with error number -2147467259" as shown below.

sp_send_dbmail error

Possible Solutions

I did a bunch of internet searches to find a solution and some of the things I found were related to SQL Server Agent not having correct permissions, the login not having the correct permissions, the database object not being properly referenced with a three part name, and sqlcmd not being installed or enabled.

So, I tried some of these things.

Send Email Using Three Part Naming

I changed the query as follows to reference the master database as follows:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'dbmail',  
    @recipients = '[email protected]',  
    @subject = 'Test Email with query results referencing database',
    @query = 'SELECT top 10 * from master.sys.objects'

Still no luck.

Send Test Email With Query Using SQL Agent

I created a SQL Agent job using the command below and this worked fine.

send email via SQL Agent

Now I was getting somewhere.  It looks like the account I was using to run the command from a query window must be the problem, but I still didn't get why a regular email worked fine, but when including a query, I was getting errors.

Here is the setup of SQL Server that I was using.  I am running SQL Server 2017 on a local machine that is not part of a domain. This is using both SQL Server and Windows Authentication mode and this is using the standard local logins for the service accounts.

Send Test Email with Query Using a SQL Login

For the tests above, I was connected to SQL Server using Windows authentication, so I decided to login with a standard login instead that has permissions to use sp_send_dbmail.

I logged into SQL Server using the "sa" login and what do you know, it worked fine.

Troubleshooting Issue Using Profiler

So, now I was curious to find out what was happening.  I started up Profiler and captured the following events:

  • SQL:BatchCompleted
  • RPC:Completed
  • User Error Message

I was the only one using this instance of SQL Server and there were no background processes, so I was able to capture everything that was occurring once the command was run.

Run sp_send_dbmail with a query for login with issues

I ran the sp_send_dbmail command again using the Windows authentication login, this is the one that fails, and captured the following: 

profiler output

So, a couple of things I noticed immediately:

  1. The Application Name includes
    • Microsoft SQL Server Management Studio - Query (this makes sense since I am running the command using SSMS)
    • Microsoft SQL Server (this looks like some background information about changing databases to msdb and using English as the language and inserting data into the mail queue)
    • SQLCMD (I was surprised to see this.  It looks like SQLCMD is used to execute the query)
  2. The ClientProcessID includes 660 and 25624.  So, it looks like the command is handed over to another process.
  3. The SPID includes 57, 58 and 59. So there are three different connections being made.

If we look at the last User Error Message we get the following information. It looks like it was trying to use this login to run SQLCMD and that is why the process fails.

Could not obtain information about Windows NT group/user 'MicrosoftAccount\[email protected]', error code 0x54b.

Run sp_send_dbmail without a query for login with issues

If we send the email without using a query, we can see Profiler looks a lot different.

send mail with out query
  1. The Application Name includes
    1. Microsoft SQL Server Management Studio - Query
    2. DatabaseMail - DatabaseMail - Id<30028>
  2. The ClientProcessID still includes two different processes.
  3. The SPID still has three different spids.

Run sp_send_dbmail with a query for login without issues

If we send the email with a query using a SQL login that exists, we can see Profiler looks a lot different.

sp_send_dbmail with query
  1. The Application Name includes
    1. Microsoft SQL Server Management Studio - Query
    2. Microsoft SQL Server
    3. SQLCMD
    4. DatabaseMail - DatabaseMail - Id<30028>
  2. The ClientProcessID includes three different processes.
  3. The SPID has five different spids.

Conclusion

It turns out that the issue was permission related, but the error message is pretty cryptic and it looks like you get the same error for multiple problems when trying to use sp_send_dbmail when including a query.

I hope this sheds some light on this issue and if you do encounter the problem you could follow these steps to see if it is a permissions issue.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools