xp_sendmail @query 是否只能用##table? 有办法用#table 吗? 定义一临时表,将exec的结果放到该临时表中。insert into #t(...) exec @sql 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 weixy : 如果你说的是将exec xp_sendmail 里的@sql换成#tablewarehouse (如下)那是不可能的。Create table #tablewarehouse(fieldname varchar(10), value varchar(100))insert #tablewarehouse(.....)insert ...insert ...exec master.dbo.xp_startmailexec master.dbo.xp_sendmail @recipients='[email protected]', @query ='select * from #tablewarehouse for xml raw', @message = 'Attached xml file. ', @attach_results = 'TRUE', @width =200, @subject = 'Check Warehouse ' 因为xp_sendmail使用一个单独的联接来执行发送邮件的任务,而#tablewarehouse是一个局部临时表,在不同的连接中是不能共享局部临时表的。所以最早期我是用全局临时表,但全局临时表有它的缺点,所以希望这里的高手能指点能否不用全局临时表。如果是以下这种方式:Create table #tablewarehouse(fieldname varchar(10), value varchar(100))insert #tablewarehouse(.....)insert ...insert ...declare @sql varchar(1000)exec master.dbo.xp_startmail set @sql = 'exec master.dbo.xp_sendmail @recipients=''[email protected]'', @query =''select * from #tablewarehouse for xml raw'', @message =''Attached xml file.'', @attach_results =''TRUE'', @width =200, @subject =''Check Warehouse'''exec @sql错误,提示:is not a valid identifier Declare @sql varchar(1000)declare @warehouse varchar(100)declare @address varchar(100)declare @prefix varchar(100)set @warehouse = '123'set @address= '456'set @prefix = '789'set @sql = 'Select ''<Root><Warehouse>' + @Warehouse + '</Warehouse><Address>' + @Address + '</Address><Prefix>' + @Prefix + ' </Prefix></Root>'''exec master.dbo.xp_startmail exec master.dbo.xp_sendmail @recipients='[email protected]', @query =@sql, @message = 'Attached xml file.', @attach_results = 'TRUE', @width =200, @subject = 'Check Warehouse for variable' 唉,原来可以这么简单....... 关于重建数据库索引的方法 急!行列转换问题 sql server与access数据转换 cast 和convert 的问题 谁能告诉我mssql 2008 r2 到底怎么给不许null的字段设置默认值为空字符串啊 擅长ASP和SQL2000存储过程的高手进来 一个列中的数值如何转换成行显示??? 怎样实现 根据客户分组且取付款金额前十位的客户相应信息的SQL 关于SQL行变列的问题(超级难题) 用SQL SERVER怎样做与ACCESS一样的交叉表查询? 求一个添加主键的SQL 语句! sql server 2000 SP4 升级失败! 弄很久了,希望大家帮下忙!
insert ...
insert ...
exec master.dbo.xp_startmail
exec master.dbo.xp_sendmail @recipients='[email protected]',
@query ='select * from #tablewarehouse for xml raw',
@message = 'Attached xml file. ',
@attach_results = 'TRUE', @width =200,
@subject = 'Check Warehouse ' 因为xp_sendmail使用一个单独的联接来执行发送邮件的任务,而#tablewarehouse是一个局部临时表,在不同的连接中是不能共享局部临时表的。所以最早期我是用全局临时表,但全局临时表有它的缺点,所以希望这里的高手能指点能否不用全局临时表。如果是以下这种方式:
Create table #tablewarehouse(fieldname varchar(10), value varchar(100))
insert #tablewarehouse(.....)
insert ...
insert ...
declare @sql varchar(1000)
exec master.dbo.xp_startmail set @sql = 'exec master.dbo.xp_sendmail @recipients=''[email protected]'',
@query =''select * from #tablewarehouse for xml raw'',
@message =''Attached xml file.'',
@attach_results =''TRUE'', @width =200,
@subject =''Check Warehouse'''
exec @sql错误,提示:is not a valid identifier
declare @warehouse varchar(100)
declare @address varchar(100)
declare @prefix varchar(100)
set @warehouse = '123'
set @address= '456'
set @prefix = '789'
set @sql = 'Select ''<Root><Warehouse>' + @Warehouse + '</Warehouse><Address>' + @Address + '</Address><Prefix>' + @Prefix + ' </Prefix></Root>'''
exec master.dbo.xp_startmail exec master.dbo.xp_sendmail @recipients='[email protected]',
@query =@sql,
@message = 'Attached xml file.',
@attach_results = 'TRUE', @width =200,
@subject = 'Check Warehouse for variable' 唉,原来可以这么简单.......