by garga1 at 2013-03-13 08:54:05
Hello all,by cmille19 at 2013-03-13 09:44:46
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
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.by garga1 at 2013-03-13 11:04:51
[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]
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:by cmille19 at 2013-03-13 11:20:02
Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.
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.by garga1 at 2013-03-13 12:21:41
Get-WmiObject -computer MyServer Win32_NetworkAdapterConfiguration | where {$.IPAddress} | select -ExpandProperty IPAddress
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.by garga1 at 2013-03-13 12:31:45
Thanks.
One quick observation though: now the output is not as it was earlier.by garga1 at 2013-03-13 13:19:28
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.
This works nice now - thanks to the solution posted here: http://serverfault.com/questions/298769 … nsole-sizeby garga1 at 2013-03-15 12:40:15
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.
All the metrics I wanted to collect are now working with this. They were, earlier, getting split onto a new row after 79 characters.