Differentiate between sql error and empty result when using Invoke-Sqlcmd in powershell

Question

I am using ps script to make a call to db and execute a select statement. The problem is in both the cases where there is some sql error (eg. permission issue) or empty result , there is no way for me tell whether there is a failure or it's just an empty response.

$Result = Invoke-Sqlcmd -ServerInstanc $sqlServer -Database $database -Query $query

So even if there is a permission issue with the db user , the result variable is null and also in case of a empty table, the result variable is null.

How do I differentiate between the two ?

Marley Stacy · Accepted Answer · 2021-01-07 13:42:40Z

1

To check if a value is a null value, in context of a powershell instance, or in the context of a failed query and empty result, by user error or some other issue:

if ($item -eq $null){
    Write-Host "Item is null"
}

To check if a value is a special DBNull value, that is to say a value pulled from a database result of null, like your empty table example:

if ($item -eq [System.DBNull]::Value)
    Write-Host "Item is a null db value"
}

answered Jan 7, 2021 at 13:42

Marley Stacy

1001 silver badge14 bronze badges

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

Collectives™ on Stack Overflow

Differentiate between sql error and empty result when using Invoke-Sqlcmd in powershell

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related