cursor.bulkcopy() fails when inserting an empty string into an NVARCHAR(MAX) column

[IonianIronist]

Describe the bug

cursor.bulkcopy() fails when any row contains an empty string ("") destined for an NVARCHAR(MAX) column. The failure surfaces as the (misleading) error:

Exception message: 

RuntimeError: Sql Error: 40197: Class 20: State 1: The service has encountered an error processing your request. Please try again. Error code 4804. on server-name in  at line 1

Stack trace:

Traceback (most recent call last):
  File "path_to_project\src\bug_repro.py", line 34, in <module>
    cur.bulkcopy(FQN, data)
    ~~~~~~~~~~~~^^^^^^^^^^^
  File "path_to_project\venv\Lib\site-packages\mssql_python\cursor.py", line 2951, in bulkcopy
    raise type(e)(str(e)) from None

The same data succeeds when:

• the empty string is replaced with None, or
• the empty string is replaced with any non-empty string (even a single space " "), or
• the column type is NVARCHAR(n) for any concrete n instead of NVARCHAR(MAX).

To reproduce

import mssql_python

server = ''
database = ''
username = ''
password = ''

CONN_STR = (
    f"Server={server};Database={database};UID={username};PWD={password};"
    "TrustServerCertificate=Yes"
)

FQN = "schema_name.table_name"

DDL = f"""
IF OBJECT_ID('{FQN}', 'U') IS NOT NULL DROP TABLE {FQN};
CREATE TABLE {FQN} (
    id   INT,
    body NVARCHAR(MAX) NULL
);
"""

with mssql_python.connect(CONN_STR) as conn:
    cur = conn.cursor()
    cur.execute(DDL)
    conn.commit()

    data = [
        (1, "hello"),
        (2, ""),  # <-- triggers the failure
        (3, "world"),
    ]

    cur.bulkcopy(FQN, data)

Expected behavior

Bulk copy is expected to insert the provided data, as an empty string should be considered valid input.

Further technical details

Python version: 3.14
SQL Server version: Microsoft SQL Azure (RTM) - 12.0.2000.8
Operating system: Windows 11

Additional context

If executed on a target table that has a not null constraint on one of the other columns, a different error appears.

RuntimeError: Sql Error: 515: Class 16: State 2: Cannot insert the value NULL into column 'col2', table 'db-name.dbo.EmptyStringBcpRepro'; column does not allow nulls. INSERT fails. on server-name in  at line 1

To reproduce

import mssql_python

server = ''
database = ''
username = ''
password = ''

CONN_STR = (
    f"Server={server};Database={database};UID={username};PWD={password};"
    "TrustServerCertificate=Yes"
)

FQN = "schema_name.table_name"

DDL = f"""
IF OBJECT_ID('{FQN}', 'U') IS NOT NULL DROP TABLE {FQN};
CREATE TABLE {FQN} (
    id   INT,
    body NVARCHAR(MAX) NULL,
    col2 bit NOT NULL
);
"""

with mssql_python.connect(CONN_STR) as conn:
    cur = conn.cursor()
    cur.execute(DDL)
    conn.commit()

    data = [
        (1, "hello", True),
        (2, "", True),  # <-- triggers the failure
        (3, "world", False),
    ]

    cur.bulkcopy(FQN, data)

Note

The issue seems to persist also on VARCHAR columns.

[github-actions]

Hi @IonianIronist, thank you for opening this issue!

Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

If you have additional information to share, please feel free to update the issue.

Thank you for your patience!

[saurabh500]

This is happening in mssql-py-core.