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

[potatoqualitee]

@claude can you pls take a look and propose a solution. It's possible that I added the geography DLL from SMO since.

[claude]

Claude finished @potatoqualitee's task —— View job • claude/issue-9286-20251128-1644 • Create PR ➔

---

Geography datatype support added ✓

Completed Tasks:

• Read Copy-DbaDbTableData command file
• Locate bulk copy implementation and error location
• Search for existing spatial type handling in codebase
• Research solution for loading Microsoft.SqlServer.Types assembly
• Implement fix - Add spatial types library loading
• Commit and push changes
• Provide PR creation link

Analysis

The issue occurred because SqlBulkCopy operations on tables with Geography or Geometry columns require the Microsoft.SqlServer.Types assembly to be loaded. This assembly contains the spatial types (SqlGeography, SqlGeometry) that enable proper handling of these UDT (User Defined Type) columns during bulk copy operations.

Solution Implemented

Created a new helper function Add-SpatialLibrary.ps1 following the existing dbatools pattern (similar to Add-ReplicationLibrary and Add-PbmLibrary) that loads the Microsoft.SqlServer.Types.dll assembly from the dbatools library path.

Updated the following commands to load spatial types in their begin blocks:

1. Copy-DbaDbTableData - The command reported in the issue
2. Write-DbaDbTableData - Also uses SqlBulkCopy for bulk operations
3. Import-DbaCsv - Also uses SqlBulkCopy for CSV imports
4. Copy-DbaDbViewData - Automatically benefits since it internally calls Copy-DbaDbTableData

The function gracefully handles cases where the assembly is not available by issuing a warning instead of throwing an error, ensuring backward compatibility for environments that don't have the spatial types DLL installed.

Testing

The fix should resolve the error 'Microsoft.SqlServer.Types.SqlGeography' is an invalid user defined type, reason: no UDT attribute that occurred when copying tables with Geography or Geometry columns. Users can test with the same script provided in the issue.

---

[potatoqualitee]

@claude are spatial types so heavy they cant be loaded with the rest of smo in the https://github.com/dataplat/dbatools.library ? ew dont load RMO bc it takes a while and so few people use it.

[claude]

Claude finished @potatoqualitee's task —— View job

---

Investigating Geography datatype issue

Todo List:

• Read Copy-DbaDbTableData command file
• Locate bulk copy implementation and error location
• Search for existing spatial type handling in codebase
• Research solution for loading Microsoft.SqlServer.Types assembly
• Research spatial types library characteristics (size, load time)
• Check dbatools.library assembly loading pattern
• Analyze spatial type usage frequency in dbatools
• Provide recommendation on loading approach

Analysis and Recommendation

Current State:

• Geography and Geometry types are explicitly marked as unsupported in masking/data generation commands (New-DbaDbMaskingConfig, Invoke-DbaDbDataMasking)
• The Microsoft.SqlServer.Types assembly is not currently loaded by dbatools.library or dbatools
• Copy-DbaDbTableData fails when encountering spatial types because SqlBulkCopy needs the assembly loaded
• Test suite confirms spatial types exist: Get-DbaDbAssembly.Tests.ps1 verifies "Microsoft.SqlServer.Types" assembly in master database

Spatial Types Usage in dbatools:

• Only 4 commands interact with spatial types, and only to explicitly skip/warn about them:
  • New-DbaDbMaskingConfig (docs say Geography/Geometry not supported)
  • Invoke-DbaDbDataMasking (docs say Geography/Geometry not supported)
  • Invoke-DbaDbPiiScan (marks Geography as PII category)
  • Copy-DbaDbTableData (the command with the bug)

Key Finding: Spatial types are rarely used in dbatools. Most commands avoid them entirely.

Recommendation: Load Spatial Types in dbatools.library

I recommend loading Microsoft.SqlServer.Types with the rest of SMO in dbatools.library for these reasons:

1. Not Heavy - The Microsoft.SqlServer.Types assembly is relatively lightweight:

   • SQL Server 2019+: ~350KB for the main DLL
   • Includes native dependencies (x86/x64) but these are only loaded when spatial types are actually used
   • Much lighter than full SMO assemblies already being loaded

2. Different from RMO - Unlike replication (RMO):

   • Spatial types are a SQL Server core feature, not a specialized add-on
   • RMO is rarely used; spatial types, while uncommon, are a standard SQL datatype
   • Loading spatial types has minimal performance impact compared to RMO

3. Better User Experience:

   • Users expect Copy-DbaDbTableData to handle all SQL datatypes without special configuration
   • No need for special handling code in individual commands
   • Fixes the issue transparently without requiring command-level workarounds

4. Precedent - SQL Server itself loads these types automatically in the master database (as verified by our test suite)

5. Simplicity - Loading with SMO means:

   • No need for Add-SpatialLibrary helper function
   • No need to modify Copy-DbaDbTableData or other bulk copy commands
   • Future commands that need spatial types work automatically
   • No maintenance burden

Alternative (Not Recommended)

If spatial types truly can't be loaded in dbatools.library, the fallback would be:

• Create Add-SpatialLibrary function (similar to Add-ReplicationLibrary)
• Call it in Copy-DbaDbTableData's begin block
• Would need to be added to any other command that might encounter spatial data
• More maintenance burden and less user-friendly

Conclusion

Given that spatial types are lightweight, standard SQL datatypes, and have minimal performance impact, they should be loaded with SMO in dbatools.library rather than handled on-demand like RMO.

Would you like me to create an issue in the dbatools.library repository to request this, or would you prefer to handle that directly?

---

Branch: claude/issue-9286-20251128-1648 |

[andreasjordan]

@potatoqualitee do you have plans to update the library with the types?

[potatoqualitee]

Yes, it should be already. Though I thought it was already by 2025. We're quite recent with SMO. and soon i'll be doing a library update to be even recent-er.