Sql Query

[Patrick Bonneau]

Is there away to make a sql query with a batch file?

If so, what is the utility for doing this?

 

[Ray at]

Use osql.exe in your batch, which you should have from any SQL Server
installation, including the "client tools only" install.

osql -?

usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted
Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Ray at work

 

[Barney]

We dont have an SQL server - we have an AS400 running
DB2. Where can I download osql.exe?

-----Original Message-----
Use osql.exe in your batch, which you should have from any SQL Server
installation, including the "client tools only" install.

osql -?

usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted
Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Ray at work

Is there away to make a sql query with a batch file?

If so, what is the utility for doing this?

.

 

[Ray at]

osql won't connect to an AS/400, only SQL Server, AFAIK. Are you familiar
with VB Script at all? You could ~~try~~ something like this after
registering cscript to be the default script host. (cscript.exe
//H:CScript)


csql.vbs:

If WScript.Arguments.Count > 0 Then Call ReturnRS(WScript.Arguments(0))


''connection string from www.connectionstrings.com
Const CONNECTION_STRING = "Provider=DB2OLEDB;Network Transport
Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package
Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"
Function ReturnRS(sql)
Dim oADO, oRS
Set oADO = CreateObject("ADODB.Connection")
oADO.Open CONNECTION_STRING
Set oRS = oADO.Execute(sql)
If Not oRS.EOF Then ReturnRS = oRS.GetString(2,,vbTab,vbCrLf)
oADO.Close
Set oADO = Nothing
End Function



And then call it with:

csql "select something from somwhere where something='something'"

This is probably far from ideal though! There's probably a command prompt
"osql" for DB/2 out there somewhere already...

Ray at work

We dont have an SQL server - we have an AS400 running
DB2. Where can I download osql.exe?

-----Original Message-----
Use osql.exe in your batch, which you should have from any SQL Server
installation, including the "client tools only" install.

osql -?

usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted
Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Ray at work

Is there away to make a sql query with a batch file?

If so, what is the utility for doing this?

.

 

[Ray Costanzo [MVP]]

Oops. I forgot to write the recordset... That would probably be helpful.

If WScript.Arguments.Count > 0 Then WScript.StdOut.Write
ReturnRS(WScript.Arguments(0))

INSTEAD OF

If WScript.Arguments.Count > 0 Then Call ReturnRS(WScript.Arguments(0))


Ray at home

osql won't connect to an AS/400, only SQL Server, AFAIK. Are you familiar
with VB Script at all? You could ~~try~~ something like this after
registering cscript to be the default script host. (cscript.exe
//H:CScript)


csql.vbs:

If WScript.Arguments.Count > 0 Then Call ReturnRS(WScript.Arguments(0))


''connection string from www.connectionstrings.com
Const CONNECTION_STRING = "Provider=DB2OLEDB;Network Transport
Library=TCPIP;Network Address=XXX.XXX.XXX.XXX;Initial Catalog=MyCtlg;Package
Collection=MyPkgCol;Default Schema=Schema;User ID=MyUser;Password=MyPW"
Function ReturnRS(sql)
Dim oADO, oRS
Set oADO = CreateObject("ADODB.Connection")
oADO.Open CONNECTION_STRING
Set oRS = oADO.Execute(sql)
If Not oRS.EOF Then ReturnRS = oRS.GetString(2,,vbTab,vbCrLf)
oADO.Close
Set oADO = Nothing
End Function



And then call it with:

csql "select something from somwhere where something='something'"

This is probably far from ideal though! There's probably a command prompt
"osql" for DB/2 out there somewhere already...

Ray at work

We dont have an SQL server - we have an AS400 running
DB2. Where can I download osql.exe?

-----Original Message-----
Use osql.exe in your batch, which you should have from any SQL Server
installation, including the "client tools only" install.

osql -?

usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted
Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]

Ray at work

Is there away to make a sql query with a batch file?

If so, what is the utility for doing this?



.