Failed to initialize sqlcmd library with error number -2147467259
-
Hi all,
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:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Rapportage',
@recipients = 'dba@unit4.com',
@query =
'
select * from unit4_beheer.[dbo].[ClusterActiveNode]
',
@subject=' Active node Salaris1',
@attach_query_result_as_file = 1
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 🙂
-
Yes,
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.
-
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',
@body = @body1,
@body_format = 'HTML',
@query = 'EXEC ERP.dbo.usp_GenerateERPFile',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ERP.psv',
@query_result_no_padding = 1
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! -
When I run
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Rapportage',
@recipients = 'dba@unit4.com',
@query = 'select * from UNIT4_BEHEER.DBO.ClusterActiveNode',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ERP.psv',
@query_result_no_padding = 1
I get Command completed succesfully. no email.
-
"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 ?
see if this is related to https://support.microsoft.com/en-us/kb/3004195
-
cor_perlee (11/11/2015)
When I runEXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Rapportage',
@recipients = 'dba@unit4.com',
@query = 'select * from UNIT4_BEHEER.DBO.ClusterActiveNode',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'ERP.psv',
@query_result_no_padding = 1
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?
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
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! -
Thanks!
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 🙂
-
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 🙂
-
Buen día;
Estimado.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 -
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 descLowell
--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! - [citar][segundo]Lowell - Lunes, 18 de febrero de 2019 3:18 PM[/segundo]
[/citar]
Eh ejecuto tu query y no me sale nada reciente, solo registro de los errores que estuve probando en la mañana.
-
Estimados.
Acabo de encontrar una solución, aunque varia mucho a lo que pensaba hacer pero a las finales me brinda el mismo resultado.
En el stord donde armo mi correo (correo de destino,asunto y detalle ) eh plasmado los parámetros del proceso de envió de esta manera:
DECLARE @filename1 varchar(max)
SET @filename1 = 'C:\Reporte.csv' (recomiendo que al archivo le brindes permisos)EXEC msdb.dbo.sp_send_dbmail
@profile_name='PERFIL CREADO',
@recipients=@CORREO,
@subject=@ASUNTO,
@body=@MENSAJE,
@body_format = 'HTML' ,
@query_attachment_filename='Reporte.csv',
@execute_query_database = 'BD',
@file_attachments =@filename1,
@query_result_no_padding=1Para ejecutar en un jobs lo eh hecho de esta manera:
-En el primer paso cargo la data en una tabla temporal.
-En el segúndo paso cargo la plantilla @filename1 = 'C:\Reporte.csv' con estos comandos:
sqlcmd -S "SERVIDOR" -d "BD" -E -Q "select * from temp_despa_vale" -o "C:\Reporte.csv" -s"," -w 700- Tercero ejecuto el stord de correo para que envié .
-
You might have better luck getting an answer if you did the translation to English.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
GO
SET QUOTED_IDENTIFIER ON
GO
-- 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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply