Post reply

Stored Procedure calling a DTS package issues. Seems to only runs successfully on Selects but not on Modifications.

  • July 20, 2007 at 6:06 am

    #177667

    Hi,

    I may be missing something really simple but I'm having an issue running a DTS via a stored procedure.

    I've created a simple DTS to update a field in a table. I run the DTS manually and it works fine. I then cleared the updated records so I can execute the stored procedure to call the same DTS to see if it also works and it seems to just run forever and never completing or updating. I end up killing the processes.

    I then ran the DTS manually again which works and then ran the stored procedure without clearing the updated records and it worked fine (because it had nothing to update.). The sp only seems to work if it has nothing to do???

    I'm calling the DTS using the xp_cmdshell. I've got full rights. All I'm doing is this:

    EXEC master..xp_cmdshell 'dtsrun /Sserver name /E /NDTS name'

    We really don't want to create a job as we're not scheduling. We'll be eventually calling the sp from an external application.

    Hope this makes sense and it's not a stupid question.

    Thanks,

    Jonathan.

  • July 20, 2007 at 6:19 am

    #721078

    why are you using a dts pkg to update a field in table. presumably it is much easier to do in in your stored proc.

    can u give us more background info, so tha we can give you more useful advice

    Everything you can imagine is real.

  • July 20, 2007 at 7:38 am

    #721117

    Hi,

    The DTS package actually does more than just update. I just removed all the steps in the package to test on one simple step. The package is just called test package. The step contains the following simple query:

    UPDATE orders

    SET orders.C_WhenReplicated = '2000/01/01'

    FROM orders

    WHERE

    (orders.ICR like '%44%'

    OR orders. ICR like '%54%')

    AND (orders.C_WhenReplicated = '' OR orders.C_WhenReplicated is NULL)

    The package itself runs fine as you can see nothing can really go wrong with it :-).

    Once this has run I then run the following:

    EXEC master..xp_cmdshell 'dtsrun /S192.23.184.028 /E /Ntest package'

    As mentioned this runs fine as I've already run the DTS manually and nothing needs to be updated.

    However, If I run the package manually then do the following to reset the field in query analyzer:

    update orders

    set [c_whenreplicated] = ''

    where [c_whenreplicated] ''

    and run EXEC master..xp_cmdshell 'dtsrun /S192.23.184.028 /E /Ntest package' in Query Analyzer, the query runs does not seem to stop.

    When I look at the process running in Enterprise Manager, there is an IO_COMPLETION wait type. I have to wait forever and then actually kill this process in Enterprise Manager. When canceling the query in Query Analyzer, it just states attempting to Cancel, please wait and that' it.

    I hope I've given enough info.

    Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply