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

eydelrivero · 2019-07-25T10:31:41Z

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!

The text was updated successfully, but these errors were encountered:

gordthompson · 2019-07-25T15:22:46Z

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 · 2019-07-25T20:07:29Z

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 · 2019-08-06T11:51:03Z

@v-makouz - Could this be an issue with ODBC Driver 17 for SQL Server? I've traced through the pyodbc code while running a query involving a TVP but I didn't see any evidence that pyodbc actually deals with the TYPE name (or schema), just that its ParameterType is SQL_SS_TABLE (-153) and its ValueType is SQL_C_BINARY.

v-chojas · 2019-08-09T19:18:09Z

I will look into this, a non-default schema is not really common and using TVPs with one may require more work on pyODBC to set the correct descriptor fields for the driver.

ghost · 2019-09-30T12:19:03Z

Did you get anywhere with this? We are experiencing the same problem.

henkelnz · 2019-11-25T19:27:04Z

"a non-default schema is not really common"

Says who? I rarely even know what my default schema is when I connect to a database because I'm calling the procedures I need, whichever schema they may be in, and it's typical to define table types in the same schema as the procedures that use them, OR in a "utility" schema if they are more broadly useful. This is a major issue for me, and I can't imagine it isn't similar for most others using TVPs. It's enough that I'm having to look into alternatives to pyodbc.

JMBoggess · 2020-01-25T18:57:20Z

I am experiencing the same issue. I am using the ODBC Driver 17 for SQL Server and receiving the same invalid data type when attempting to call a Stored Procedure that uses a Table Valued Parameter in a schema other than the default. Just wanted to add my voice in agreement to the use of non-default schemas.

v-makouz · 2020-02-11T16:39:11Z

@gordthompson I'm trying to look into this, but how do I create the custom schema? If possible can you give me the exact SQL you used to setup the repro?

I tried this:

CREATE SCHEMA myschema

-- 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

But when I run the repro script I get:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type ListInt. (2715)

gordthompson · 2020-02-11T17:42:40Z

Hi @v-makouz. That's the expected error message for this issue. This environment ...

