-
Notifications
You must be signed in to change notification settings - Fork 559
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Failure in executing stored procedure with table valued parameter #732
Comments
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', )] |
@gordthompson Used pip to install Still I am able to reproduce the error |
I can't reproduce this either, do you know what the server version is? |
I am having the same issue on pyodbc 4.0.30, and I'm running SQL Server 2017. |
@v-makouz I am running on SQL Server 12.0.2000.8 |
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.: If the connection is currently on the master database, the following code will fail If you change the database to the location of the stored procedure before executing, it will work. |
Okay, so it looks like this might be related to #595 |
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. |
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:
Issue
Executing stored proc with table valued parameter (TVP) along with other parameters throws the following error
TVP
Stored Procedure
Python code snippet
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.
The text was updated successfully, but these errors were encountered: