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

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

.NET Framework Version

.NET 8.0.1

dataplat / dbatools