Failed to initialize sqlcmd library with error number 2147467259

We recently encountered a bunch of problems with sp_send_dbmail, and they all returned this:

Failed to initialize sqlcmd library with error number -2147467259.

Super irritating, that error, because it seems to suggest that you’re doing something you’re not.
In fact, this error only indicates that there’s some generic problem sending mail with sp_send_dbmail.

Here are the problems we uncovered, and the troubleshooting steps for them.

1. The SQL Server service account didn’t have sufficient privileges on the domain.

As a rule, we don’t allow our domain accounts to query the properties of other accounts. This is true for service accounts, as well. SQL, to do a fair number of things (not least to ensure the SQL Agent job owner is a valid domain member) requires the ability to query domain account properties.

We discovered this in troubleshooting: when trying to use the

execute as user=

statement to make sure we didn’t have a problem with database permissions, we received this error:

Could not obtain information about Windows NT group/user...

Aha: domain permissions problem. This was causing the «Failed to initialize sqlcmd library» error.
Granting the service account additional privileges on the domain fixed this problem.

2. The executing user didn’t have sufficient privileges in the query database.

Having fixed the «execute as user» problem, we can now impersonate a DB user to execute the query in SSMS. If you have sufficient permissions on the instance. We’re assuming you have sysadmin access, here.

When we run the sp_send_dbmail, and the executing user doesn’t have permissions on the target database, we will see the «Failed to initialize sqlcmd library with error number -2147467259.» error.

We can uncover this is the problem by just executing the query portion of the sp_send_dbmail stored proc, and using the «execute as user=» statement beforehand. Specify the SQL Agent service account user in this statement, and if the user doesn’t have permissions, you’ll get an error message that’s actually useful.

3. The query is executing in the wrong database.

You actually see this pretty often, and the solution to this is most frequently listed possibility for fixing the «Failed to initialize sqlcmd library with error number -2147467259» error.

The easiest solution to this problem is to specify the target database in sp_send_dbmail, like this:

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailList
       ,@execute_query_database = 'target_db_name'
       ,@subject = 'Subject'
       ,@body='Here's the body of the email'
       ,@query = @querytext
       ,@profile_name='email_profile'

I hope this helps; we spent far too long on tracking down #1 above.

Источник

Содержание

Failed to initialize sqlcmd library with error number -2147467259
Failed to initialize sqlcmd library with error number 2147467259
Вопрос
msdb.dbo.sp_send_dbmail — Failed to initlaize sqlcmd 2147467259
Popular Topics in Microsoft SQL Server
12 Replies
Read these next.
Snap! — LifeLock Breach, Secret AI, Reversible Aging, Brain Fog?
File Permissions
Spark! Pro series – 16th January 2023
How do you like to learn?
poor wifi, school’s third floor

Failed to initialize sqlcmd library with error number -2147467259

November 11, 2015 at 2:56 am

I have the following problem:

I have a three node cluster with three sql instances. All are SQL Server 2014 SP1 Enterprise Edition.

Several jobs run everyday emailing data without any problem.

But suddenly when I try to create a new job, and run it, the following error message pops up:

Failed to initialize sqlcmd library with error number -2147467259

My account and the SQL Server agent account are sysadmin.

This is not a security problem.

This is my query:

select * from unit4_beheer.[dbo].[ClusterActiveNode]

@subject=’ Active node Salaris1′,

When I try to run this from a query window, the same problem occurs.

When I remove the @query parameter the email is sent.

I have been searching on the internet but no one seems to know what the problem is.

Any ideas would be greatly appreciated 🙂

November 11, 2015 at 6:18 am

Does the query bring back data?

Does the query bring back data when using a login and user that has the same access rights as the login and user that executes the job?

Catch-all queries done right [/url]
Gail Shaw’s Performance Blog[/url]

November 11, 2015 at 6:26 am

When I run the query alone, it returns data.

The problem is with the @query parameter. When I take it out of the query, the email is sent.

I f I leave it in, the errormessage is given.

November 11, 2015 at 6:32 am

you are missing required parameters, i think.

if you declare a query is going to be attached, you have to have a file name.

EXEC msdb.dbo.sp_send_dbmail @recipients=’SQL@SQL.COM’,

@subject = ‘ERP Upload’,

@query = ‘EXEC ERP.dbo.usp_GenerateERPFile’,

Lowell — help us help you! If you post a question, make sure you include a CREATE TABLE. statement and INSERT INTO. statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

November 11, 2015 at 6:49 am

@query = ‘select * from UNIT4_BEHEER.DBO.ClusterActiveNode’,

I get Command completed succesfully. no email.

November 11, 2015 at 8:14 am

