DTS - Execute SQL Task

Question

Post reply

DTS - Execute SQL Task

Zepil

SSC Enthusiast

Points: 124
More actions
August 19, 2004 at 11:37 pm

#108585

I created a Execute SQL task to run a store procedure.
The store procedure inserts a number of data into a table. however, when it hits a duplicate key error, the SQL task will terminate instead of continue to execute the rest of the store procedure, while i would like the duplicated transaction to be inserted into a duplicate table.
I use (if @@error <>0) to check for the condition, but it does not even hit this portion.
However, i am able to execute the store procedure successfully using SQL QUERY ANALYZER
Can any1 help me?
I am using SQL2000 server (with so service packs). I thought this problem only exist in SQL 7 server. Am i suppose to upgrade to any of the SPs to solve this problem

Viewing 5 posts - 1 through 4 (of 4 total)

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

Zepil SSC Enthusiast Points: 124 More actions · Answer 1

Zepil

SSC Enthusiast

Points: 124

August 22, 2004 at 7:30 pm

#520114

pls, does any1 has an answer?

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 2

You can't achieve this. When sql server encounters a error (Fatal or non-Fatal Error) - the program terminates accordingly.

Pls follow the link, on how the error handling should be done inside the stored procedure.

http://www.sqlteam.com/item.asp?ItemID=2463

Then when u said you will insert the duplicate records to a different table. Why not check whether duplicate exists before inserting and still get the duplicates inserted to a different table without relying on the @@Error.

Thanks,

Ganesh

Antares686 SSC Guru Points: 125444 More actions · Answer 3

You might consdier using a Data Driven Query task instead so you can use the lookup functionality or build the check and choice of Update/Insert into the SP.

Zepil SSC Enthusiast Points: 124 More actions · Answer 4

Thanks for all the reply.

Its not efficient for me to check for duplicate as the table contains about 3 mil records and per day i have to insert about 200k records per single batch. Duplicates are not expected as those are production, however, error handling have to be handled.

Below is the error i got when i run the procedure :

Server: Msg 2627, Level 14, State 1, Procedure load_txn, Line 58

Violation of PRIMARY KEY constraint 'PK_TXN_LOG'. Cannot insert duplicate key in object 'TXN_LOG'.

The statement has been terminated.

It does not state whether this is a fatal error or non-fatal error, but this only fails with EXECUTE SQL TASK in the DTS.

When i run exec load_txn in SQL QUERY ANALYZER it works perfectly.

Also i use the EXECUTE PROCESS TASK to run C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe which i pass in parameter

/U sa /P password /d databasetable /S servername /Q "exec load_txn" and it executes successfully, written all the duplicates to the duplicate table just like using SQL QUERY ANALYZER.

This leads me to conclude that duplicate transaction is not a fatal error.

I have try to use Data Driven Query task, but guess i am not familiar with it. I need to do certain processing within my processing in the store procedure, eg checking for certain transaction type to determine certain calculation of transaction amount. I am not sure If Data Driven Query task is as flexible as store procedure for data manipulation