3/7/2007

SQL Server Stuff

Filed under: General — russell @ 4:45 pm

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