|
Home > Archive > Microsoft SQL Server forum > July 2005 > Why can't I load my DTS job using sp_OAMethod
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Why can't I load my DTS job using sp_OAMethod
|
|
|
| Hello,
I was trying to user the code below to run a DTS job. The job fails
when I get to the piece of code that begins:
EXEC @rc = sp_OAMethod @PackageToken,
'LoadFromSQLServer',
...
It looks like it tries to look for the DTS package, because it takes 45
seconds to execute when the server name is correct, leading me to
believe that it is properly finding the server. Am "MyDTSPackage" is
the name of I saved my DTS Package under save as. I enter it in the
list below using single quotes just like I enter the server name.
I am wondering if it is a permissions issue. I am running the this
stored procedure from SQL Query Analyzer:
EXEC usp_OATest
(The Stored Procedure gets created without a problem...it is running it
that is causing issues.)
The error message is the custom one that shows at the
Error:
line.
Also, master..xp_cmdshell 'DTSRun....' works fine, but I would like to
be able to use this other method.
Thanks in advance for any help!
Ryan
CREATE PROC usp_OATest
AS
DECLARE @rc int
DECLARE @PackageToken int
DECLARE @GlobalVariableToken
int
--Load DTS Package
EXEC @rc = sp_OACreate 'DTS.Package',
@PackageToken OUTPUT
IF @rc <> 0 GOTO Error
EXEC @rc = sp_OAMethod @PackageToken,
'LoadFromSQLServer',
NULL,
'ServerName',
NULL,
NULL,
256,
NULL,
NULL,
NULL,
'MyDTSPackage'
IF @rc <> 0 GOTO Error <------ERROR DETECTED HERE
--execute package
EXEC @rc = sp_OAMethod @PackageToken,
'Execute'
IF @rc <> 0 GOTO Error
--destroy package
EXEC sp_OADestroy @PackageToken
IF @rc <> 0 GOTO Error
GOTO Done
Error:
EXEC sp_OAGetErrorInfo @PackageToken
RAISERROR('Error during package preparation or execution', 16, 1)
Done:
RETURN(@rc)
GO
EXEC usp_OATest
| |
|
|
|
|
|