DBmail error - driving crazy

Question

DBmail error - driving crazy

SSC-Dedicated

Points: 36494

November 7, 2020 at 12:34 am

I have spent enough time on this trying to troubleshoot, wanted to check if anyone else has experienced this error message and how did they get around. Thanks.

Msg 22050, Level 16, State 1, Line 0

Failed to initialize sqlcmd library with error number -2147467259.


declare  @report_recipients varchar(1000)        
            
set   @report_recipients='youremail'        
         
         
            
            
            
declare @qry nvarchar(max)         
declare @column1name varchar(100)            
            
declare @filename varchar(400)            
            
declare @sub varchar(400)            
            
            
set @sub='Output in Excel ' + '-'+ CONVERT(VARCHAR(10),GETDATE(),110)            
            
Set @filename= convert( varchar(10),getdate(),110)+'_DeadlockReport.csv'            
            
            
-- Create the column name with the instrucation in a variable            
SET @Column1Name =  CHAR(13) + CHAR(10) + 'ExecutionTime'            
            
            
             
-- Create the query, concatenating the column name as an alias            
select @qry='set nocount on             
        
drop table  if exists #errorlog   

 
CREATE TABLE #errorlog (
            LogDate DATE
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );


DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
INSERT INTO #errorlog EXEC sp_readerrorlog;


SELECT @tag = text
FROM #errorlog
WHERE [Text] LIKE ''Logging%MSSQL\Log%'';


SET @path = SUBSTRING(@tag, 38, CHARINDEX(''MSSQL\Log'', @tag) - 29);
with a as (SELECT
  CONVERT(xml, event_data).query(''/event/data/value/child::*'') AS DeadlockReport,
  convert(date,CONVERT(xml, event_data).value(''(event[@name="xml_deadlock_report"]/@timestamp)[1]'', ''datetime''))
  AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + ''\system_health*.xel'', NULL, NULL, NULL)
WHERE OBJECT_NAME like ''xml_deadlock_report'')
 
select Execution_Time '+@Column1Name+' , 

Count(Execution_Time)
from a
group by Execution_Time
order by Execution_Time 
 
  
  '      
  --print @qry

exec msdb.dbo.sp_send_dbmail         
@profile_name='Database_Mail',        
  
@recipients=@report_recipients,    
@query=@qry
,        
@subject=@sub,        
@attach_query_result_as_file = 1,

@append_query_error=1
,        
@query_attachment_filename = @filename  ,     
@query_result_separator=',',        
@query_result_width =32767,        
@query_result_no_padding=1

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.