Troubleshooting SQL Server sp_send_dbmail Error Failed to Initialize SQLCMD Library


By:   |   Updated: 2018-06-11   |   Comments (9)   |   Related: More > Database Mail


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 Updated: 2018-06-11


get scripts

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





Comments For This Article




Wednesday, July 01, 2020 - 7:03:06 AM - Regi Back To Top (86074)

Thank you for the article.

In my case, running sp_send_dbmail with @query parameter did not work when executed on a SQL Server job, adding the SQL Server Agent account permission it worked fine.

Regards


Friday, March 27, 2020 - 8:04:39 AM - smt Back To Top (85204)

Hi Greg thanks for the reply.
It did run when I : EXECUTE AS USER = 'sa'

But it gotta be another way! you say to to profiler which I haven't but I will. But the big question is I am sysAdmin on the server, SQL agent account has the admin rights and the query is not creating any folder on the box but just sending an email so why is it failing!!!


Thursday, March 26, 2020 - 6:29:08 PM - Greg Robidoux Back To Top (85200)

Hi Smt,

were you able to use Profiler to see what accounts were being used in the process?

Also, did you try to use the "Run As" in the SQL Agent job step to see if that might work?

-Greg


Thursday, March 26, 2020 - 3:02:26 PM - Smt Back To Top (85197)

When I run below from ssms it runs and I get an email:

DECLARE

    @tab char(1) = CHAR(9)

 

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'my_PROFILE',

    @recipients = 'myname.abc123.com',

    @query = 'DBAtool.dbo.usp_Report' ,

    @subject = 'TestReport',

    @attach_query_result_as_file = 1,

    @query_attachment_filename='myfile.csv',

    @[email protected],

    @query_result_no_padding=1

 

but when i tried to run from Sql agent it give me below error, I even gave sql agent account to Admin rights!!!
".....Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step faile"
Can you please help what exactly should I do to get email thru Agent? Thanks


Thursday, February 20, 2020 - 1:04:23 PM - Greg Robidoux Back To Top (84678)

Hi Susan,

The following is info about permissions from the SQL Server online documentation.

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database. However, when the user sending the message does not have permission to use the profile for the request, sp_send_dbmail returns an error and does not send the message.

You can try to add the database user to the DatabaseMailUserRole. Not sure is this automatically included in the db_owner permissions.

-Greg


Thursday, February 20, 2020 - 11:10:02 AM - Susan Brower Back To Top (84674)

Hi Greg, Thank you for your excellent post.  It helps me tremendously.

I have a question...  I ran my job including the step using an execution of sp_send_dbmail with SQL Performance Monitoring turned on and saw the login id it was trying to use.  I created the login on the SQL Server for the id and gave it dbo permissions to msdb.  It still failed with the same error.  I changed the login to be incuded in the sysadmin group, reran the job, and this time it was successful.  Do you have any recommendations for how the login id can be set up with MINIMUM security? 


Friday, October 04, 2019 - 6:00:55 AM - W.E. Back To Top (82664)

Thank's a lot for this article. It just helped me a lot. Using the profiler to check for errors sounds so simple, but I never would have thought of that...


Wednesday, May 22, 2019 - 9:07:50 AM - Asher Back To Top (80155)

This error was related to the variable holding my query not being large enough.


Friday, January 25, 2019 - 6:09:57 AM - Andy Todd Back To Top (78879)

 Just wanted to express my thanks - your post saved me who knows how many hours of delving myself. So well wrutten and illustrated, it made sense straightaway and using a SQL login basically fixed my issue.



download





Recommended Reading

Setting up Database Mail for SQL Server

Send email in a tabular format using SQL Server database mail

Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account

Generate HTML Formatted Emails from SQL Server

SQL Server Database Mail Attachment Error








get free sql tips
agree to terms


Learn more about SQL Server tools