How to identify which statement(query)( within List Of Queries) failed to Execute

[Nitin Daphale]

Hi all,

I am executing query for SQL Server 2008 like that ,

myQuery=  Insert into Employee(id,name)values(' 99 ' ,' Nitin Daphale ');Insert into Employee(id,name)values('77' ,'XYZ');Insert into Employee(id,name)values(' 88 ' ,'ABC');

Here am Inserting 3 Records in an Employee table
but the problem is, if any statement fails I can't identify that statement(s).

Can anybody suggest me any idea to do this? Thanks in advance.

[Zinderin]

Hi all,

I am executing query for SQL Server 2008 like that ,

myQuery=  Insert into Employee(id,name)values(' 99 ' ,' Nitin Daphale ');Insert into Employee(id,name)values('77' ,'XYZ');Insert into Employee(id,name)values(' 88 ' ,'ABC');

Here am Inserting 3 Records in an Employee table
but the problem is, if any statement fails I can't identify that statement(s).

Can anybody suggest me any idea to do this? Thanks in advance.

The only way to do this is to break down each insert into its own little block of code, and test the rows effected after each insert, or return the id of the created row, or count the number of rows in the the table before and after (whatever is most effective for you).

Unless you're doing a massive amount of batch inserts (1000s at a shot), breaking them down into individual blocks of code with their own testing, trapping and reporting is not going to be a hit on your performance.

[Lusiphur]

Unless you're doing a massive amount of batch inserts (1000s at a shot), breaking them down into individual blocks of code with their own testing, trapping and reporting is not going to be a hit on your performance.

And realistically, ease of use > performance even then.

But yes, as Zinderin said easiest way is to separate them and capture affected rows from each to get the confirmation of the firing of the statement. Alternately... if you MUST batch them together like that... you can always set up some return variables (one per statement) that return a single row of response data confirming the data submission/retrieval but then you need to modify your application code to accomodate a different expected output from your query.

Hope that helps. :)

[Nitin Daphale]

And realistically, ease of use > performance even then.

But yes, as Zinderin said easiest way is to separate them and capture affected rows from each to get the confirmation of the firing of the statement. Alternately... if you MUST batch them together like that... you can always set up some return variables (one per statement) that return a single row of response data confirming the data submission/retrieval but then you need to modify your application code to accomodate a different expected output from your query.

Hope that helps. :)

' Zinderin ' and ' Lucipher ' thanks u both.

Separating statements( 'Zindering' suggested ) which I had already applyied in my project
and 'counting no of rows of the table' will be wrong because there are about 60 users of my application using it in parallel.

I was unable to get 'Lucipher' .
According to 'Lucipher', am I able to get result in a batch in which I can identify failed queries? ( rough code will be more helpful ).

Anyway I am very thankful to u both for ur response.
Thanks...