SQLCMD PowerShell needs to return only value (without header)

Question

Running the following sqlcmd in PowerShell:

$unassignedCnt = sqlcmd -W -k 1 -S MSSQLSERVER -E -Q "SET NoCount ON; SELECT   Count(DISTINCT SP.SubnetKey) AS 'UnassignedCount' FROM [Control].[dbo].[Subnet] AS SP Left OUTER JOIN [Control].[rw_current].[QIP_Subnet] AS Q ON SP.KeyHash = Q.__key_Hash; SET NoCount OFF; "
Write-Host $unassignedCnt

This returns the following formatted result:

UnassignedCount --------------- 1

I would like to have only:

Can I achieve this through the SQL command or do I need to post process the result? If so what's the simplest approach?

Write-Host $unassignedCnt -> Write-Host $unassignedCnt.UnassignedCount — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 3, 2017 at 9:25
Unlikely. What is the output of $unassignedCnt.GetType().FullName? — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 3, 2017 at 9:34
$unassignedCnt -> $unassignedCnt[0].UnassignedCount. Adjust the index if the first item is not the one you want. — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 3, 2017 at 9:36

DhruvJoshi · Accepted Answer · 2017-08-03 10:20:10Z

2

You can do this using by changing your SQL query to use PRINT instead of SELECT

SELECT will create a table which has header and data associated with it. PRINT simply outputs the results.

$unassignedCnt = sqlcmd -W -k 1 -S MSSQLSERVER -E -Q "SET NoCount ON; DECLARE @result varchar(max); SELECT  @result= Count(DISTINCT SP.SubnetKey) FROM [Control].[dbo].[Subnet] AS SP Left OUTER JOIN [Control].[rw_current].[QIP_Subnet] AS Q ON SP.KeyHash = Q.__key_Hash; PRINT @result ;SET NoCount OFF; "
Write-Host $unassignedCnt

answered Aug 3, 2017 at 10:20

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

Collectives™ on Stack Overflow

SQLCMD PowerShell needs to return only value (without header)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related