Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Unanswered: Execute DTS in Query Analyzer

    I am trying to execute a DTS package from the query analyzer and eventually putting that code into a stored procedure. The DTS package is a simple import from a text file into a table. When I execute the package from the Enteprise Manager it works fine. When I try to execute it from the Query Analyzer it says the 'The command(s) completed successfully.' however no data was inserted. Is there anything I need to do to the package to enable it to be executed externally? Please help!

    Here is the code that I am using:

    DECLARE @oPKG int
    DECLARE @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, 'LoadFromSqlServer', NULL,
    @ServerName='MyServer', @PackageName='MyDTSPackage', @Flags=256
    IF @hr <> 0
    BEGIN
    PRINT '*** Load Package failed'
    RETURN
    END

    --Executing the Package:
    EXEC @hr = sp_OAMethod @oPKG, 'Execute'
    IF @hr <> 0
    BEGIN
    PRINT '*** Execute failed'
    RETURN
    END

    --Cleaning up:
    EXEC @hr = sp_OADestroy @oPKG
    IF @hr <> 0
    BEGIN
    PRINT '*** Destroy Package failed'
    RETURN
    END

    If anyone can help, I'd really appreciate it. Thanks in advance.

  2. #2
    Join Date
    May 2002
    Posts
    10

    Re: Execute DTS in Query Analyzer

    Hey Dkwon

    I've had exactly the same problem as you. I never got the package to work through Query Analyzer, but I think I know what causes it.

    When you run the package through Enterpirse manager you are using your own login and therefore have access to objects that your login has permission to access.

    As soon as you use sp_OACreate it creates an instance of the OLE object (in this case a DTS package) on the server. Thus, the package is being run from the server, and the server may not have permission to execute the package. Try run the package through enterprise manager on the server and see what happens?

    Hope this helps. Let us know how you fare.

    Lionel

  3. #3
    Join Date
    Oct 2002
    Posts
    1
    I'm still looking for a solution to this problem if anyone has one. How do I get the server to have the right permission? --John

Posting Permissions

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