Troubleshooting SQL Server sp_send_dbmail Error Failed to Initialize SQLCMD Library

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 = 'greg@mssqltips.com',  
    @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 = 'greg@mssqltips.com',  
    @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.

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 = 'greg@mssqltips.com',  
    @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.

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: 

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.

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.

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.

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

• Check out these other Database Mail tips