Working with Table Valued Parameters (TVPs) - mkleehammer/pyodbc GitHub Wiki

Consider a database with a user named "nigel" whose default schema is also named "nigel". He created a User-Defined Table Type named line_item_tbl

CREATE TYPE nigel.line_item_tbl AS TABLE 
(
    item_no int NOT NULL, 
    product_id int NOT NULL, 
    PRIMARY KEY (item_no)
)

and a Stored Procedure that takes an invoice_no (type int) and a line_items table (type line_item_tbl) and returns a result set with the invoice_no added to each row of line_items. For example, if invoice_no is 123 and line_items is

item_no  product_id
-------  ----------
      1        1001
      2        1002

then the stored procedure returns

invoice_no  item_no  product_id
----------  -------  ----------
       123        1        1001
       123        2        1002

CREATE PROCEDURE nigel.add_invoice_no_to_line_items 
    @invoice_no int, 
    @line_items nigel.line_item_tbl READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @invoice_no AS invoice_no, item_no, product_id FROM @line_items;
END

When he runs the following code it works fine.

import pyodbc

uid = "nigel"
pwd = "GoesTo11!"
connection_string = (
    r"DRIVER=ODBC Driver 17 for SQL Server;"
    r"SERVER=(local)\SQLEXPRESS;"
    r"DATABASE=mydb;"
    f"UID={uid};PWD={pwd};"
)
cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()

print(crsr.execute("SELECT SCHEMA_NAME()").fetchval())  # default schema
# nigel

invoice_no = 123
line_items = [(1, 1001), (2, 1002)]

sql = "EXEC nigel.add_invoice_no_to_line_items @invoice_no=?, @line_items=?"
params = (invoice_no, line_items)
print(crsr.execute(sql, params).fetchall())
# [(123, 1, 1001), (123, 2, 1002)]

Scott is an administrator whose default schema is "dbo". When he tries running the same code with

uid = "scott"
pwd = "tiger^5HHH"

he gets the following error when trying to .execute() the stored procedure:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #2: Cannot find data type line_item_tbl. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parameter or variable '@P2' has an invalid data type. (2724)")

This was reported in issue #595 and fixed in pyodbc 4.0.32.

To avoid this problem we need to prepend the type's name and schema to the beginning of the list that holds the TVP data rows (tuples). That is, instead of

line_items = [(1, 1001), (2, 1002)]

we need to use

line_items = ["line_item_tbl", "nigel", (1, 1001), (2, 1002)]