Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Red face Unanswered: sp_OAMethod @oPKG 'Execute' problem

    Hi All!
    I try to execute DTS package which suppose to export table to flat file from SQL script. I'm not getting any errors from script but it looks like package is not running. The Package works fine from EM and the same script works fine on VBscirpt.

    Thanks.

    Script:

    declare @oPKG int,
    @hr int

    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
    IF @hr <> 0
    BEGIN
    PRINT '*** Create Package object failed'
    RETURN
    END

    EXEC @hr = sp_OAMethod @oPKG OUT, 'LoadFromSqlServer', NULL,
    @ServerName='DevServer', @PackageName='ExportToFlatFile', @Flags=256
    IF @hr <> 0
    BEGIN
    PRINT '*** Load Package failed'
    RETURN
    END

    EXEC @hr = sp_OAMethod @oPKG, 'Execute'
    IF @hr <> 0
    PRINT '*** Execute failed'

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried creating a job for the dts package (it might already exist) and running the sp_start_job stored procedure ?

  3. #3
    Join Date
    Dec 2002
    Posts
    3
    Hi,
    Thanks.Sure, I tried to run it in sp_start_job stored procedure before, but I found out the problem now. It requires full network path and also to figure out error I set up FailOnError packages property

    EXEC @hr = sp_OASetProperty @oPKG, 'FailOnError', 'true'
    IF @hr <> 0
    BEGIN
    PRINT '*** Set up property is failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
    END

    But if execution is failed, I still can not get error description, just a message "sp_OAGetErrorInfo failed" with hex number. Probably it is not possible to find out some errors for Steps using sp_OAGetErrorInfo.

  4. #4
    Join Date
    Dec 2002
    Posts
    3
    Hi,
    Thanks.Sure, I tried to run it in sp_start_job stored procedure before, but I found out the problem now. It requires full network path and also to figure out error I set up FailOnError packages property

    EXEC @hr = sp_OASetProperty @oPKG, 'FailOnError', 'true'
    IF @hr <> 0
    BEGIN
    PRINT '*** Set up property is failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
    END

    But if execution is failed, I still can not get error description, just a message "sp_OAGetErrorInfo failed" with hex number. Probably it is not possible to find out some errors for Steps using sp_OAGetErrorInfo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •