CODEWITHSUNDEEP
sqlpowershell
Goca
Goca

Reputation: 1893

Unable to cast object of type 'System.DBNull' to type 'System.Type'

I have a PowerShell script trying to run a sql command using Invoke-Sqlcmd but I get the following exception:

Invoke-Sqlcmd : The pipeline has been stopped.
At D:\Scripts\powersehelscript.ps1:57 char:9
+ $rows = Invoke-Sqlcmd -Query "SELECT * from TableName where location is  null;"  ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptComman
 Invoke-Sqlcmd : Unable to cast object of type 'System.DBNull' to type
'System.Type'.
At D:\Scripts\MyScript.ps1:57 char:9
+ $rows = Invoke-Sqlcmd -Query "SELECT * from TableName where location is null;"  ...

my sql command looks like this:

$rows = Invoke-Sqlcmd -Query "SELECT * from TableName where location is null;" -Database $Database -ServerInstance $SqlServer -Username $U -Password $P

It seems to be connecting fine to the Database and if I run the same query directly in the DB i get the results back.

From the exception I see "Unable to cast object of type 'System.DBNull' to type" but I have no clue why this casting issue is occurring

Upvotes: 1

Views: 1443

Answers (1)

Mark Smith Marcus T
Mark Smith Marcus T

Reputation: 78

In case anyone reading this is looking for other conclusions, I had exact same issue of PowerShell same error and it grinding down to a single geo column. I was simply writing some code to grab the table and export once a week for easy user reference. The Add-Type solution didn't change my outcome. What worked for me was, in the SQL cmd, using CAST(mygeocol AS varchar(100)). Conveniently, in SQL & PS, that brought it back to a format like "POINT (-90.1234 30.1234)" and worked great. Hope it helps someone!

Upvotes: 2

Related Questions