osql batch processing without -i

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]

On Mon, 16 Jan 2006 18:44:19 -0600, <dp********@com cast.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

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info> wrote in message
news:ra******** *************** *********@4ax.c om...

On Mon, 16 Jan 2006 18:44:19 -0600, <dp********@com cast.net> wrote:

(snip)

In UNIX, I can simply execute the following from either the command lineor 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.

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