Query in bat file?

[blueghost73@yahoo.com]

Ok, I know this is a stupid newbie question, but I'm a stupid newbie,
so it fits.

I've got a query that writes a basic report, and I need to be able to
give it to a user who doesn't even know how to spell SQL to run.
Basically, I want her to be able to double click on a bat file on her
computer, and it'll produce an output file based on what the query
finds in the database.

I've got the query in a file with a .sql extension, and it runs fine in
Query Analyzer. I saw an old post when searching the google archives
for this newsgroup, which says to create a .bat file with a command in
the format:

isql -Usa -Ppassword -ic:\test.sql -oc:\test.log

That doesn't work, though. My output file ends up having the error:

DB-Library: Unable to connect: SQL Server is unavailable or does not
exist. Unable to connect: SQL Server does not exist or network access
denied.
Net-Library error 52: ConnectionOpen (Connect()).

I think I understand what the problem is, but I'm not sure what to do
about it. I'm not telling it where the SQL server is. I think the
previous conversation where I got this advice was assuming that this
would all be done on the same machine as the database, when I'm trying
to run this from just any generic PC on the same network as the
database server. How/where do I tell the bat file what server that I
want it to go to? Or am I completely off in thinking that's the
problem?

--Richard

[Erland Sommarskog]

Re: Query in bat file?

(blueghost73@ya hoo.com) writes:[color=blue]
> isql -Usa -Ppassword -ic:\test.sql -oc:\test.log[/color]

You should probably use OSQL instead. With ISQL you get some odd settings
that result in your query giving different results.

And, if possible, use -E to specify Windows Authentication. Giving the
user a batch file with the sa password is not good for your security.
[color=blue]
> That doesn't work, though. My output file ends up having the error:
>
> DB-Library: Unable to connect: SQL Server is unavailable or does not
> exist. Unable to connect: SQL Server does not exist or network access
> denied.
> Net-Library error 52: ConnectionOpen (Connect()).
>
> I think I understand what the problem is, but I'm not sure what to do
> about it. I'm not telling it where the SQL server is.[/color]

So ISQL assumes that is is the default instance on the local machine.
[color=blue]
> How/where do I tell the bat file what server that I want it to go to?[/color]

You use the -S option:

osql -E -S SERVER -ic:\test.sql -oc:\test.log



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[dalst36]

Re: Query in bat file?

try -S SERVERNAME in there too

[blueghost73@yahoo.com]

Re: Query in bat file?

Erland Sommarskog wrote:[color=blue]
> (blueghost73@ya hoo.com) writes:[color=green]
> > isql -Usa -Ppassword -ic:\test.sql -oc:\test.log[/color]
>
> You should probably use OSQL instead. With ISQL you get some odd[/color]
settings[color=blue]
> that result in your query giving different results.[/color]

Ok, I don't know what I'm doing, so I'm willing to try OSQL, just
because you say so. :)[color=blue]
>
> And, if possible, use -E to specify Windows Authentication. Giving[/color]
the[color=blue]
> user a batch file with the sa password is not good for your security.
>[/color]
I'm not THAT much of a clueless newbie. :) We've got other users set up
on the SQL Server, with much less security access. The command I gave
above was just a cut and paste from the previous discussion. I wasn't
putting the sa password in the bat file.
[color=blue][color=green]
> > How/where do I tell the bat file what server that I want it to go[/color][/color]
to?[color=blue]
>
> You use the -S option:
>
> osql -E -S SERVER -ic:\test.sql -oc:\test.log
>[/color]
This is exactly what I was looking for. Unfortunately, we're severely
lacking in documentation around here, so I wasn't sure where to find
out all the options for doing this stuff. This worked great, though.
Thanks for your help!

--Richard

[Craig Kelly]

Re: Query in bat file?


<blueghost73@ya hoo.com> wrote :

<snip>
[color=blue]
> This is exactly what I was looking for. Unfortunately, we're severely
> lacking in documentation around here, so I wasn't sure where to find
> out all the options for doing this stuff. This worked great, though.
> Thanks for your help!
>
> --Richard[/color]

Richard,

FYI, you can run osql -? at the command prompt to see a list of command line
options. In addition, osql (and other command line utilities) are described
in SQL Server Books Online under "Using the SQL Server Tools" in the
sub-topic "Command Prompt Utilities".

If for some reason you don't have BOL, you can download the latest version
here:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Craig

[blueghost73@yahoo.com]

Re: Query in bat file?


Craig Kelly wrote:[color=blue]
> <blueghost73@ya hoo.com> wrote :
>
> <snip>
>[color=green]
> > This is exactly what I was looking for. Unfortunately, we're[/color][/color]
severely[color=blue][color=green]
> > lacking in documentation around here, so I wasn't sure where to[/color][/color]
find[color=blue][color=green]
> > out all the options for doing this stuff. This worked great,[/color][/color]
though.[color=blue][color=green]
> > Thanks for your help!
> >
> > --Richard[/color]
>
> Richard,
>
> FYI, you can run osql -? at the command prompt to see a list of[/color]
command line[color=blue]
> options. In addition, osql (and other command line utilities) are[/color]
described[color=blue]
> in SQL Server Books Online under "Using the SQL Server Tools" in the
> sub-topic "Command Prompt Utilities".
>
> If for some reason you don't have BOL, you can download the latest[/color]
version[color=blue]
> here:
>
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> Craig[/color]

Oddly, I have BOL, but some sections are missing. I may download the
updated version. Thanks for the link.

--Richard