原文:http://msdn.microsoft.com/msdnmag/issues/04/04/DataPoints/default.aspx
这篇FQA概述了写T-Sql语句时经常碰到的问题,还有用SqlServer发送电子邮件的源码下载,一个字"牛".
#"关于触发器的判断语句该怎么写?"小弟很欣赏这个话题.
#"第二个话题是Sql语句如果调用Com组件?"其中发送电子邮件的源码实在太好了.小弟在里贴出来方便自己以后使用.
ALTER PROCEDURE prSendMail @sTo VARCHAR(500), @sBody VARCHAR(8000), @sSubject VARCHAR(500), @sFrom VARCHAR(500) AS DECLARE @nCDO INT, @nOLEResult INT, @nOutput INT, @sSource VARCHAR(255), @sDescription VARCHAR(255), @sProgID VARCHAR(50), @sMsg VARCHAR(250) SET @sProgID = 'CDO.Message' EXECUTE @nOLEResult = sp_OACreate @sProgID, @nCDO OUT IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error creating object ' + @sProgID + '. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'To', @sTo IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error executing CDO.To property. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'From', @sFrom IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error executing CDO.From property. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'Subject', @sSubject IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error executing CDO.Subject property. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'TextBody', @sBody IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error executing CDO.TextBody property. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END —Call Send method of the object EXECUTE @nOLEResult = sp_OAMethod @nCDO, 'Send', Null IF @nOLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT SET @sMsg = 'Error executing CDO.Send method . Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) + '. Description = ' + @sDescription RAISERROR (@sMsg, 16, 1) RETURN END —Destroy CDO EXECUTE @nOLEResult = sp_OADestroy @nCDO RETURN @nOLEResult GO