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 🙂

  • 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]

  • 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 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 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 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!

  • [citar]

    [segundo]Lowell - Lunes, 18 de febrero de 2019 3:18 PM[/segundo]

    [cita]
    Estoy en la misma situación, he revisado las respuestas que recibió, pero solo logré que se ejecutara el procedimiento, pero no recibí correos electrónicos.

    Lo que he hecho es crear un procedimiento donde armo todo el formato de mi correo electrónico y adjunto un archivo "REPORT.CSV", pero me muestra un error "La biblioteca sqlcmd no se puede inicializar con el número de error -2147467259.", Cuando Al poner @exclude_query_output = 1,
      el procedimiento se ejecuta pero el correo no sale.
    Apoyo por favor, voy días buscando una solución.

    [/ quote]

    cuando agrega @exclude_query_output = 1, ¿hay algún error en el correo fallido?
    Cuando agregue @exclude_query_output = 1, ¿hay algún error en el correo fallido?

    ,
    mail. *
    FROM [msdb]. [dbo]. [sysmail_allitems] mail
    inner join [msdb]. [dbo]. [sysmail_event_log] error
      ON err.mailitem_id = mail.mailitem_id
    DONDE mail.sent_status <> 'enviado'
      orden por mailitem_id desc

    [/ code]

    [/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=1

    Para 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