"Invalid SQL data type" error when TVP data has None in first row

Question

I get this error when I try to start a Stored Procedure through pyodbc in an SQL Server database with a TVP and a column has a None value.

[HY004] [Microsoft][ODBC Driver 18 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)

Is there any way to indicate to pyodbc the datatype of the column or do I have to give up in TVP's?

This is what I'm trying:

cursor.execute("{CALL dbo.stored_procedure (?, ?)}", (None, tvp_data))

First variable I don't care about, second one is my TVP. Everything works fine as long as there is no None in a column in the TVP itself, but if there is, I get the error.

Answer 1

This is a known limitation of the parameter metadata discovery as discussed on GitHub here and here. Workarounds include:

Option 1. Sort your TVP data so the first row contains no None values.

Option 2. Use OPENJSON() in an anonymous code block and pass your TVP data as a JSON string.

tvp_data = [(1, None), (2, "Bravo")]
tvp_json = [dict(zip(["id", "txt"], row)) for row in tvp_data]

sql = """\
SET NOCOUNT ON;
DECLARE @tvp dbo.issue_1229_table_type;
INSERT INTO @tvp (id, txt)
SELECT id, txt FROM OPENJSON(?)
WITH (
    id int '$.id',
    txt nvarchar(50) '$.txt'
);
EXEC issue_1229_sp @tvp
"""
results = crsr.execute(sql, json.dumps(tvp_json, default=str)).fetchall()
print(results)
# [(1, None), (2, 'Bravo')]

(Example copied from my own GitHub comment here.)