SEO

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

Without an error message, it's hard to say what the problem might be,
but see here for a link to sample code:

http://www.sqldts.com/default.aspx?210

Simon