ignore sql execution errors

Question

ignore sql execution errors

amigoface

SSC Eights!

Points: 991
More actions
December 4, 2011 at 8:18 am

#247881

hi,
i am using this code to execute a large insert script :
string connectionString = "mydata string connction";
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(mylargeinsertscript);
unfortunally this script that contain a large number of insert instructions raise exception somewhere (because some reason) ...
is it possible to bypass this exception and continue to execute next insert operations?
thanks and good day .

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

There are several option you could use:

1) instead of sending the large script from your app, store it in SQL Server (as a stored procedure) and just call the sproc. In the sproc you could add TRY...CATCH blocks to deal with errors.

2) run the statements separately

I'd go for the first option for several reasons:

The script can be easily tested and tuned at the DB level making sure it works properly and consistent.

Both scirpts (at the frontend as well at the db) will be easier to maintain.

The sproc could help to prevent frequent recompilation of the adhoc statement.

... list to be continued ...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amigoface SSC Eights! Points: 991 More actions · Answer 2

this script is executed only one time to populate a large table ,

so if i understand the body of the stored procedure may look like :

try
execute the large number of insert script
catch

right ?

but what heppens when an exception raise on some places in the insert instructions ?

i want to just continue with the other inserts

LutzM SSC Guru Points: 107049 More actions · Answer 3

Slightly different:

Inside the sproc you'd have multiple TRY CATCH blocks. One per block of statements you want to control separately.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amigoface SSC Eights! Points: 991 More actions · Answer 4

i think that i have found what was wrong in my script

the date format was DD.MM.YYYY , it should be MM.DD.YYYY

so date like 01.01.1984 was insert fine unlike dates like 14.02.1990 🙂

thanks for your time

really appreciate your help

LutzM SSC Guru Points: 107049 More actions · Answer 5

amigoface (12/4/2011)
i think that i have found what was wrong in my script
the date format was DD.MM.YYYY , it should be MM.DD.YYYY
so date like 01.01.1984 was insert fine unlike dates like 14.02.1990 🙂
thanks for your time
really appreciate your help

When you insert date values, the best way is to use the format 'YYYYMMDD' since it will always be used correctly, whereas the formats you're using currently depends on the setting of the dateformat. This may change if someone decide to change the language assigned to the SQL Server login you're using.

Even though your current issue is resolved, I still recommend to move the code to the SQL Server level.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]