ansaurus

Question

Answer 1

+1 A:

You could try breaking your stored procedure up into several smaller pieces and wrapping them all in a transaction.

TLiebe 2010-06-18 21:03:40

No.. I mean how to control sql script's runtime from your c# code?

Ike 2010-06-19 18:50:37

That's not exactly a storeproc. I'm trying to run a script generated by Management Studio's wizard from my c# code to create a database based on that script. Sometimes for some reason it throws an exception. But I'd like to control the script's lifetime and skip those problematic pieces and also I'd like to inform user in my app's log which part of the script (actual t-sql statement) is working right at the moment.Theoretically it's possible, right?

Ike 2010-06-21 13:22:25

If you modify the script that the wizard generated you could insert a variable and update it periodically so you can track the progress. If you insert code to check for errors (use @@ERROR) after every statement in your script, you can exit the script and pass back the error and the progress variable to the calling program. As for reporting progress, the best way I can think to do that is to break up the script from the wizard into several smaller pieces, call them sequentially and report the progress back to the user after each piece is executed.

TLiebe 2010-06-21 14:37:57

Yeah I split entire script to smaller parts. Didn't know that GO command is actually not exactly t-sql command. I was surprised at first why Database.ExecuteNonQuery() takes the entire script without problems, but similar SqlCommand.ExecuteNonQuery() doesn't.

Ike 2010-06-22 00:37:03

Answer 2

A:

Sure you can. Use

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
[ ; ]

http://msdn.microsoft.com/es-es/library/ms188378.aspx

With saved points, you can have grain control over which statements commit or not.

And at the end, you can return this information to your app by out variables or a table with information.

Fraga 2010-06-18 21:04:23

I mean if I try to run a script that was generated by SQL Management script wizard, from my c# code using something like ExecutNonQuery or something else. How can I control it?

Ike 2010-06-19 18:57:22

ansaurus

tags:

views:

answers:

How to control ExecuteNonQuery

related questions