need to assign sqlcmd output to a variable

Question

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

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 — Pankaj Jaju
– Pankaj Jaju, Commented Mar 13, 2018 at 14:36
Thanks for reply @pankaj... Editted command as suggested... Still not working... "Script completed with error code 1" — Sahil Gupta
– Sahil Gupta, Commented Mar 13, 2018 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
– Pankaj Jaju, Commented Mar 13, 2018 at 15:32
Ohh my bad... Sure I will try it and will let you know the result — Sahil Gupta
– Sahil Gupta, Commented Mar 13, 2018 at 15:36

Naveen Koti · Accepted Answer · 2018-03-13 15:34:30Z

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?

answered Mar 13, 2018 at 15:34

Naveen Koti

961 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sahil Gupta Over a year ago

Sure koti I will try it too

Sahil Gupta · Accepted Answer · 2018-03-14 09:46:14Z

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

answered Mar 14, 2018 at 9:46

Sahil Gupta

36 bronze badges

Collectives™ on Stack Overflow

need to assign sqlcmd output to a variable

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related