cursor.bulkcopy() cannot load custom (non-spatial) CLR UDT columns #667

Describe the bug

cursor.bulkcopy() cannot load a column whose type is a custom, assembly-registered CLR UDT (any UDT other than the built-in geography/geometry/hierarchyid). It raises a server-side syntax error instead of inserting the rows.

The cause is that bulkcopy() derives each column's type from the destination via SQLDescribeCol, gets SQL_SS_UDT (-151) for a custom UDT, and the native write_to_server_zerocopy then emits an INSERT BULK whose column list has an empty/invalid type token for that column (e.g. INSERT BULK dbo.udt_bcp_test (id int, v ) WITH (...)). There is no per-column type override to work around it (column_mappings maps names only).

A CLR UDT's wire form is varbinary(max) (its IBinarySerialize payload). bulkcopy() into a varbinary(max) column works fine, and other clients (pyodbc, python-tds) load UDT columns today by declaring the bulk column as varbinary(max) and streaming the serialized bytes (the server materializes the UDT on insert). bulkcopy() offers no way to do that.

Exception message:
RuntimeError: Sql Error: 102: Class 15: State 1: Incorrect syntax near ')'. on <server> in  at line 1
Sql Error: 319: Class 15: State 1: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. on <server> in  at line 1

Stack trace:
Traceback (most recent call last):
  File "repro.py", line 21, in <module>
    cur.bulkcopy("dbo.udt_bcp_test", rows, table_lock=True)
  File ".../site-packages/mssql_python/cursor.py", line 3073, in bulkcopy
    raise type(e)(str(e)) from None
RuntimeError: Sql Error: 102: Class 15: State 1: Incorrect syntax near ')'. on <server> in  at line 1
Sql Error: 319: Class 15: State 1: Incorrect syntax near the keyword 'with'. ...

Both server errors ("near ')'" and "near the keyword 'with'") are consistent with a generated INSERT BULK (... v ) WITH (...) — an empty type before the ), then the WITH clause.

To reproduce

One-time prerequisite — a minimal custom CLR UDT (the bug needs a non-spatial assembly UDT; built-in spatial types do not reproduce it):

SimpleBlob.cs:

using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = false, MaxByteSize = 8000)]
public struct SimpleBlob : INullable, IBinarySerialize
{
    private bool _null;
    private byte[] _data;
    public bool IsNull => _null;
    public static SimpleBlob Null => new SimpleBlob { _null = true };
    public override string ToString() => _data == null ? "" : Convert.ToBase64String(_data);
    public static SimpleBlob Parse(SqlString s)
    {
        if (s.IsNull) return Null;
        return new SimpleBlob { _data = Convert.FromBase64String(s.Value) };
    }
    public void Read(BinaryReader r) { int n = r.ReadInt32(); _data = r.ReadBytes(n); }
    public void Write(BinaryWriter w) { w.Write(_data == null ? 0 : _data.Length); if (_data != null) w.Write(_data); }
}

Build and register it:

EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
-- csc /target:library SimpleBlob.cs   (references Microsoft.SqlServer.Server)
CREATE ASSEMBLY SimpleBlobAsm FROM 'C:\temp\SimpleBlob.dll' WITH PERMISSION_SET = SAFE;
CREATE TYPE dbo.SimpleBlob EXTERNAL NAME SimpleBlobAsm.[SimpleBlob];

Repro (repro.py):

import struct
import mssql_python

CONN = "Server=<server>;Database=<db>;Trusted_Connection=yes;TrustServerCertificate=yes"

conn = mssql_python.connect(CONN)
cur = conn.cursor()

cur.execute(
    "IF OBJECT_ID('dbo.udt_bcp_test','U') IS NOT NULL DROP TABLE dbo.udt_bcp_test;"
    "CREATE TABLE dbo.udt_bcp_test (id int NOT NULL, v dbo.SimpleBlob NULL)"
)
conn.commit()

data = b"\x01\x02\x03\x04"
payload = struct.pack("<i", len(data)) + data      # SimpleBlob's IBinarySerialize bytes
rows = [(1, payload), (2, payload)]

cur.bulkcopy("dbo.udt_bcp_test", rows, table_lock=True)   # <-- raises (see exception above)

Control that isolates the type (not the bytes, not table_lock): change the column to v varbinary(max) and run the identical bulkcopy — it succeeds, and SELECT CAST(v AS dbo.SimpleBlob) FROM dbo.udt_bcp_test round-trips the bytes. The failure happens only when the destination column is the UDT, in both table_lock=True and table_lock=False.

Expected behavior

bulkcopy() should load custom CLR UDT columns by sending the supplied bytes as varbinary(max) (the UDT's serialized form), letting SQL Server materialize the UDT on insert — the same way spatial UDTs already round-trip and the way pyodbc/python-tds handle it. At minimum it should raise a clear "unsupported/undescribable column type" error rather than a raw T-SQL syntax error.

Further technical details

Python version: 3.11
mssql-python version: 1.10.0
SQL Server version: SQL Server 2019
Operating system: macOS 14 (also reproduced on Ubuntu 24.04)

Additional context

Diagnosis (debug logs + cursor.py/binding inspection):

• Debug log of the failing run:

  bulkcopy: Retrieving destination metadata for type coercion
  [DDBC] SQLDescribeCol: Getting column descriptions ...
  bulkcopy: Auto-generated 2 column mappings
  bulkcopy: Calling write_to_server_zerocopy
  bulkcopy: write_to_server_zerocopy failed: Sql Error: 102 ... Incorrect syntax near ')'.
  

• SQLDescribeCol returns SQL_SS_UDT (-151); constants.py only maps -151 for geography/geometry/hierarchyid, so a custom UDT falls through and write_to_server_zerocopy emits no valid type token for the column.
• No Python-level override exists: cursor.bulkcopy's only column parameter is column_mappings (names only), and the native binding confirms it — PyCoreCursor.bulkcopy.__text_signature__:

  (table_name, data_source, batch_size=0, timeout=30, column_mappings=None,
   keep_identity=False, check_constraints=False, table_lock=False, keep_nulls=False,
   fire_triggers=False, use_internal_transaction=False, python_logger=None)
  

What works, for contrast: bulkcopy() into varbinary(max)/int/char/tinyint columns; Arrow fetch (cursor.arrow()) of a UDT column via the LOB fallback; Kerberos/Trusted_Connection=yes connect.