--調用方法
print 'start to LoadFromSQLServer'
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer',NULL,
@ServerName='localhost', @PackageName='testdts', @Flags=256
IF @hr <> 0
BEGIN
print 'error LoadFromSQLServer'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'LoadFromSQLServer successful'
--設置全局屬性(如果DTS有的話)
print 'start to set property'
EXEC @hr = sp_OASetProperty @object, 'GlobalVariables("var_1").Value',123
IF @hr <> 0
BEGIN
print 'error set property'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'set property successful'
--運行DTS
print 'start to execute'
exec @hr = sp_OAMethod @object,'Execute'
if @hr <> 0
begiN
print 'Execute Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
returN
enD
print 'Execute Successful'
--銷毀對象
EXEC @hr = sp_OADestroy @objecT
IF @hr <> 0
BEGIN
PRINT 'Destroy Package failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
print 'destroy successful'
GO
以上介紹了調用存儲過程及DTS的方法,可靈活應用於程式開發中.
