SQL Server Stuff
I have had to automate some thing with native SQL Server and found a couple of good links for running a DTS package and sending mail from a stored procedure. Here are the links:
http://www.sqldev.net/xp/xpsmtp.htm
http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1
Additional Code after the jump
Run DTS from stored procedure
| Code: |
--Creating the DTS Package Object:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
--Loading the Package:
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("MyServer", "", "", 256, , , , "MyPackage")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN
END
--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
|
|
|