07-23-2008, 10:17 AM | #1 | ||
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
Running SQL in Batch Files
Ok, so there's this batch file (DOS commands and the like) that creates stored procedures and then runs them with some command-line SQL utility.
The problem is that this batch file has many SQL statements it runs, and if one of the early ones fails, the thing keeps on running, making some of the later statements useless. I want to stop running the batch file if there's some kind of failure when executing one of the stored procedures, but Googling has only brought me really basic information... like whether running SQL in a batch file is possible, which I know it is. Any help here? |
||
07-23-2008, 10:52 AM | #2 |
Pro Starter
Join Date: Nov 2002
Location: Winnipeg, MB
|
Batch File SQL? Ack. I leave that crap to DB Admins
I guess first question would be what type of database, and what command-line utility is being run (ie is it SQL Server using sqlcmd)?
__________________
"Breakfast? Breakfast schmekfast, look at the score for God's sake. It's only the second period and I'm winning 12-2. Breakfasts come and go, Rene, but Hartford, the Whale, they only beat Vancouver maybe once or twice in a lifetime." |
07-23-2008, 10:55 AM | #3 |
Death Herald
Join Date: Nov 2000
Location: Le stelle la notte sono grandi e luminose nel cuore profondo del Texas
|
You should be able to check for a return code, and then stop if you get an unexpected result. Is this MSSQL?
__________________
Thinkin' of a master plan 'Cuz ain't nuthin' but sweat inside my hand So I dig into my pocket, all my money is spent So I dig deeper but still comin' up with lint |
07-23-2008, 10:56 AM | #4 |
Pro Starter
Join Date: Nov 2002
Location: Winnipeg, MB
|
I'm going to assume SQL Server + sqlcmd and point you to: http://msdn.microsoft.com/en-us/library/ms165702.aspx
Look for ":on error [ exit | ignore ]", that seems like it has potential.
__________________
"Breakfast? Breakfast schmekfast, look at the score for God's sake. It's only the second period and I'm winning 12-2. Breakfasts come and go, Rene, but Hartford, the Whale, they only beat Vancouver maybe once or twice in a lifetime." |
07-23-2008, 11:00 AM | #5 |
College Starter
Join Date: Oct 2000
Location: Berkeley
|
Unless this is just a one time I thing, I'd highly recommend learning vbscript for things like this.
|
07-23-2008, 11:03 AM | #6 | |
Pro Starter
Join Date: Nov 2002
Location: Winnipeg, MB
|
Quote:
Better yet, Powershell.
__________________
"Breakfast? Breakfast schmekfast, look at the score for God's sake. It's only the second period and I'm winning 12-2. Breakfasts come and go, Rene, but Hartford, the Whale, they only beat Vancouver maybe once or twice in a lifetime." |
|
07-23-2008, 11:08 AM | #7 |
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
The thing is already written, I've just been asked to improve the error-handling because it ends up wasting people's time when the batch doesn't kick out on the first sign of an error.
Sorry, I should've said it's using something called "isql" to run SQL statements. We're on Sybase. For sqlcmd, this :onerror thing seems like it'll stop the current sqlcmd, but not kick out of the actual .bat file? Or at least that's how I'm reading the description. Anyway, I can't use that for isql. |
07-23-2008, 11:08 AM | #8 |
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
|
07-23-2008, 11:23 AM | #9 | |
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
Quote:
What I've seen done, and might be my only option if I can't find anything else... is to look for errors in the proc, and then print out a line if one is found. Then parse that file. |
|
07-23-2008, 11:48 AM | #10 |
Pro Starter
Join Date: Nov 2002
Location: Winnipeg, MB
|
I think almost any sql command utility will have a way for you to pass back a return value of some sort from the SQL being run. You might just have to check the iSQL docs. Then, in your procs, return that code when you hit errors.
__________________
"Breakfast? Breakfast schmekfast, look at the score for God's sake. It's only the second period and I'm winning 12-2. Breakfasts come and go, Rene, but Hartford, the Whale, they only beat Vancouver maybe once or twice in a lifetime." |
07-23-2008, 11:51 AM | #11 | ||
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
Quote:
Yeah I'm looking for it... I came across this: ISQL Should Exit On First Error in SQL Batch - Database Forum And one of the replies said: Quote:
Not sure this means isql don't have an :onerror equivalent, but I'm sketchy now. |
||
07-23-2008, 12:27 PM | #12 |
Pro Starter
Join Date: Nov 2002
Location: Winnipeg, MB
|
I think they are talking about error handling in-line in the SQL, because the guy's single SQL file has a bunch of statements and he wants to fail out as soon as any one of them fails. He's trying to avoid writing the logic into the SQL script in hope some magic command in iSQL will monitor it for him, but he's out of luck.
The way I see it, you need to have proper error handling in your SQL proc(s), such that they exit out with a specific return code at any sign of error. Then you need to find the way of checking for that return code within your batch file so that it knows to not run any other procs afterwards.
__________________
"Breakfast? Breakfast schmekfast, look at the score for God's sake. It's only the second period and I'm winning 12-2. Breakfasts come and go, Rene, but Hartford, the Whale, they only beat Vancouver maybe once or twice in a lifetime." |
07-23-2008, 01:21 PM | #13 | |
Head Coach
Join Date: Mar 2003
Location: Hometown of Canada
|
Quote:
Ok thanks, that makes sense then. Yeah, I think I'll have to do it with the parsing way I mentioned earlier. I don't think I need any special command from isql to do that. Thanks for the input everyone. |
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|