pyodbc: Queries with Table Value Parameters (TVP) when the Type table is not in the dbo schema

Environment

Python: 3.7.4
pyodbc: 4.0.26
OS: macOS Mojave 10.14.5
DB: Azure SQL 2016
driver: ODBC Driver 17 for SQL Server

Issue

I’m glad to see TVPs are supported now. I was going through some testing against an Azure SQL database and it seems that the TVP type needs to be created on the dbo schema in order for this to work. If the TVP type is in a schema different than dbo, I get the error [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type ListInt

Database setup

-- create type
CREATE TYPE [myschema].[ListInt] AS TABLE
(
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

-- create stored procedure
CREATE PROCEDURE [myschema].[PyOdbcTestTvp](@tvp [myschema].ListInt READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

My python code

Following pyodbc sqlservertests.py

c01 = [1, 2, 3]
param_array = []

for i in range(3):
    param_array.append([c01[i]])

try:
    result_array = self.cursor.execute("EXEC [myschema].[PyOdbcTestTvp] ?", [param_array]).fetchall()
except Exception as ex:
    print("Failed to execute PyOdbcTestTvp")
    print("Exception: [" + type(ex).__name__ + "]", ex.args)

The example works as expected if the TVP type is created in the dbo schema like this:

-- create type
CREATE TYPE [ListInt] AS TABLE
(
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

-- create stored procedure
CREATE PROCEDURE [myschema].[PyOdbcTestTvp](@tvp ListInt READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

Is there anything I can do to make this work with the TVP type in a schema other than dbo? Thanks!

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 4
Comments: 20 (4 by maintainers)

Most upvoted comments

Thanks @v-chojas . #904 does seem to do the trick.

So the fix is to use string values to explicitly specify the name of the type and its schema as the first two elements of the TVP “value”. In the example above:

table_values = [(1,), (2,)]  # list of tuples

sql = "EXEC [myschema].[PyOdbcTestTvp] ?"
tvp_payload = [["ListInt", "myschema", ] + table_values]
print(tvp_payload)
# [['ListInt', 'myschema', (1,), (2,)]]  
# i.e., a single-element list containing a list of the form: [str, str, tuple, tuple, tuple, …]
result = crsr.execute(sql, tvp_payload).fetchall()
print(result)
# [(1, ), (2, )]

TODO: Once #904 gets merged and released we should probably add an entry in the wiki.

gordthompson on Jul 24, 2021

I am also interested in this issue. My employer deals with a lot of databases with many different schemas. To work around this issue I’ve been using global temp tables, as a workaround. This bug fix would be a great benefit.

juls858 on Dec 28, 2020

@v-chojas , @v-makouz – Any feedback from my comments of 2019-07-25 and 2019-08-06? It really does look like the ODBC driver is the one responsible for

directly constructing, or
providing the server instance with all of the information required to construct

the call

exec sp_prepare @p1 output,N'@P1 [ListInt] READONLY',N'EXEC [myschema].[PyOdbcTestTvp] @P1',1

and the output from

exec sp_describe_undeclared_parameters N'EXEC [myschema].[PyOdbcTestTvp] @P1'

does tell the driver that the (“suggested”) schema name is myschema. I don’t really see how pyodbc can work around the issue because the “conversation” between pyodbc and the driver only involves the

ParameterType: SQL_SS_TABLE (-153), and the
ValueType: SQL_C_BINARY

(Note that December 25 will not only be Christmas Day, but it will also be the day that this issue turns seventeen (17) months old.)

gordthompson on Dec 16, 2020

@gordthompson Ah, OK, thanks, I got a little confused by the two similar SQLs, it all makes sense now, I can repro and looking into it

v-makouz on Feb 11, 2020

Additional information:

I’m not sure to what extent pyodbc is directly involved in creating the exec sp_prepare call, but the output from

exec sp_describe_undeclared_parameters N'EXEC [myschema].[PyOdbcTestTvp] @P1'

does include ‘myschema’ as the “suggested_user_type_schema”:

parameter_ordinal: 1
name: @P1
suggested_system_type_id: 243
suggested_system_type_name: 
suggested_max_length: 4
suggested_precision: 0
suggested_scale: 0
suggested_user_type_id: 259
suggested_user_type_database: myDb
suggested_user_type_schema: myschema
suggested_user_type_name: ListInt
suggested_assembly_qualified_type_name: 
suggested_xml_collection_id: 
suggested_xml_collection_database: 
suggested_xml_collection_schema: 
suggested_xml_collection_name: 
suggested_is_xml_document: 0
suggested_is_case_sensitive: 0
suggested_is_fixed_length_clr_type: 0
suggested_is_input: 1
suggested_is_output: 0
formal_parameter_name: @tvp
suggested_tds_type_id: 243
suggested_tds_length: 4

gordthompson on Jul 25, 2019

I am able to reproduce the issue. pyodbc seems to be going through the usual motions, successfully calling SQLPrepareW, SQLDescribeParam, SQLBindParameter, and so on, but when it finally gets to SQLExecute it fails with

main            1b14-1e38	EXIT  SQLExecute  with return code -1 (SQL_ERROR)
        HSTMT               0x0045AA10

        DIAG [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type ListInt. (2715) 

        DIAG [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180) 

        DIAG [01000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parameter or variable '@P1' has an invalid data type. (2724)

SQL Profiler shows that we’re calling …

exec sp_describe_undeclared_parameters N'EXEC [myschema].[PyOdbcTestTvp] @P1'

… followed by …

exec sp_prepare @p1 output,N'@P1 [ListInt] READONLY',N'EXEC [myschema].[PyOdbcTestTvp] @P1',1

… and that’s failing, probably because the [ListInt] reference is not qualified so it must be looking in the current default schema, which is dbo for my login.

gordthompson on Jul 25, 2019