Front Office Football Central

Front Office Football Central (https://forums.operationsports.com/fofc//index.php)
-   FOFC Archive (https://forums.operationsports.com/fofc//forumdisplay.php?f=27)
-   -   Running SQL in Batch Files (https://forums.operationsports.com/fofc//showthread.php?t=66571)

MikeVic 07-23-2008 10:17 AM

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. :p

Any help here? :)

Fidatelo 07-23-2008 10:52 AM

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)?

cartman 07-23-2008 10:55 AM

You should be able to check for a return code, and then stop if you get an unexpected result. Is this MSSQL?

Fidatelo 07-23-2008 10:56 AM

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.

Daimyo 07-23-2008 11:00 AM

Unless this is just a one time I thing, I'd highly recommend learning vbscript for things like this.

Fidatelo 07-23-2008 11:03 AM

Quote:

Originally Posted by Daimyo (Post 1788318)
Unless this is just a one time I thing, I'd highly recommend learning vbscript for things like this.


Better yet, Powershell.

MikeVic 07-23-2008 11:08 AM

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. :)

MikeVic 07-23-2008 11:08 AM

Quote:

Originally Posted by Fidatelo (Post 1788323)
Better yet, Powershell.


Yeah, that's what a friend suggested.

MikeVic 07-23-2008 11:23 AM

Quote:

Originally Posted by cartman (Post 1788307)
You should be able to check for a return code, and then stop if you get an unexpected result. Is this MSSQL?


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.

Fidatelo 07-23-2008 11:48 AM

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.

MikeVic 07-23-2008 11:51 AM

Quote:

Originally Posted by Fidatelo (Post 1788363)
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.


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:

But since isql doesn't provide for an automatically-called error handling routine you have to add the code yourself, ie,
add the @@error checking where you think there could be problems.


Not sure this means isql don't have an :onerror equivalent, but I'm sketchy now.

Fidatelo 07-23-2008 12:27 PM

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.

MikeVic 07-23-2008 01:21 PM

Quote:

Originally Posted by Fidatelo (Post 1788401)
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.


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.


All times are GMT -5. The time now is 03:46 PM.

Powered by vBulletin Version 3.6.0
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.