Copy-DbaDbTableData - Error when attempting to copy tables with a column of datatype Geography

[carlei1978]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: [14:50:56][Copy-DbaDbTableData] Something went wrong | 'Microsoft.SqlServer.Types.SqlGeography' is an invalid user defined type, reason: no UDT attribute.

If I add the switch -EnableException to Copy-DbaDbTableData then the detailed error is
Exception:
Line |
97864 | throw $records[0]
| ~~~~~~~~~~~~~~~~~
| 'Microsoft.SqlServer.Types.SqlGeography' is an invalid user defined type, reason: no UDT attribute.

Steps to Reproduce

$secureuser = "myuser"
$securePassword = ConvertTo-SecureString "mypassword" -AsPlainText -Force
$securecred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $secureuser, $securePassword
$SourceDBServer = Connect-DbaInstance -SqlInstance localhost -SqlCredential $securecred -TrustServerCertificate -NonPooledConnection -EncryptConnection
$TargetDbServer = Connect-DbaInstance -SqlInstance localhost -SqlCredential $securecred -TrustServerCertificate -NonPooledConnection -EncryptConnection
$params = @{
    SqlInstance = $SourceDBServer
    Destination = $TargetDbServer
    Database = 'ExportTest'
    DestinationDatabase = 'ImportTest'
    Table = '[dbo].[GeogTest]'
    DestinationTable = '[dbo].[GeogTest]'
    KeepIdentity = $true
    KeepNulls = $true
    Truncate = $true
    BatchSize = 10000
}
Copy-DbaDbTableData @params

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

2.1.7

Other details or mentions

The following T-SQL can be used to create the source table and populated with some test data:

CREATE TABLE dbo.GeogTest(
lKey INT NOT NULL,
sGeog GEOGRAPHY NOT NULL,
CONSTRAINT [PK_GeogTest] PRIMARY KEY CLUSTERED(
lKey
)
)
GO

INSERT INTO dbo.GeogTest
VALUES(1, 0xE6100000010C4772F90FE94F414031992A1895942540),
(2, 0xE6100000010C9B030473F4102940ACC5A70018655940),
(4, 0xE6100000010CD39FFD4811D139409A99999999994B40),
(5, 0xE6100000010C03EE79FEB4811040E09EE74F1B485A40),
(6, 0xE6100000010CC976BE9F1A2F114054742497FF385A40),
(7, 0xE6100000010C325A47551384264035EF384547025440),
(8, 0xE6100000010CB35E0CE544BB12C06A32E36DA5A75A40)
GO

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

---

PSVersion 7.4.1
PSEdition Core
GitCommitId 7.4.1
OS Microsoft Windows 10.0.19045
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 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64) Nov 14 2023 18:33:19 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 22.04.3 LTS)

.NET Framework Version

.NET 8.0.1