Problem to run delete and DTS in stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem to run delete and DTS in stored procedure

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
You can schedule a job to delete the rows for the step1 using the SP and then on the second step execute the specified DTS package in order to avoid the conflict of locking, if any. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi Satya,
Thanks for your reply.
Well, FYI, i am calling the stored procedure from VB code, while the DTS package is called through the same stored procedure. I dun think i can schedule the job as i dun know when the data will be updated. Thanks Jimmy
Jimmy
So in this case use PROFILER to monitor the activity and also enable DTS package logging in order to assess the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi Satya,<br />Thanks for your help!<br />I manage to solve the problem. It’s mainly because of the transaction where it happens to have implicit and explicit transaction. Explicit is where we declare the BEGIN TRANSACTION while the implicit is hidden in the SELECT, DELETE, UPDATE statement.<br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Jimmy
Hi All,<br />Problem again!! <img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ /><br />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.<br />Attach is the stored procedure and hope someone could help me to solve the problems!!<br />Thanks.<br /><br /><br />BEGIN TRANSACTION<br /><br /> SAVE TRANSACTION MY_TRAN<br /><br /> — ====================================<br /> — Start Performing Records Deleting <br /> — ====================================<br /><br /> SELECT @[email protected] — Implicit Transaction<br /> COMMIT TRANSACTION<br /> EXEC @hr = sp_CallDeleteRecords @sPassValue — Call stored procedure<br /> IF @hr &lt;&gt; 0<br /> BEGIN<br /> GOTO E_OAError<br /> END<br /><br /> — ================================<br /> — Run DTS Package<br /> — ================================<br /><br /> — create a package object<br /> EXEC @hr = sp_OACreate ‘DTS.Package’, @object OUTPUT<br /> if @hr &lt;&gt; 0<br /> BEGIN<br /> GOTO E_OAError<br /> END<br /> <br /> — Load package object<br /> EXEC @hr = sp_OAMethod @object, ‘LoadFromSQLServer ("SERVERNAME", "user", "password", 256, "packagePassword", , , "packageName")’,NULL<br /> IF @hr &lt;&gt; 0<br /> BEGIN<br /> GOTO E_OAError<br /> END<br /> <br /> — Execute package object<br /> EXEC @hr = sp_OAMethod @object, ‘Execute'<br /> IF @hr &lt;&gt; 0<br /> BEGIN<br /> GOTO E_OAError<br /> END<br /><br /><br /> CleanUp:<br /> RETURN<br /> <br /> E_OAError:<br /><br /> ROLLBACK TRANSACTION MY_TRAN<br /> exec dbo.dt_displayoaerror_u @object, @hr<br /> GOTO CleanUp<br /> <br />COMMIT TRANSACTION<br /><br /><br />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.<br /><br /><br />Jimmy
You need to enable transactions in the package (Package Properties)where in the each step that you want to enroll in the transaction you need to set : "Join Transaction if present"
"Rollback Transaction on failure" …in the workflow properties And articles for your refernce:
http://www.databasejournal.com/features/mssql/article.php/3327681
http://www.sqlteam.com/item.asp?ItemID=10627 Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi Satya,
I m sorry, i cant get ur idea clearly especially the "join transaction". Does it mean that the DTS need to join the transaction before it can run? how is the procedure? …could you pls provide more info about this. FYI, the stored procedure and the DB is located in the server A but the DTS package is in server B.
Thanks. Jimmy
I mean to say you can control the transaction inside the DTS package and refer to the links provided above in order to understand how to control the transactions in a DTS package. Either you control the transaction as a whole in the STored procedure or execute DTS package from SP by controlling Transactions inside the package. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Seem like it doesn’t work for me! Jimmy
HI all,
I hv tried the to select the the "JOIN TRANSACTION IF PRESENT", "ROLLBACK TRANSACTION ON FAILURE", but it still cannot rollback the records. why? based on the code i given above, is there anything wrong on it?
Pls advance! Thanks. Jimmy
]]>