Using SQL command in VBS Script

Question

I'm not very familiar with VBS but I want to use this SQL Query into VBS script. Here is my SQL query :

declare @names nvarchar(4000)
select @names = coalesce(@names+';','')+ email from myTable where sendbox=1

Here is my vbs code :

Set Recordset= CreateObject("ADODB.recordset")
ConnString="DRIVER={SQL Server};SERVER=MYSERVER\INSTANCE;Trusted_Connection = yes;DATABASE=MyBase"
Dim Query, Dest
Query = "select @names = coalesce(@names+';','')+ email from myTable where sendbox=1"
Recordset.open Query,ConnString
Dest = Recordset(0).value
Recordset.close

As you can see, I don't know where how I could declare my var @name.

score 0 · Answer 1 · answered Nov 24 '19 at 20:39

I found another way that's working for what i should do. Here is my solution if it can help :

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "DRIVER={SQL Server};SERVER=MyServer\Instance;Trusted_Connection = yes;DATABASE=MyBase"
Set objRecordset = objConnection.Execute("SELECT Email email from myTable where sendbox=1")
    i = 0
    dim Dest
    objRecordset.MoveFirst
    Do While Not objRecordset.EOF
      Dest = Dest & objRecordset(0).Value & ";"
      objRecordset.MoveNext
      i = i + 1
    Loop
objRecordset.Close
Set objRecordset = Nothing
Set objConnection = Nothing

Using SQL command in VBS Script

1 Answers1