Put sql query output in variable powershell

Question

I want to put a counted value from a sql query into a variable of powershell...? If i run the following command:

sqlcmd -S "ms-sql-1264" -d "ShellPlus" -q "SET ANSI_WARNINGS OFF; SET NOCOUNT ON; SELECT COUNT(InternalMeasurementID) as number_of_running_measurements FROM [ShellPlus].[dbo].[Measurements] WHERE Archived = 0 AND ClinicalInfo NOT LIKE '%PSG%' AND Classification NOT LIKE '%TEST' AND Classification NOT LIKE '%RESEARCH%' AND Status = 2"

I get the result:

PS H:\> C:\install\powershell scripts\mail sturen lopend EEG\test_sql.ps1

number_of_running_measurements
------------------------------
                             0

but the program won't stop...?

And i want to create an if statement with the value "number of running measurements"

Anyone?

Best regards, Thijs

vonPryz · Accepted Answer · 2019-08-13 08:09:08Z

2

Sqlcmd is different from most Windows applications that it is case sensitive. That is, switches -q and -Q have different purposes.

-q "cmdline query"  
-Q "cmdline query" (and exit)

Since lower case q means to query without exit, change it to upper case q for query and exit behavior.

answered Aug 13, 2019 at 8:09

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

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

2 Comments

Thijs Over a year ago

Thanks, but how do i get my value into a variable...?

Thijs Over a year ago

$test = sqlcmd -S "ms-sql-1264" -d "ShellPlus" -Q "SET ANSI_WARNINGS OFF; SET NOCOUNT ON; SELECT COUNT(InternalMeasurementID) as number_of_running_measurements FROM [ShellPlus].[dbo].[Measurements] WHERE Archived = 0 AND ClinicalInfo NOT LIKE '%PSG%' AND Classification NOT LIKE '%TEST' AND Classification NOT LIKE '%RESEARCH%' AND Status = 2" $test[2].Trim() This worked!!

Collectives™ on Stack Overflow

Put sql query output in variable powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related