Failure in executing stored procedure with table valued parameter

[sandeepnmenon]

trafficstars

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.6.5
• pyodbc: 4.0.28
• OS: Windows 64-bit
• DB: Microsoft SQL Server
• driver: ODBC Driver 17 for SQL Server

Issue

Executing stored proc with table valued parameter (TVP) along with other parameters throws the following error

Executing sql:execute [dbo].[ExecuteMapping] @id=?, @values=?, @threshold=? 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)")

TVP

CREATE TYPE [dbo].[MappingType] AS TABLE
(
  [t_entityid] UNIQUEIDENTIFIER NOT NULL,
  [t_threshold] FLOAT DEFAULT 0 
)
GO

Stored Procedure

PROCEDURE [dbo].[ExecuteMapping](
    @id uniqueidentifier,
    @values MappingType READONLY,
    @threshold float
)

Python code snippet

sql_connection = pyodbc.connect(connection_string)

mappingvalues = []
mappingvalues.append(["8e4d7360-9a46-ea11-a812-000d3a54419d",0.6])
mappingvalues.append(["ed0ef314-9a46-ea11-a812-000d3a8d88aa",0.6])

params = ("1a1d243c-a587-4d10-995e-cbbf6cad4dce", mappingvalues, 0.5)
sqlCmd = "execute [dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?"

sqlCursor = sql_connection.cursor()
result = sqlCursor.execute(sqlCmd, params)

Executing this piece of code throws the above mentioned error

In the example given here, the TVP is the only parameter. How should I pass the parameteres when there are non TVP parameters along with TVP parameters for the stored procedure.

[gordthompson]

I am unable to reproduce your issue using pyodbc 4.0.30.

# import, connect, etc. ...

# -- The stored procedure definition is:
#
# CREATE PROCEDURE [dbo].[ExecuteMapping](
#     @id uniqueidentifier,
#     @values MappingType READONLY,
#     @threshold float
# )
# AS
# BEGIN
# 	SET NOCOUNT ON;
# 	SELECT @id AS thing;
# END

mappingvalues = []
mappingvalues.append(["8e4d7360-9a46-ea11-a812-000d3a54419d",0.6])
mappingvalues.append(["ed0ef314-9a46-ea11-a812-000d3a8d88aa",0.6])

params = ("1a1d243c-a587-4d10-995e-cbbf6cad4dce", mappingvalues, 0.5)
sqlCmd = "execute [dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?"

sqlCursor = cnxn.cursor()
result = sqlCursor.execute(sqlCmd, params)
print(result.fetchall())  # [('1A1D243C-A587-4D10-995E-CBBF6CAD4DCE', )]

[sandeepnmenon]

@gordthompson My pyodbc version was 4.0.28 I am ran an update command using conda update pyodbc now when I run pyodbc.version I see '4.0.0-unsupported' Then I removed the pyodbc using conda remove pyodbc

Used pip to install pip install pyodbc now pyodbc.version gives me 4.0.30

Still I am able to reproduce the error

[v-makouz]

I can't reproduce this either, do you know what the server version is?

[justinforlenza]

I am having the same issue on pyodbc 4.0.30, and I'm running SQL Server 2017.

[sandeepnmenon]

@v-makouz I am running on SQL Server 12.0.2000.8

[hjb417]

I was able to replicate this. I get the failure when the the current database differs from where the stored procedure is defined... even you specify the fully qualified name!

E.x.: Assume the procedure mentioned above, [dbo].[ExecuteMapping], is in the database opera

If the connection is currently on the master database, the following code will fail cursor.execute("use master").nextset() cursor.execute("execute [opera].[dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?", params)

If you change the database to the location of the stored procedure before executing, it will work. e.x.: cursor.execute("use opera").nextset() cursor.execute("execute [opera].[dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?", params)

[gordthompson]

Okay, so it looks like this might be related to #595

[gordthompson]

I get the failure when the the current database differs from where the stored procedure is defined... even you specify the fully qualified name!

If the stored procedure requires a user-defined Table type from the other database then that simply won't work as explained on Stack Overflow here:

Passing Table Valued parameter to stored procedure across different databases

If the stored procedure requires a user-defined Table type from a different schema in the current database then that issue was solved by #904 and released in pyodbc 4.0.32 as described in the wiki here.