pythonpandaspyodbcdatabase-connectivity

Passing data to a stored procedure that accepts Table Valued Parameter using pyodbc

Trying to send data to a stored procedure that accepts Table Valued Parameter. Getting following error:

[Error] ('HY004', '[HY004] [Microsoft][ODBC SQL Server Driver]Invalid SQL data type (0) (SQLBindParameter)')

I know it is due to datatype mismatch – but how to correct this?

When I used SQL Server profiler, I see following

exec sp_sproc_columns N'[MyTestTvp]',N'dbo',@ODBCVer=3

Python Code

import pandas as pd
import pyodbc 
from pandas.plotting import scatter_matrix
import matplotlib.pyplot as plt

def main():


  cnxn = pyodbc.connect("Driver={SQL Server};Server=dataserver;UID=UserName;PWD=Password@123;Database=MySQLServerDatabase;")
  dfInput = pd.read_sql_query('exec dbo.usp_Temp_GetAllPatientBKs_ToEncrypt ?', cnxn, params=['None'] )


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

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

  try:
    cursor = cnxn.cursor()
    result_array = cursor.execute("EXEC dbo.[MyTestTvp] ?", [param_array]).fetchall()
    cursor.commit()  #very important to commit
  except Exception as ex:
    print("Failed to execute MyTestTvp")
    print("Exception: [" + type(ex).__name__ + "]", ex.args)

if __name__== "__main__":
  main()

TVP in SQL Server

CREATE TYPE dbo.[MyList] AS TABLE
(
    [Id] INT NOT NULL
);

-- create stored procedure
CREATE PROCEDURE dbo.[MyTestTvp]
(
    @tvp dbo.[MyList] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM @tvp

END

UPDATE

Thanks a lot to Gord Thompson. Based on the answer posted by Gord Thompson, I changed the connection 

cnxn = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};Server=dataserver.sandbox.rcoanalytics.com;UID=SimpleTest;PWD=SimpleTest@123;Database=RCO_DW;")

Then I got following error:

Data source name not found and no default driver specified

Referred pyodbc + MySQL + Windows: Data source name not found and no default driver specified

Then Installed Driver={ODBC Driver 13 for SQL Server} on the server in ODBC Data Source Administrator in the System DSN tab

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources

REFERENCES

  1. Step 3: Proof of concept connecting to SQL using pyodbc
  2. Step 1: Configure development environment for pyodbc Python development
  3. Step 2: Create a SQL database for pyodbc Python development
  4. Python on Azure
Solution

  • I was able to reproduce your issue. You are using the very old "SQL Server" ODBC driver which was written for SQL Server 2000. TVPs were introduced in SQL Server 2008.

    So, you are getting the error because the driver you are using does not understand TVPs as they did not exist at the time the driver was created.You will need to use a more modern version of the driver, e.g., "ODBC Driver 17 for SQL Server".