Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050)

• EasyBoy

  SSCertifiable

  Points: 5301

  More actions

  May 4, 2016 at 7:52 am

  #326100

  I am running the following script through SSMS and it works fine but while i run through job i am receiving failed to initialize sqlcmd library error. I have search on web and made few changes i.e change @query_result_header = 1 and @query_no_truncate = 1 but somehow it's not wroking for me.

  Can anyone please advice?

  declare @sub as nvarchar(128)

  set @sub = 'Error Logs From ' + convert(varchar(50), getdate() - 1, 121)

  EXEC [msdb].dbo.sp_send_dbmail

  @profile_name ='SQLSRVMON',

  @recipients ='XXXX',

  @subject = @sub,

  @from_address= 'XXXX.com',

  @reply_to = 'XXX.com',

  @body ='Everyone, The following are a list of errors from the previous daY ',

  @body_format='TEXT',

  @execute_query_database ='db_NAME',

  @attach_query_result_as_file = '1',

  @query_attachment_filename = 'output.txt',

  --@query_result_width = 2000,

  @query_result_header = 1,

  @query_no_truncate = 1,

  @query_result_no_padding = 0,

  --@query_no_truncate = 1,

  @query_result_separator = '',

  @query = '

  select '' ID: '' + cast(L.Id as nvarchar(20))+ char(13) + char(10)

  , ''DATE: '' + cast(L.[Date] as nvarchar(20))+ char(13) + char(10)

  , ''THREAD: '' +L.[Thread]+ char(13) + char(10)

  , ''LEVEL: '' + L.[Level] + char(13) + char(10)

  , ''LOGGER: '' + L.[Logger]+ char(13) + char(10)

  , ''METHOD: '' + L.[Method] + char(13) + char(10)

  , ''MESSAGE: '' + L.[Message] + char(13) + char(10) + char(13) + char(10)

  , ''EXCEPTION: '' + L.[Exception] + char(13) + char(10) + char(13) + char(10) + char(13) + char(10) + ''-------------------------------------------------------''

  from [db_name].[dbo].[Log] L with (nolock)

  where L.[Level] = ''ERROR''

  and logger <> ''TextLogging''

  and logger not like ''WebForms%''

  and [date] >= GETDATE() - 1 order by id asc

  '

  Edit:

  This works fine if i remove the @query. Not sure what is the exact problem with this query?

  I have included db_name.schema.table_name.

• SQL2219

  Valued Member

  Points: 69

  More actions

  December 12, 2016 at 9:45 am

  #1917424

  i am having this same truncation issue, have you solved this?

• johnwalker10

  SSCrazy Eights

  Points: 9124

  More actions

  December 14, 2016 at 9:34 pm

  #1918073

  Take a look at this link, hope this will help you out: http://www.sqlservercentral.com/articles/SQLCMD/123681/%5B/url%5D

• mrkhill

  Grasshopper

  Points: 13

  More actions

  November 1, 2018 at 2:59 pm

  #2012119

  I found this info to help me with the same error:

       

  
  original post was failing because SQL didn’t know where to run SELECT * FROM TestTable. The fix is to provide sp_send_dbmail with database context by either fully qualifying your table in the @query parameter:

  @query = 'SELECT id FROM testDB.dbo.TestTable'

  or by providing the optional @execute_query_database parameter:

  @execute_query_database = 'testDB'

  I chose to add the @execute_query_database and it worked!

  Source: Found Here