Microsoft SQL Server forum - osql batch processing without -i

Author

osql batch processing without -i

2006-01-16, 8:24 pm

Hi,
I have just started using MSSQL and the DOS environment at work. I have
a lot of experience with Sybase and the UNIX environment, but this is a
whole new ball of wax.
I'd like to use osql from a batch file to log into the dataserver and
run a fairly long list of SQL and then exit. I don't want to have a bunch
of SQL files sitting around that I have to use the -i option to run, and
I'd rather not create temporary SQL files like this.

echo "exec sp_who2" >tmp.sql
echo "select * from....... " >>tmp.sql
osql -E -S <DATASERVER> -n -w999 -i tmp.sql
del tmp.sql

I can't use the -Q option, of course because as I said, I'll be writing
quite a few lines of SQL, and it won't all fit on the one line, or at least
it wouldn't be pretty if I did.

In UNIX, I can simply execute the following from either the command line
or in
a script.

isql -S<DATASERVER> -U<USER> <<-EOF

sp_who
go
select * from .....
go
EOF --EOF is the isql session terminator exits me back to the command line.

This behavior does not appear to work with OSQL. There is a -O option
which help mentions, and that talks about disabling the EOF terminator for
batch processing, but I wasn't able to find any usage or examples on the net
where someone is using EOF to terminate their OSQL SQL batch.

This is what help listed.
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1

Any help you could offer would be appreciated. Thanks.

Darren

Hugo Kornelis

2006-01-17, 8:23 pm

On Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@comcast.net> wrote:

(snip)
> In UNIX, I can simply execute the following from either the command line
>or in
>a script.
>
>isql -S<DATASERVER> -U<USER> <<-EOF
>
>sp_who
>go
>select * from .....
>go
>EOF --EOF is the isql session terminator exits me back to the command line.
>
> This behavior does not appear to work with OSQL.

Hi,

It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
utilities take their input from STDIN, which is either the next line in
the script (if run from a script) or the next line entered on the
console (if run from a console).

The same thing happens if you create a script (testme.bat) with the
following content:

copy con test.txt
This won't work

Execute it. The copy con command is started and the console will wait
for your input. Type one or more lines, then type Ctrl-Z (the EOF
marker). You'll next see an error because "Thins won't work" is not a
valid DOS command.

An equivalent script in Unix would enter "This won't work" in the file
test.txt.

> Any help you could offer would be appreciated.

I think that you'll have to settle for either a bunch of commonly used
SQL script files, or for dynamically building the SQL script using a
bunch of redirected echo statements.

--
Hugo Kornelis, SQL Server MVP

2006-01-19, 8:24 pm

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info> wrote in message
news:raiqs1pfephh7fa
ud9sesnjot56c7tdng9@
4ax.com...
> On Mon, 16 Jan 2006 18:44:19 -0600, <dphillip79@comcast.net> wrote:
>
> (snip)
line[color=darkred]
line.[color=darkred]
>
> Hi,
>
> It's not OSQL, it's Windows/DOS that is causing the difference. In UNIX,
> utilities take their input from STDIN, which is either the next line in
> the script (if run from a script) or the next line entered on the
> console (if run from a console).
>
> The same thing happens if you create a script (testme.bat) with the
> following content:
>
> copy con test.txt
> This won't work
>
> Execute it. The copy con command is started and the console will wait
> for your input. Type one or more lines, then type Ctrl-Z (the EOF
> marker). You'll next see an error because "Thins won't work" is not a
> valid DOS command.
>
> An equivalent script in Unix would enter "This won't work" in the file
> test.txt.
>
>
> I think that you'll have to settle for either a bunch of commonly used
> SQL script files, or for dynamically building the SQL script using a
> bunch of redirected echo statements.
>

Thanks for the Info Hugo. I was afraid of that. I'm used being able to
do whatever I want in UNIX, so DOS seems to be a bit of a step back for me.
I did just download a version of sed for DOS, which I'm really excited
about. :) I also have a VB .net class next week, so I'm hoping to pick up
some tricks in there. Thanks again.

Darren

> --
> Hugo Kornelis, SQL Server MVP

Sponsored Links