Getting SQL Script to run in VBS

Hi Everyone,

I am very new to VB Scripting and would like to find out how might I go
about running an SQL (2000) statement through VBScript and then assign the
result into an environment variable. My application can read the environment
variables. Essentially here is the SQL command I have been trying to use
with VB Script:

isql -U sa -P password -Q "select
cast(substring(@@version,0,charindex('(',@@version,0)) as varchar(40)),
cast(substring(@@version,charindex('Build',@@version,0)-1, Len(@@version))
as varchar(35))" -o c:\result\SQLVersion.txt

The SQL statement gives me the SQL Server version and its service pack info.
Right now it writes to a file but I rather keep it in the memory if
possible, and then simply manipulate the text so that it shows only a single
line of text instead of three.

Thank you for any pointers.

Viatcheslav
Sat Feb 07 03:30:17 CST 2004

Instead of using another process (isql), use ADO (this will not use any
files)

Dim conn, rs, strSELECT
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strSELECT = "select " & _
"cast(substring(@@version,0,charindex('(',@@version,0)) as varchar(40)), "
& _
"cast(substring(@@version,charindex('Build',@@version,0)-1, Len(@@version))
" & _
"as varchar(35))"

conn.Open "Provider=SQLOLEDB;User ID=sa;Password=password;Initial
catalog=master;"
rs.Open strSELECT, conn
rs.MoveFirst

Dim strVersion, strSP
strVersion = rs(0)
strSP = rs(1)

rs.Close
conn.Close

WScript.Echo strVersion & " - " & strSP

rs.Close
conn.Close

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com - best scripting/compiled HTA IDE

"Dragon" <NoSpam_Baadil@hotmail.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:eMDaA%23O7DHA.2540@TK2MSFTNGP11.phx.gbl...
> Hi Everyone,
>
> I am very new to VB Scripting and would like to find out how might I go
> about running an SQL (2000) statement through VBScript and then assign the
> result into an environment variable. My application can read the
environment
> variables. Essentially here is the SQL command I have been trying to use
> with VB Script:
>
> isql -U sa -P password -Q "select
> cast(substring(@@version,0,charindex('(',@@version,0)) as varchar(40)),
> cast(substring(@@version,charindex('Build',@@version,0)-1, Len(@@version))
> as varchar(35))" -o c:\result\SQLVersion.txt
>
> The SQL statement gives me the SQL Server version and its service pack
info.
> Right now it writes to a file but I rather keep it in the memory if
> possible, and then simply manipulate the text so that it shows only a
single
> line of text instead of three.
>
> Thank you for any pointers.
>
>

Dragon
Mon Feb 09 12:28:52 CST 2004

Thank you very much for the help. I think this is working great. I do have
one related question though.

I modified the script so that it writes the results to s registry key.
Everything work greate except that I get some unknown character at the end
of my resulting string. I tried to reduce the length of the string in the
Select statement but it doesn't seem to make any difference. Any ideas?

Dim conn, rs, strSELECT
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strSELECT = "select " & _
"cast(substring(@@version,0,charindex('(',@@version,0)) as varchar(40)), "
&_
"cast(substring(@@version,charindex('Build',@@version,0)-1,
Len(@@version)-1)" & _
"as varchar(35))"

conn.Open "Provider=SQLOLEDB;User
ID=sa;Password=password;Initialcatalog=master;"
rs.Open strSELECT, conn
rs.MoveFirst

Dim strVersion, strSP
strVersion = rs(0)
strSP = rs(1)

rs.Close
conn.Close

'WScript.Echo strVersion & " - " & strSP

Set objshell = CreateObject("wscript.shell")
objshell.regwrite
"HKEY_LOCAL_MACHINE\SOFTWARE\MySystems\SQLVersion",strVersion & " - " &
strSP

Dragon
Mon Feb 09 17:25:09 CST 2004

I found the solution. I simply trimmed the char via VBS. :-)

Thank you.

"Dragon" <NoSpam_Baadil@hotmail.com> wrote in message
news:ObWsSqz7DHA.2432@TK2MSFTNGP10.phx.gbl...
> Thank you very much for the help. I think this is working great. I do have
> one related question though.
>
> I modified the script so that it writes the results to s registry key.
> Everything work greate except that I get some unknown character at the end
> of my resulting string. I tried to reduce the length of the string in the
> Select statement but it doesn't seem to make any difference. Any ideas?
>
> Dim conn, rs, strSELECT
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
>
> strSELECT = "select " & _
> "cast(substring(@@version,0,charindex('(',@@version,0)) as varchar(40)),
"
> &_
> "cast(substring(@@version,charindex('Build',@@version,0)-1,
> Len(@@version)-1)" & _
> "as varchar(35))"
>
> conn.Open "Provider=SQLOLEDB;User
> ID=sa;Password=password;Initialcatalog=master;"
> rs.Open strSELECT, conn
> rs.MoveFirst
>
> Dim strVersion, strSP
> strVersion = rs(0)
> strSP = rs(1)
>
> rs.Close
> conn.Close
>
> 'WScript.Echo strVersion & " - " & strSP
>
> Set objshell = CreateObject("wscript.shell")
> objshell.regwrite
> "HKEY_LOCAL_MACHINE\SOFTWARE\MySystems\SQLVersion",strVersion & " - " &
> strSP
>
>
>