PS output in sql query window gets warpped/split

Welcome Forums General PowerShell Q&A PS output in sql query window gets warpped/split

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

  • Author
  • #6386

    Points: 0
    Rank: Member

    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?


    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 "

    IPAddress : {,,, ab80::cd81:ef82:ghf:8
    IPAddress :

    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 : {,,, ab80::cd81:ef82:ghf:8c0z}

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


    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

    SET @doc = @doc + @line
    FETCH NEXT FROM l_cursor INTO @line

    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.


    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 : {,,, ab80::cd81:ef82:ghf:8

    Now, it is like this:

    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: ... nsole-size

    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

    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.

The topic ‘PS output in sql query window gets warpped/split’ is closed to new replies.