Powershell Error.

Question

Powershell Error.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 10, 2016 at 3:27 pm

#400140

Hi,
When I try to run the below command, the variable ($pk_key2) comes as NULL.
Can you suggest if something is going bad?
[string] $pk_key2 = invoke-sqlcmd -query "SELECT column_name FROM information_schema.key_column_usage WHERE TABLE_NAME =" + ''''+ $table + '''' -ServerInstance $servername -Database $databasename
$pk_key2
$pk_key2 = "" + $pk_key2 + ""
$pk_key2
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

SQL-DBA-01 (5/10/2016)
Hi,
When I try to run the below command, the variable ($pk_key2) comes as NULL.
Can you suggest if something is going bad?
[string] $pk_key2 = invoke-sqlcmd -query "SELECT column_name FROM information_schema.key_column_usage WHERE TABLE_NAME =" + ''''+ $table + '''' -ServerInstance $servername -Database $databasename
$pk_key2
$pk_key2 = "" + $pk_key2 + ""
$pk_key2

invoke-sqlcmd returns a datarow - not a string.

as for null change code as follows and see which one of the variables isn't set - that will be a possible cause - the other is that the table required does not have an entry on that information schema table.

$query = "SELECT column_name FROM information_schema.key_column_usage WHERE TABLE_NAME ='" + $table.TrimEnd() + "'"

$pk_key2 = invoke-sqlcmd -query $query -ServerInstance $servername -Database $databasename

$servername

$databasename

$table

$query

$pk_key2

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thanks. Actually I am trying to get the pk column and interested to feed in in the arrow marked place to count number of records.

But the $pk_key2 is being placed as NULL, so getting error.

$rdr = $cmd.ExecuteReader()

[array] $test = @()

while($rdr.Read())

{

$test += ($rdr[$pk_key2].ToString()) <------------ Here.

}

Write-Host -ForegroundColor Green $test

write-host 'Total Number of Records:' $test.Count;

OutPut:

You cannot call a method on a null-valued expression.

At C:\folder\table_entry.ps1:375 char:5

+ $test += ($rdr[$pk_key2].ToString())

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : InvalidOperation: (:) [], RuntimeException

+ FullyQualifiedErrorId : InvokeMethodOnNull

Thanks.