«Type» and «Run as» value of the new job step is same as the job which is working fine ? can you provide the job script ?

November 11, 2015 at 10:27 am

@query = ‘select * from UNIT4_BEHEER.DBO.ClusterActiveNode’,

I get Command completed successfully. no email.

great, now that the command works as expected, now you have to check to see why the mail failed: run this query and see what error exists in dbmail. what is the specific error?

mail.send_request_date As SentDate,

sent_status As Reason,

FROM [msdb].[dbo].[sysmail_allitems] mail

inner join [msdb].[dbo].[sysmail_event_log] err

ON err.mailitem_id = mail.mailitem_id

WHERE mail.sent_status <> ‘sent’

order by mailitem_id desc

November 11, 2015 at 1:03 pm

I will take a look in the morning.

I also have a feeling it could be related to my user account.

The account is actually from a domain that is linked by a trust.

When I ran the command in a query window this afternoon using an account from the actual domain SQL server is in, it worked! I didn’t have time to investigate further.

I will have to look into this tomorrow.

For now, thanks for your help 🙂

November 12, 2015 at 12:37 am

As I suspected the problem is with the user.

When I run the job or run the query from a query window as a domain local user, there is no problem.

When I try it as a user from a trusted domain, it all fails.

There was also a problem with the query itself.

I did a select via a linked server in the mail step. This failed.

I fixed this by selecting the data in a previous step and only selecting ‘local’ data in the mail step. This works.

So in conclusion,

when I use a trusted user account, I can’t use the mail functionality from a query window.

When I create a job with owner sa, with a mail step, it works fine now( the problem was with the query using a linked server).

thanks for the help 🙂

February 18, 2019 at 12:58 pm

Me encuentro en la misma situación, eh revisado las respuestas que te han brindaron pero solo eh logrado a que se ejecute el procedure pero no sale ningun correo.

Lo que yo eh hecho es crear un procedure donde armo todo el formato de mi correo y adjunto un archivo «REPORTE.CSV», pero me arroja un error «No se puede inicializar la biblioteca sqlcmd con el número de error -2147467259.», al poner @exclude_query_output = 1
se ejecuta el procedure pero no sale el correo.
Apoyo por favor, ya voy dias buscando solucion .

EXEC msdb.dbo.sp_send_dbmail
@profile_name=’BD’,
@recipients=’soporte@xxx.com.pe’,
@subject=@ASUNTO,
@body=@MENSAJE,
@body_format = ‘HTML’ ,
@query_attachment_filename=’Reporte.csv’,
@execute_query_database = ‘BD’,
@query = ‘select*from BD.dbo.TEMP_DESPA_VALE’,
@query_result_header = 0 ,
@exclude_query_output = 1 ,
@append_query_error = 1 ,
@attach_query_result_as_file = 1 ,
@query_result_separator=@tab,
@query_result_no_padding=1

February 18, 2019 at 3:18 pm

I am in the same situation, I have reviewed the answers that you have received but I have only managed to get the procedure executed but no emails come out.

What I have done is create a procedure where I assemble the entire format of my email and attach a «REPORT.CSV» file, but it throws me an error «The sqlcmd library can not be initialized with error number -2147467259.», when putting @exclude_query_output = 1
the procedure is executed but the mail does not come out.
Support please, I’m going days looking for a solution.

when you add @exclude_query_output = 1, are there any errors in the failed mail?
Cuando agrega @exclude_query_output = 1, ¿hay algún error en el correo fallido?

SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> ‘sent’
order by mailitem_id desc

Источник

Вопрос

Please find the script and advice

DECLARE @tab VARCHAR(1)
SET @tab = CHAR(9)
DECLARE @sub VARCHAR(100)
DECLARE @qry VARCHAR(1000)
DECLARE @msg VARCHAR(250)
DECLARE @query NVARCHAR(1000)
DECLARE @query_attachment_filename NVARCHAR(520)

SELECT @sub = ‘XXX ‘+ convert(VArchar,getdate()-1,103)
SELECT @msg = ‘Dear Team,

Kindly note the Attachments XXX

NOTE : This is an auto generated email and do not reply back’
SELECT @query = ‘ SET NOCOUNT ON; Exec Reportdb.dbo.RPT_Comparison_AUTO’

SELECT @query_attachment_filename = ‘xxxx Report.xls’

execute as login = ‘sa’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘REPORTS’,
@recipients = ‘baskar.r@XXXXXX.com’,
@copy_recipients =»,
@blind_copy_recipients = ‘baskar.r@XXXXXX.com’,
@body = @msg,
@subject = @sub,
@query = @query,
@query_attachment_filename = @query_attachment_filename,
@attach_query_result_as_file = 1,
@query_result_header = 1,
@query_result_width = 32767,
@query_result_separator = @tab,
@query_no_truncate = 0,
@exclude_query_output = 0,
@query_result_no_padding =1;

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

