如何解決郵件問題無法初始化 sqlcmd 庫，錯誤號為 -2147467259？

我在嘗試發送電子郵件時遇到錯誤：

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

這是我的查詢：

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 = ',';

我嘗試將郵件的最大字節大小設置為 10MB，但這並沒有幫助。該問題與@query 參數有關。你有什麼建議？

PS sp_ineachdb 是自定義程序

在您的範例中，您嘗試將查詢結果插入到表變數中，然後嘗試在 sp_send_mail 過程呼叫中使用該表變數。那是行不通的。sp_send_mail 也需要引用真實表而不是臨時表。

引用自：https://dba.stackexchange.com/questions/248670