Modify a value returned from Invoke-sqlcmd

Question

I've searched for a number of hours now and am unable to figure out how to do this.

I query an MSSQL database that returns 2 columns, one of these values is empty/null but does represent something in the SQL database(I've tested disabling it).

How would I check through what is returned from my query for the empty value and modify this to something else?

$TestQuery = Invoke-Sqlcmd -Database $DB -Query $qcd -ServerInstance "SomeInstance\Instance1" -Verbose

Result:

Activity              Setting
--------              -------
All Operation         Enabled
Backup                Enabled
Restore               Enabled
Prune                 Enabled
Aux Copy              Enabled
Schedule              Enabled
Archive Check         Enabled
Tape Erase            Enabled
Offline content Index Enabled
Online Content Index  Enabled
                      Enabled

You can see the last item returned doesn't have a value but does reflect a setting in the application we use, I just want to modify that value to "Value1" for example.

Any help is greatly appreciated, I did try using hashtables but had no idea what I was doing despite several hours of googling.

Edit: My Query:

SELECT JM.opName AS 'Activity',
    CASE action
    WHEN 1 THEN 'Disabled'
    WHEN 2 THEN 'Enabled'
    END AS 'Setting'
    FROM JMJobAction AS J
    LEFT JOIN JMJobOperationNames JM on JM.opType = J.opType
WHERE clientId = 1
    AND appType = 0
    AND J.opType != 8
    AND appId = 1

AdminOfThings · Accepted Answer · 2021-05-18 12:35:55Z

1

You may do the following in PowerShell:

$TestQuery = Invoke-Sqlcmd -Database $DB -Query $qcd -ServerInstance "SomeInstance\Instance1"
$TestQuery |
    Where { [string]::IsNullOrEmpty($_.Activity) } | Foreach-Object {
        $_.Activity = 'Value1' # Update all empty or nulls with Value1
    }
$TestQuery # Contains updated results

Note that this does not update the actual database. You will need a separate query that writes back to the database.

When a database table contains a NULL, it is interpreted as the System.DBNull data type in PowerShell. [System.DBNull]::Value is not the same as $null. So if you only wanted to query for NULL values, then your query could more appropriately be modified to the following:

$TestQuery | Where Activity -is [DBNUll]

answered May 18, 2021 at 12:35

AdminOfThings

25.2k4 gold badges23 silver badges31 bronze badges

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

1 Comment

Mike Over a year ago

I did read somewhere that a DB Null is not the same as $null. But thank you so much, this has resolved the issue I was having, I couldn't figure out how to do this within the SQL query but this is the next best thing.

Carlos Garcia · Accepted Answer · 2021-05-18 14:30:37Z

1

I don't know if I understand your question correctly. I understand that you want to have a default_value when there is no data in a column.

That can be solved in your SQL Query with case. Here an example

[Edit] Based on your added query

SELECT 
    CASE 
      WHEN JM.opName is null OR JM.opName = '' THEN "DefaultActivity" 
      ELSE JM.opName
    END AS Activity,
    CASE action
       WHEN 1 THEN 'Disabled'
       WHEN 2 THEN 'Enabled'
    END AS 'Setting'
FROM JMJobAction AS J
    LEFT JOIN JMJobOperationNames JM on JM.opType = J.opType
WHERE clientId = 1
    AND appType = 0
    AND J.opType != 8
    AND appId = 1

edited May 18, 2021 at 14:30

answered May 18, 2021 at 11:39

Carlos Garcia

3,0202 gold badges24 silver badges42 bronze badges

2 Comments

Mike Over a year ago

So I'm not sure how to add this into my query, I've added my query into my first post I just keep breaking the query

Carlos Garcia Over a year ago

Hi @Mike, I have updated my answer. Unfortunately I cannot test it, I dont have such database :) but give this a try and let me know if there are errors

Collectives™ on Stack Overflow

Modify a value returned from Invoke-sqlcmd

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related