小红帽恰恰 塞拉维:如何在VC6.0中使用DTS

存储过程例子如下：

FROM sysobjects
WHERE name = N'test2'
AND type = 'P')
DROP PROCEDURE test2
GO

CREATE PROCEDURE test2 AS
DECLARE @object int
DECLARE @hr int
declare @GVOutput int

begin

print 'start to create DTS.Pachage'
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
exec sp_OAGetErrorInfo
RETURN
END
print 'create DTS.Pachage successful'

print 'start to LoadFromSQLServer'
EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer',NULL,
@ServerName='cybics-wws\sqlserver', @PackageName='delete', @Flags=256
IF @hr <> 0
BEGIN
print 'error LoadFromSQLServer'
exec sp_OAGetErrorInfo
RETURN
END
print 'LoadFromSQLServer successful'

print 'start to set property'
EXEC @hr = sp_OASetProperty @object, 'GlobalVariables("corpid").Value',1
IF @hr <> 0
BEGIN
print 'error set property'
exec sp_OAGetErrorInfo
RETURN
END
print 'set property successful'

EXEC @hr = sp_OAGetProperty @object, 'GlobalVariables("corpid").Value', @GVOutput OUT
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Read Failed'
EXEC sp_OAGetErrorInfo
RETURN
END
PRINT @GVOutput

print 'start to execute'
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Execute failed'
exec sp_OAGetErrorInfo
RETURN
END
print 'execute successful'

print 'start to destroy'
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'Destroy Package failed'
exec sp_OAGetErrorInfo
RETURN
END
print 'destroy successful'

end
GO

如果你的DTS没有全局变量，中间设置全局变量的代码可以省去