How to use VBScript to return data from SQL Server 2005

Question

Using a variety of sources I put together the following script ...

Dim myStream, myConnection, myCommand
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
'
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"
MsgBox(myConnection.State)
MsgBox(myConnection.Provider)
MsgBox(myConnection.Version)
myCommand.ActiveConnection=myConnection
myCommand.CommandText="SELECT itemsgt,item FROM NIFItem"
MsgBox(myCommand.CommandText)
myStream.Open
myCommand.Properties("Output Stream") = myStream
'myCommand.Properties("xml root") = "root"
myCommand.Execute ,,adExecuteStream
myStream.Position=0
MsgBox(myStream.Charset)
MsgBox(myStream.Size)
MsgBox(myStream.Type)
myStream.Charset="ISO-8859-1"
Dim strxml
strxml = myStream.ReadText
MsgBox (strxml)

Sorry about all the message box but I am trying to debug ... anyway I can run the script, I can see the query execute on my SQL server instance, but nothing is ever returned to the output stream. I have checked the code multiple times and see nothing wrong.

Thanks for any ideas.

Answer 1

Have you tried to debug the vbs from cmd prompt?
Run the script like this:
cscript //X yourscript.vbs

Answer 2

To retrieve the results into a stream, the query needs to include "FOR XML AUTO". Also change the text adExecuteStream to the constant value 1024.

Full Code:

Dim myStream, myConnection, myCommand
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"
myCommand.ActiveConnection=myConnection

myCommand.CommandText="SELECT itemsgt,item FROM NIFItem FOR XML AUTO"

myStream.Open
myCommand.Properties("Output Stream") = myStream
myCommand.Properties("xml root") = "root"

myCommand.Execute ,,1024 'instead of adExecuteStream
myStream.Position=0
myStream.Charset="ISO-8859-1"
Dim strxml
strxml = myStream.ReadText
MsgBox (strxml)

If you don't need a stream and instead want to read the values of itemsgt and item, try this instead:

Dim myStream, myConnection, myCommand, adoRec
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
myConnection.Open "Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=DSIPAR;Data Source=.\DSIDATA"
myCommand.ActiveConnection=myConnection
myCommand.CommandText="SELECT itemsgt,item FROM NIFItem"
SET adoRec = myCommand.Execute()
'Get the first results
If Not adoRec.EOF then
  MsgBox "itemsgt = "  & adoRec(0) & vbcrlf & "item="  &  adoRec(1)
End If

'Iterate through the results
While Not adoRec.EOF
  itemsgt = adoRec(0)
  item = adoRec(1)
  'MsgBox "itemsgt = " & itemsgt & vbcrlf & "item="  & item
  adoRec.MoveNext
Wend