0

I got an error while trying to send an email:

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

Here is my query:

DECLARE @Table TABLE (
server_name varchar(50),
database_name varchar(20),
byuser varchar(50),
size_mb numeric (20,0),
size_gb decimal (9,2),
backup_started datetime,
backup_finished datetime, 
total_time varchar(50),
expiration_days INT,
isdamaged bit,
iscompressed bit,
iscopyonly bit,
databasecreationtime datetime,
physicaldevicename varchar(100),
isolderthanaday bit
);
insert into @Table (server_name,database_name,byuser,size_mb,size_gb,backup_started,backup_finished,total_time,expiration_days,isdamaged,iscompressed,iscopyonly,databasecreationtime,physicaldevicename,isolderthanaday)
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command = 
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg'' 
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 1 ELSE 0 END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))  
AND bkup.type = ''D''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notifications',
    @recipients = 'example@example.com',
    @subject = 'Full backup in last 24 hours',
    @query = N'select * from @Table',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ',';

I tried setting max byte size for mail to 10MB but that didn't help. The problem is associated with @query paramteter. What would you suggest?

P.S sp_ineachdb is a customized procedure

  • 1
    At first glance, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either. – Scott Hodgin Sep 12 at 19:50
  • Thanks for your suggestions and help. Yep, you are right. I need to create a real table for that. – RaufDBA Sep 12 at 19:53
3

From your example, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.