Источник

msdb.dbo.sp_send_dbmail — Failed to initlaize sqlcmd 2147467259

Last week we lost power to our building, and at the same time we had some SSL certs expire.

We updated the certs for the SQL server, and everything seems to be functioning properly, but the SQLSERVERAGENT jobs that send mail now don’t work.

We’ve been at this issue for two days- sending mail without @query works. In SSMS query or as a job.

Our mail profile is simple called «Alerts»

Our queries can get complicated, so to test this is one of the many queries I’ve tried just to eliminate as many variables as possible and I still get the error.

I’ve run down every thread containing this error number.

I’ve run down every thread containing msdb.dbo.sp_send_dbmail problems

The SQLSERVERAGENT uses «Alerts» as its mail profile. It has membership to «DatabaseMailUserRole» in the table in question.

Tried these queries with different tables, as well as using different mail profiles, execute as switches, everything the internet suggests. Nothing seems to fix this.

Read these next.

Snap! — LifeLock Breach, Secret AI, Reversible Aging, Brain Fog?

Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: January 16, 1956: SAGE Is Disclosed to the Public (Read more HERE.) Bonus Flashback: January 16, 1969: Soyuz 4 & 5 Make History (Read more HERE.) You need to hear .

File Permissions

Hi All,Last week my work asked me to remove all Security groups and set access levels per user. They also have decided that the no longer want these at Folder level but would rather it was File level across the whole server.The reason for this , They say .

Spark! Pro series – 16th January 2023

It is a new week, full of endless opportunities and possibilities. Let’s make the most of it! Just a reminder, if you are reading the Spark!, Spice it up. We like it spicy here! Today in History: NASA’s.

How do you like to learn?

There is a lot of buzz and actually also controversy about learning styles and multiple intelligences in the way that we think about learning, so not taking a side here and saying that it is a magical code that will unlock our ability to do all things. T.

poor wifi, school’s third floor

I work as a help desk technician at a high school for a school district. Teachers/students on the building’s third floor have been reporting poor wifi, with their Chromebooks/laptops etc experiencing slow connectivity and random disconnections. We hav.

Источник

Sometimes a message coming from SQL Server engine can be misleading and confusing.
I had such cases many times and decided to put a post every time I meet it again.

Failed to initialize sqlcmd library with error number -2147467259

When I was working on one SQL Job – its constantly reporting an error:

Msg 22050, Level 16, State 1, Line 58
Failed to initialize sqlcmd library with error number -2147467259.

Although SQL Job has had only one step and called one specific stored procedure, but that stored procedure contained many nested procedures.
Therefore, at the beginning it was hard to find a culprit of the error as each step was dependent of the previous one.
Anyway, exclusion one by one brought an effect. I found a nasty step. Bastard Stored Procedure.

Culprit

It doesn’t contains too many business logic, but send alert by email in specific condition.
The send block looked like below:

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [Person].[Person]
WHERE [EmailPromotion] = 2;
';

I have confirmed that the statement was a reason of the error.
Spending some time on the research I found eventually that the root cause could be a result generated from a query.
Indeed. Giving up with the query – everything works:

EXEC msdb.dbo.sp_send_dbmail @profile_name = NULL,
@recipients = 'recipient@azureplayer.net'
@subject = @Title,
@body = 'Some email body here...';

Hence, something is bad with the query even though the query is executed successfully.

Solution #1

Then I realize that the query uses 2-part object names, so I have added database name to query giving fully qualifying table name:

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [AdventureWorks2012].[Person].[Person]
WHERE [EmailPromotion] = 2;
';

That’s work!

Solution #2

Another solution of that problem is to provide the optional @execute_query_database parameter:

EXEC msdb.dbo.sp_send_dbmail
 @execute_query_database = 'AdventureWorks2012'
,@recipients = 'recipient@azureplayer.net'
,@subject = @Title
,@body_format = 'HTML'
,@body = 'You can find the details in the attachment.'
,@attach_query_result_as_file = 1
,@query = '
SELECT TOP 10
[BusinessEntityID], [PersonType], [Title], [FirstName], [LastName]
FROM [Person].[Person]
WHERE [EmailPromotion] = 2;
';

Conclusion

This is because sp_send_dbmail doesn’t seem to have any database context.
Now, it’s seem to be easy. But the error message wasn’t been helping solve this “mystery” quickly.

Enjoy! I hope that post helped you.

Источник

The below procedure to send email gives an error if @exclude_query_output = 0, otherwise runs fine with no errors and send emails but without attachment, I think the problem with @Query parameters