CREATE SCHEMA myschema
GO
-- create type
CREATE TYPE [myschema].[ListInt] AS TABLE
(
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
-- create stored procedure
CREATE PROCEDURE [myschema].[PyOdbcTestTvp](@tvp [myschema].ListInt READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

... plus this Python code ...

table_values = [[1], [2]]
sql = "EXEC [myschema].[PyOdbcTestTvp] ?"
result = crsr.execute(sql, [table_values]).fetchall()

... produces that error message. On the other hand, this environment ...

-- create type
CREATE TYPE [dbo].[dboListInt] AS TABLE
(
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
-- create stored procedure
CREATE PROCEDURE [dbo].[dboPyOdbcTestTvp](@tvp [dbo].dboListInt READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

... with this Python code ...

table_values = [[1], [2]]
sql = "EXEC [dbo].[dboPyOdbcTestTvp] ?"
result = crsr.execute(sql, [table_values]).fetchall()

... works correctly, provided that your default schema is dbo.

v-makouz · 2020-02-11T18:05:00Z

@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

sandeepnmenon · 2020-03-30T10:58:31Z

Any headway on this issue?
I am getting same error

Execute sql got error:('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable # 2: Cannot find data type READONLY. (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)")

where my second parameter is a TVP with a uniqueidentifier and float value.
Why is is saying "Cannot find data type READONLY" rather than the name of my TVP type?

gordthompson · 2020-03-30T13:12:31Z

( Question from @sandeepnmenon is not relevant here. See #732 )

rk77203077 · 2020-06-29T05:31:13Z

I am facing same issue. when creating type in dbo , no issues. but when in another schema. below error.

Failed to execute function - PyOdbcTestTvp. Exception: [ProgrammingError] ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type ListInt. (2715) (SQLParamData)')

boschert · 2020-08-03T15:24:14Z

We likewise are seeing issues with using the added TVP functionality, since many of our tables do not use a default dbo schema. Any enhancements or support for this issue would be greatly appreciated!

gordthompson · 2020-12-16T15:09:33Z

@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.)

juls858 · 2020-12-28T22:40:58Z

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.

noisten-js · 2021-06-01T14:58:50Z

I seem to end up on this thread every time I have to use TVPs with Microsoft SQL Server (which is anytime I have to support SQL 2012 or SQL 2014. In SQL 2016+ we don't use TVP and favor JSON as our multi-row mechanism). I am again trying to get my proc call with a TVP to quit giving me the "Cannot find data type READONLY" message.

It has consistently worked to setup a user whose default schema is the same schema as the TVP. This time however it's not working so I've missed something in my user setup or something and continue to work through that. During this round of the fight though I came across an article from Microsoft and thought it could help here. (I've made it as far as buying a C class from Udemy so I'm no help in actually solving the issue so all I can do is share what I find, I hope it helps.

Uses of ODBC Table-Value Paramaters

Sometimes, an application uses a table-valued parameter with dynamic SQL and the type name of the table-valued parameter must be supplied. If this is the case and the table-valued parameter is not defined in the current default schema for the connection, SQL_CA_SS_TYPE_CATALOG_NAME and SQL_CA_SS_TYPE_SCHEMA_NAME must be set by using SQLSetDescField. Because table type definitions and table-valued parameters must be in the same database, SQL_CA_SS_TYPE_CATALOG_NAME must not be set if the application uses table-valued parameters. Otherwise, SQLSetDescField will report an error.

It at least appears that Microsoft intended to support ODBC and TVPs in different schemas than the user's default schema

v-chojas · 2021-06-01T20:17:23Z

Try #904

That doc needs fixed, neither of those two constants are defined and the last sentence contradicts the one before it.

gordthompson · 2021-07-24T16:55:04Z

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 · 2021-12-31T18:15:15Z

Wiki updated:

https://github.com/mkleehammer/pyodbc/wiki/Working-with-Table-Valued-Parameters-(TVPs)

eydelrivero changed the title ~~Queries with Table Value Parameters (TVP) when the Type table is in different schema~~ Queries with Table Value Parameters (TVP) when the Type table is not in the dbo schema Jul 25, 2019

This was referenced Jun 1, 2020

Invocation of stored procedure with empty TVP dumps core #772

Closed

Passing None to SQL Server INSERTs drastically slows down inserts: #741

Open

gordthompson mentioned this issue Dec 16, 2020

Failure in executing stored procedure with table valued parameter #732

Closed

gordthompson mentioned this issue Feb 6, 2021

'[HY004] [Microsoft][ODBC Driver 17 for SQL Server]Invalid SQL data type (0) (SQLBindParameter)' When first row of TVP contains None as a cell value #862

Closed

gordthompson linked a pull request Jul 24, 2021 that will close this issue

TVP non-default schema #904

Closed

gordthompson closed this as completed Dec 31, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

eydelrivero commented Jul 25, 2019

gordthompson commented Jul 25, 2019

gordthompson commented Jul 25, 2019

gordthompson commented Aug 6, 2019

v-chojas commented Aug 9, 2019

ghost commented Sep 30, 2019

henkelnz commented Nov 25, 2019

JMBoggess commented Jan 25, 2020

v-makouz commented Feb 11, 2020 •

edited

Loading

gordthompson commented Feb 11, 2020

v-makouz commented Feb 11, 2020 •

edited

Loading

sandeepnmenon commented Mar 30, 2020 •

edited

Loading

gordthompson commented Mar 30, 2020

rk77203077 commented Jun 29, 2020

boschert commented Aug 3, 2020

gordthompson commented Dec 16, 2020 •

edited

Loading

juls858 commented Dec 28, 2020

noisten-js commented Jun 1, 2021 •

edited

Loading

v-chojas commented Jun 1, 2021

gordthompson commented Jul 24, 2021

gordthompson commented Dec 31, 2021

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

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

Comments

eydelrivero commented Jul 25, 2019

Environment

Issue

Database setup

My python code

gordthompson commented Jul 25, 2019

gordthompson commented Jul 25, 2019

gordthompson commented Aug 6, 2019

v-chojas commented Aug 9, 2019

ghost commented Sep 30, 2019

henkelnz commented Nov 25, 2019

JMBoggess commented Jan 25, 2020

v-makouz commented Feb 11, 2020 • edited Loading

gordthompson commented Feb 11, 2020

v-makouz commented Feb 11, 2020 • edited Loading

sandeepnmenon commented Mar 30, 2020 • edited Loading

gordthompson commented Mar 30, 2020

rk77203077 commented Jun 29, 2020

boschert commented Aug 3, 2020

gordthompson commented Dec 16, 2020 • edited Loading

juls858 commented Dec 28, 2020

noisten-js commented Jun 1, 2021 • edited Loading

v-chojas commented Jun 1, 2021

gordthompson commented Jul 24, 2021

gordthompson commented Dec 31, 2021

v-makouz commented Feb 11, 2020 •

edited

Loading

v-makouz commented Feb 11, 2020 •

edited

Loading

sandeepnmenon commented Mar 30, 2020 •

edited

Loading

gordthompson commented Dec 16, 2020 •

edited

Loading

noisten-js commented Jun 1, 2021 •

edited

Loading