PS output in sql query window gets warpped/split

by garga1 at 2013-03-13 08:54:05

Hello all,
I am a newbie at PS and trying to run a simple PS code in SQL server query window. The output is getting wrapped after 79 characters. Is it possible that this does not happen?

Example:

SET NOCOUNT ON
declare @QryCode VARCHAR(5000), @ServerName VARCHAR(100) = ‘MyServer’
select @QryCode = ‘powershell -command "Get-WmiObject -computer ‘+@ServerName+’ Win32_NetworkAdapterConfiguration | select IPAddress| Format-list | Out-string -width 4096"’
select @QryCode
exec master…xp_cmdshell @QryCode

When I run this code in a query window, I get the following output:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
powershell -command "Get-WmiObject -computer MyServer Win32_NetworkAdapterConfiguration | select IPAddress| Format-list | Out-string -width 4096 "

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IPAddress : {100.00.00.000, 100.00.00.001, 100.00.00.002, ab80::cd81:ef82:ghf:8
c0z}
NULL
IPAddress :
NULL

As you can see, the last 4 characters of the output are getting split onto the next line. If I run the actual code (also shown in the output above) in the powershell console, then the output is just fine as below:

IPAddress : {100.00.00.000, 100.00.00.001, 100.00.00.002, ab80::cd81:ef82:ghf:8c0z}

Is there a way that this can be avoided with a flag in the above command?

Thanks.
Ajay
by cmille19 at 2013-03-13 09:44:46
It’s not a Powershell things,you’re calling xp_cmdshell which is what is doing the formatting. One technique for dealing with xp_cmdshell output is to save output to a temp table and then combine the rows. Also if you’re only interested in the IPAddress then you can use the -expandproperty parameter.

[code2=sql]CREATE TABLE #output
(line varchar(255))
INSERT #output
EXEC xp_cmdshell 'powershell -Command "Get-WmiObject -computer MyServer Win32_NetworkAdapterConfiguration | select -expandproperty IPAddress "'

DELETE #output WHERE line IS NULL

DECLARE @doc varchar(max)
SET @doc = ''

DECLARE @line varchar(255)

DECLARE l_cursor CURSOR
FOR SELECT line FROM #output

OPEN l_cursor

FETCH NEXT FROM l_cursor INTO @line

WHILE @@FETCH_STATUS = 0
BEGIN
SET @doc = @doc + @line
FETCH NEXT FROM l_cursor INTO @line
END

CLOSE l_cursor
DEALLOCATE l_cursor
DROP TABLE #output

SELECT @doc[/code2]
by garga1 at 2013-03-13 11:04:51
The IPAddress is just one of the metrics where this is happening - so this was just an example. Also, the code you pasted is not working. I get this error:

Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.
by cmille19 at 2013-03-13 11:20:02
You’ll need to add a where clause to your powershell command.The ExpandProperty only works for non-Null data. The where statement will check that IPAddress exists.

Get-WmiObject -computer MyServer Win32_NetworkAdapterConfiguration | where {$.IPAddress} | select -ExpandProperty IPAddress
by garga1 at 2013-03-13 12:21:41
Thanks a bunch. This is working now. I will try with other metrics also where it was failing. My suspicion is that wherever the output is a single string, it will fail again.

Thanks.
by garga1 at 2013-03-13 12:31:45
One quick observation though: now the output is not as it was earlier.

Earlier it was:

IPAddress : {100.00.00.000, 100.00.00.001, 100.00.00.002, ab80::cd81:ef82:ghf:8
c0z}

Now, it is like this:
100.00.00.000
100.00.00.001
100.00.00.002
ab80::cd81:ef82:ghf:8c0z

I would be collecting data for many metrics at the same time, dump them into a table and then parse them in the end based upon the characters in the beginning of the line before ":". Example "IPAddress :". This messes up because it does not tell me that the data I am looking at is IP Address or something else.
by garga1 at 2013-03-13 13:19:28
This works nice now - thanks to the solution posted here: http://serverfault.com/questions/298769 … nsole-size

SET NOCOUNT ON
declare @QryCode VARCHAR(5000), @ServerName VARCHAR(100) = ‘MyServer’
select @QryCode = 'powershell -command "$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (4096, 25); Get-WmiObject -computer ‘+@ServerName+’ Win32_NetworkAdapterConfiguration | where {$
.IPAddress} | select IPAddress| Format-list | Out-string"'

select @QryCode
exec master…xp_cmdshell @QryCode
go

This gives me what I wanted. I will still test it for other metrics and post back by the end of week.

Thanks for your help.
by garga1 at 2013-03-15 12:40:15
All the metrics I wanted to collect are now working with this. They were, earlier, getting split onto a new row after 79 characters.