Hi,
I have encountered some problem in using stored procedure and DTS package.actually i need to update the particular set of records in DB. I use the stored procedure to do the records deletion and then insert the new records using the DTS package. FYI, i am using the stored procedure as well to call the DTS package. however it can not be run, and hang over there till i need to manually kill the sql process in task manager. The command that i use inside the stored procedure are DELETE and EXEC. Is there any priority of execution between these 2 commands? because i found out that the deletion have not complete but the DTS package is starting to run. additional info, i am using transaction as well so that the data will stil remain unchanged if any error occur.
Thanks in advance!

Jimmy

Jimmy

Hello,

How are you calling the stored procedure? In the DTS package, through an application? In addition, how are you calling the DTS package? Through VB code (SQL DTS objects), by scheduling, or by dtsrun in the stored procedure?

Thanks,

Brian

HI Brian,
Thanks for reply.
Well, i am calling the stored procedure through VB code while the DTS package is called using the same stored procedure by DTSRUN.



Jimmy

Hello,

OK. You have to make sure that the account executing the stored procedure has permissions. In addition, that account must also have permissions to execute xp_cmdShell; that is what you are using, correct?

Are you using a SQL login or Integrated security to login with?

Brian

Why not do updates rather than a delete and insert?

If you are not able to use updates, why not use a delete in an "Execute SQL" task in the DTS package. That way you can make sure the delete is committed before starting the inserts. And it can be done on the same connection, which might help your task complete.

Hope this helps!
Jeff

Hi,
Thanks jbelina.
I will try out your sugggestion.
BTW, I manage to solve the problem. It is all because of the TRANSACTION where it happens to have implicit and explicit transaction. FYI, explicit transaction is all we declare as BEGIN TRANSACTION while the implicit is usually occurred during we use DELETE, SELECT, UPDATE...and we need to commit the implicit trasaction first.
:)

Jimmy

Hi All,
Problem again!! :(
I found out the stored procedure that i written can not rollback the records after when i purposely test out the rollback function especially during the calling DTS package.
Attach is the stored procedure and hope someone could help me to solve the problems!!
Thanks.


BEGIN TRANSACTION

    SAVE TRANSACTION MY_TRAN

    -- ====================================
    -- Start Performing Records Deleting
    -- ====================================

    SELECT @sPassValue=@sValueFromVB -- Implicit Transaction
    COMMIT TRANSACTION
    EXEC @hr = sp_CallDeleteRecords @sPassValue -- Call stored procedure
    IF @hr <> 0
    BEGIN
        GOTO E_OAError
    END

    -- ================================
    -- Run DTS Package
    -- ================================

    -- create a package object
    EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
    if @hr <> 0
    BEGIN
        GOTO E_OAError
    END

    -- Load package object
    EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer ("SERVERNAME", "user", "password", 256, "packagePassword", , , "packageName")',NULL
     IF @hr <> 0
    BEGIN
        GOTO E_OAError
    END

    -- Execute package object
    EXEC @hr = sp_OAMethod @object, 'Execute'
    IF @hr <> 0
    BEGIN
              GOTO E_OAError
    END


    CleanUp:
        RETURN

    E_OAError:

        ROLLBACK TRANSACTION MY_TRAN
        exec dbo.dt_displayoaerror_u @object, @hr
        GOTO CleanUp

COMMIT TRANSACTION


p.s. i purposely use another fake server name in DTS calling to test therollback function. however it doesn't rollback the records that have been deleted.



Jimmy

Jimmy,

I believe your DTS package is being executed on a separate SPID, would it be possible to put all the logic (transaction, delete, update, etc.) in your DTS package?

Take care,
Jeff