0
Dim testvar

testvar = ""sqlcmd.exe -S SERVERNAME\INSTANCENAME -U USERNAME -P PASSWORD -r1 -W -Q "select * from Event.dbo.ALL_EVENTS where (POLICY_TYPE!='LOGFILE' and state='OPEN' and Category like 'Windows-High' and not (POLICY_NAME like 'Level 2 and 3 alerts%') and not (TITLE like '%Level 2 :%') and not (TITLE like '%Level 3 :%') and (severity = 'MAJOR' or severity = 'MINOR' or severity = 'CRITICAL'))"" wscript.echo Command

Sahil Gupta
  • 3
  • 6
  • Couple of issues .. 1) VBS won't allow you to declare and assign at once. You need to break it into 2 steps 2) sqlcmd won't run on its own - you may look into [`.Exec`](https://ss64.com/vb/exec.html) – Pankaj Jaju Mar 13 '18 at 14:36
  • Thanks for reply @pankaj... Editted command as suggested... Still not working... "Script completed with error code 1" – Sahil Gupta Mar 13 '18 at 15:27
  • You are assigning a string to testvar and not the output of the command. As I mentioned in my earlier comment, use sheel.exec to actually execute the command and read whatever the output is from the console window (I am assuming sqlcmd will output to a console) – Pankaj Jaju Mar 13 '18 at 15:32
  • Ohh my bad... Sure I will try it and will let you know the result – Sahil Gupta Mar 13 '18 at 15:36

2 Answers2

0

Hey Sahil have you tried returning the result using functions

Reference: How to save the result of a SQL query into a variable in VBA?

Naveen Koti
  • 96
  • 1
  • 6
0

This is how I did it. Thanks everyone for help.

Dim printRes, queryErr Set cmdExec = CreateObject("WScript.Shell")

Set messages = cmdExec.Exec("sqlcmd -S ******** -U **** -P ***** -i C:\file\fullpath\mssql.sql")

Set queryRes = messages.StdOut

'Set queryErr = messages.StdErr 

printRes = queryRes.ReadAll 'printerr = queryErr.ReadAll

WScript.Echo "Result is " & printRes 'WScript.Echo "Error is " & printErr

Sahil Gupta
  • 3
  • 6