PS output in sql query window gets warpped/split

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 8 months ago.

  • Author
    Posts
  • #6386

    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.

You must be logged in to reply to this topic.