geometry/geography/hierarchyid types in resultset abort batch

[petervandivier]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [09:01:49][Invoke-DbaQuery] [NP:.] Failed during execution | Type 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not contain static Null property or field.
WARNING: [09:01:49][Invoke-DbaQuery] [NP:.] Failed during execution | Type 'Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not contain static Null property or field.
WARNING: [09:01:49][Invoke-DbaQuery] [NP:.] Failed during execution | Type 'Microsoft.SqlServer.Types.SqlHierarchyId, Microsoft.SqlServer.Types, Version=16.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' does not contain static Null property or field.

Steps to Reproduce

@(
    'geometry'
    'geography'
    'hierarchyid'
) | % { Invoke-DbaQuery -SqlInstance localhost -Query "select cast(null as $_)" }

Please confirm that you are running the most recent version of dbatools

[13.54s] C:\Users\Peter.Vandivier
PS> (Get-Module dbatools).Version.ToString()
1.1.133
[12.85ms] C:\Users\Peter.Vandivier
PS>

Other details or mentions

When querying a live table with a null geom/geog/hierarcyid column value, the entire resultset is discarded. Consider:

Invoke-DbaQuery -SqlInstance localhost -Query @"
create table #foo (
    id int,
    geo geography
);

insert into #foo values (1,null);
insert into #foo values (2,geography::STGeomFromText('linestring(0 0, 0 0)', 4326));

select * from #foo;
"@

Invoke-SqlCmd2 handles this use case as expected.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      7.2.6
PSEdition                      Core
GitCommitId                    7.2.6
OS                             Microsoft Windows 10.0.19044
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )

.NET Framework Version

.NET 6.0.8

[petervandivier]

Actually... digging a little deeper it seems like maybe there's a broader issue around these types generally? 🤔

Null/Not-Null does not appear to improve the error.

 Invoke-DbaQuery -SqlInstance localhost -Query "select convert(geography,geography::STGeomFromText('linestring(0 0, 0 0)',4326));"

Invoke-DbaQuery

Invoke-SqlCmd2

[niphlod]

looking at it it may be the null scrubber code that needs to be adapted

https://github.com/dataplat/dbatools/blob/development/internal/functions/Invoke-DbaAsync.ps1#L125

[petervandivier]

@niphlod that was my initial thought as well but please see the follow-up example. Given that non-null values also throw the same error I'm not sure that still makes a lot of sense 🤔

[potatoqualitee]

It may be a bug too, I think I recall an issue filed in the SMO library mayyyyyybe. And maybe one day I'll just add all SMO libraries so that I can automate updates as soon as SMO is released.

[petervandivier]

I don't see anything obvious after a very cursory glance at the SMO issues list

Is there an SMO class I could query between sessions with different modules/versions loaded to pinpoint a before/after or good/bad state? Something like [System.Data.SqlClient.SQLConnection].Assembly.Version (but that's got the same value coming out in all sessions I'm checking now both good and bad)

[potatoqualitee]

I'll be updating SMO next week. Hopefully that'll help.

[petervandivier]

appears to still be broken at 1.1.136 😞

[potatoqualitee]

alas, i have not yet had a chance to update SMO 😭

[potatoqualitee]

Does this mean it works now? 😅

>> ) | % { Invoke-DbaQuery -SqlInstance sqlcs -Query "select cast(null as $_)" }
WARNING: [16:32:46][Invoke-DbaQuery] [sqlcs] Failed during execution | DataReader.GetFieldType(0) returned null.
WARNING: [16:32:46][Invoke-DbaQuery] [sqlcs] Failed during execution | DataReader.GetFieldType(0) returned null.
WARNING: [16:32:46][Invoke-DbaQuery] [sqlcs] Failed during execution | DataReader.GetFieldType(0) returned null.
[282ms] C:/github❯
[282ms] C:/github❯ Invoke-DbaQuery -SqlInstance sqlcs -Query @"
>> create table #foo (
>>     id int,
>>     geo geography
>> );
>>
>> insert into #foo values (1,null);
>> insert into #foo values (2,geography::STGeomFromText('linestring(0 0, 0 0)', 4326));
>>
>> select * from #foo;
>> "@
WARNING: [16:33:44][Invoke-DbaQuery] [sqlcs] Failed during execution | DataReader.GetFieldType(1) returned null.

[jpomfret]

Tried to see if this was fixed in the development branch.. and I couldn't verify that - but I also couldn't get the example working with Invoke-SqlCmd2 🤔

@petervandivier - are you able to help us test this and see if we can figure out where it's broken?

[wsmelton]

This is a bug in the libraries from Microsoft and not our code.

When our code runs $da.fill($ds) you run and it spits out the error:

MethodInvocationException: Exception calling "Fill" with "1" argument(s): "DataReader.GetFieldType(1) returned null."

[wsmelton]

This would be the code block throwing the error, which indicates to me it is on MS side. We are using the new Microsoft.Data.SqlClient library in this command now because we stopped use of the old SqlClient at the direction of Microsoft.

https://github.com/dataplat/dbatools/blob/1652adcea1e6baf997f425ae22535e30355320fc/private/functions/Invoke-DbaAsync.ps1#L234-L241

[potatoqualitee]

Hello, can you please run the the following command:

Install-Module dbatools -AllowPrerelease -Scope CurrentUser

then restart powershell, and try again.

this is prerelease2 of dbatools 2.0, which contains a ton of fixes. maybe microsoft has fixed the issue since

[potatoqualitee]

wait i tried it and have issues

@(
    'geometry'
    'geography'
    'hierarchyid'
) | % { Invoke-DbaQuery -SqlInstance localhost -Query "select cast(null as $_)" }

WARNING: [19:00:50][Invoke-DbaQuery] [localhost] Failed during execution |
DataReader.GetFieldType(0) returned null.
WARNING: [19:00:50][Invoke-DbaQuery] [localhost] Failed during execution |
DataReader.GetFieldType(0) returned null.
WARNING: [19:00:50][Invoke-DbaQuery] [localhost] Failed during execution |
DataReader.GetFieldType(0) returned null.

anyone know? I w ill remove the 2.0 tag as this seems like a general issue

[potatoqualitee]

HELL YEAH~

ipmo C:\github\dbatools
[29s] C:/github❯ @(
>>     'geometry'
>>     'geography'
>>     'hierarchyid'
>> ) | % { Invoke-DbaQuery -SqlInstance localhost -Query "select cast(null as $_)" }

Column1
-------
Null
Null
NULL

PR incoming