isql/sql script

[Saama5]

There are multiple isql commands in a batch file. Can Sql script returns some value to first isql command? So rest of isql commands execution depends on the output of first isql command. Like true or false.

 

[tlbroadbent]

The best solution is to put all the SQL commands in one .sql file and execute the commands in one ISQL (or OSQL in SQL 7 and above) call. Then you can include various control of flow commands in the SQL batch. You can also use variables and temporary tables in the batch.

It is diffcult to pass the results from one OSQL/ISQL execution to another. I can think of some methods that may work but I would prefer not to have to use them. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Saama5]

I have many batches running in same script so how can I skip them if one condition in one batch is not fulfilled because all of them have seperate go and variables.

 

[tlbroadbent]

Use the -b option on the OSQL/ISQL command line. You can then exit the batch file or take another action if an error ocurs.

SQL BOL has the following info on the -b option:

"Specifies that [osql]/isql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. MS-DOS batch files can test the value of DOS ERRORLEVEL and handle the error appropriately."

2nd option: Create a job with a step for each SQL batch. If a batch fails, you can quit the job.

3rd option: Create a DTS package with workflow between ExecuteSQL tasks that specifies what to do when a task errors. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Saama5]

Thanks! Lot of good suggestions.

 

[csbdeady]

Reading:
"
The best solution is to put all the SQL commands in one .sql file and execute the commands in one ISQL (or OSQL in SQL 7 and above) call. Then you can include various control of flow commands in the SQL batch. You can also use variables and temporary tables in the batch.
"

ok I've got a .sql file - and it's called from a .bat file that issues the i/osql command:

osql -S SERVER -d %1 -E -i c:\doh.sql

How do I pass in a name of a table to that .sql file as a variable?

I have a variable in my .sql called @strBaseObject ready to be used.

Thanks
-Colin