this is runs on SQL 2016

Failed to initialize sqlcmd library with error number -2147467259.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

— EXEC emailAbsentLoop_Tmp

alter Procedure [dbo].[emailAbsentLoop_Tmp] as

Declare @SupervisorID varchar(20)

DECLARE @employeeid NVARCHAR(50)

DECLARE @name NVARCHAR(256)

DECLARE @emailid NVARCHAR(256)

DECLARE @absdate varchar(10)

declare @bdy NVARCHAR(1000)

Declare @query varchar(max)

declare @SupervisorName NVARCHAR(100)

DECLARE XSupervisor CURSOR FOR

select j.EmpSupervisor from EmpJobDetails j

inner join NameListQry n on j.EmpSupervisor=n.EmployeeID

where len(j.empsupervisor)>0 and j.current_=1 and len(j.emailname)>0

group by j.EmpSupervisor

OPEN XSupervisor

FETCH NEXT FROM XSupervisor INTO @SupervisorID

WHILE @@FETCH_STATUS=0 BEGIN

—EXEC [dbo].[EmailAbsEmployees] @SupervisorID

—————begin EmailAbsEmployees——————————————

delete attended

insert into Attended(employeeid,empName,TranDate,TranTime)

SELECT n.employeeid,n.name as empName,TranDate,TranTime FROM biotime854.dbo.swipes s

inner join NameListQry n on s.EmployeeID collate SQL_Latin1_General_CP1256_CI_AS=n.EmployeeID

where convert(varchar,trandate,103)=convert(varchar,getdate(),103)

and timecard=1 and active=1 and EmpSupervisor=@SupervisorID

delete EmailAbsents

insert into EmailAbsents(EmployeeID,Name,SupervisorName,SupervisorEmail,Today)

select a.EmployeeID, Name,SupervisorName,j.EmailName as SupervisorEmail, Convert(varchar,GETDATE(),103) as Today

from NameListQry a inner join EmpJobDetails j on a.EmpSupervisor=j.EmployeeID

left join Attended t on a.EmployeeID collate Arabic_CI_AS=t.EmployeeID

where t.EmployeeID is null and j.Current_=1 and

(j.EmailName!=null or j.emailname!=») and a.EmpSupervisor=@SupervisorID

order by t.employeeid

DECLARE db_cursor CURSOR FOR

—Absent Employees

select EmployeeID,Name,SupervisorName,SupervisorEmail,Today from EmailAbsents

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @employeeid,@name,@SupervisorName,@emailid,@absdate

WHILE @@FETCH_STATUS = 0

BEGIN

set @bdy=

‘Dear ‘ + @SupervisorName + N’

‘ + N’

We have noticed that you were absent at this date ‘ + convert(varchar(10),@absdate,103) + N’.

‘ + N’

If you were present on ‘ + convert(varchar(10),@absdate,103) + N’ Please fill out the Missing Punch Form else if you are on leave and it has been approved then Ignore this msg. ‘ + N’

‘ + N’

Thank You.’

EXEC msdb.dbo.sp_send_dbmail

@profile_name=’OnTimeProfile’,

@recipients=’nest@nestontime.com’,

@subject=’Absent Record/Missing Punch’,

@body=@bdy,

@query=’Select * from EmailAbsents’,

@query_result_header = 0,

@exclude_query_output = 0,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename=’Absentees.csv’,

—@query_no_truncate = 1,

@query_result_no_padding = 1,

@body_format = ‘HTML’ ;

FETCH NEXT FROM db_cursor INTO @employeeid,@name,@SupervisorName,@emailid,@absdate

END

CLOSE db_cursor

DEALLOCATE db_cursor

—————END EmailAbsEmployees———————————————

FETCH NEXT FROM XSupervisor INTO @SupervisorID

END

CLOSE XSupervisor

DEALLOCATE XSupervisor

Источник

1. The SQL Server service account didn’t have sufficient privileges on the domain.

2. The executing user didn’t have sufficient privileges in the query database.

3. The query is executing in the wrong database.

Failed to initialize sqlcmd library with error number -2147467259

Failed to initialize sqlcmd library with error number 2147467259

Вопрос

msdb.dbo.sp_send_dbmail — Failed to initlaize sqlcmd 2147467259

Popular Topics in Microsoft SQL Server

Read these next.

Snap! — LifeLock Breach, Secret AI, Reversible Aging, Brain Fog?

File Permissions

Spark! Pro series – 16th January 2023

How do you like to learn?

poor wifi, school’s third floor

Failed to initialize sqlcmd library with error number -2147467259

Culprit

Solution #1

Solution #2

Conclusion