Re: Open and Run SQL Script in Query Analyzer

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


<billy.rogers@xxxxxxxxxxxxxxxxxxx> wrote in message
news:1190916302.499139.237120@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm sorry i don't understand this line. How would I make it work with
my query?


strCmd = "%comspec% /c osql -S MyServer\MyInstance -d MyDatabase -E -
i
Query.sql"


You need to know the name of the SQL Server and the name of the Instance (if
any). If the *.sql file does not specify the database (with USE MyDatabase),
then you also must specify the name of the database. On my computers, the
isqlw command is not recognized. I use the osql command to run queries.
Perhaps try:
===========
' Specify the *.sql file with the query to run.
strSQL = """N:\Prism\Dallas\Groups\acerteza\CODES AND QA
DATABASES\VAR-ASSOC\QA CODES\QA VAR_ASSOC AGENTCOUNT.sql"""
' Run the query.
strCmd = "%comspec% /k osql -S MyServer\MyInstance -d MyDatabase -E -i " &
strSQL
===========
I found that the /c parameter I used before made the command window close
before you could read anything. The /c is used if the command redirects the
output to a text file. The /k parameter leaves the command window open until
you Exit. The above assumes your SQL Server is "MyServer", your instance
name is "MyInstance" and the database name is "MyDatabase". If the *.sql
file specifies the database (USE MyDatabase), then you can omit the "-d
MyDatabase" part. If you do not have an Instance name (default instance),
use "-S MyServer" with no backslash and instance name. To redirect the
output to a text file you can use:

strCmd = "%comspec% /c osql -S MyServer\MyInstance -d MyDatabase -i -E " &
strSQL & " > report.txt"

Finally, I just discovered that osql does not work with SQL Server 2005. The
error message suggests using the command sqlcmd instead. Of course that
command is not recognized on SQL Server 2000, so that really stinks.

If you need to use SQL Authentication and want to hard code the SQL
credentials in the script, perhaps you can use:

strCmd = "%comspec% /k osql -S MyServer\MyInstance -d MyDatabase -U
username -P password -i " & strSQL

You can check syntax for osql at a command prompt of any computer with SQL
Server installed with the command:

osql -?

--
Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--


.

Quantcast