0

I am currently trying to use pyodbc to select data from a table within Database A and insert it into a table within Database B. I was able to establish connections with both databases, so I know there is no error there. Additionally, my first cursor.execute command (line #9) works as I was able to print all the data.

The issue I am running into is when I try and insert the data from the first cursor.execute command into Database B. There are a few questions on SO regarding this same error, however I have checked to ensure I am not committing on of those errors. All the data types are accepted within SQL Server, I have the correct number of parameters and parameter markers, and I have ensured that the columns within my Python code match both the input and output tables. I am completely stuck and would greatly appreciate any help.

The specific error I am getting is:

('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

Please see my code below:

import pyodbc
import time

cnxn1 = pyodbc.connect(r"DRIVER={SQL Server Native Client 11.0};SERVER='Server';" + \
                        "DATABASE='DatabaseA';Trusted_Connection=Yes")
cursor1 = cnxn1.cursor()

cnxn2 = pyodbc.connect(r"DRIVER={SQL Server};SERVER='Server'," + \
                        "user='Username', password='Password', database='DatabaseB'")
cursor2 = cnxn2.cursor()

SQL =  cursor1.execute("select * from table.DatabaseA")
SQL2 = """insert into table.DatabaseB([col1], [col2], [col3], [col4],[col5], [col6], [col7],
                               [col8], [col9], [col10], [col11], [col12], [col13], [col14],
                               [col15], [col16],[col17], [col18], [col19], [col20], [col21], 
                               [col22], [col23], [col24], [col25], [col26], [col27], [col28],
                               [col29], [col30], [col31]) 
          values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"""

for row in cursor1.fetchall():  
    cursor2.execute(SQL2,row)

In regard to the last two lines of code, I have also tried the following with no success:

for row in SQL:
   cursor2.execute(SQL2,row)
Parfait
  • 104,375
  • 17
  • 94
  • 125
ls101
  • 177
  • 5
  • 17
  • This error does not have to do with syntax or running across both cursors. Check carefully again, data types align and parameter placeholders match numbers. Here you simplistically number cols but most likely not in actual. Try even interpolating values out one of the insert rows and run in SQL Server console. Without reproducibility, not much we can do. – Parfait Jan 19 '17 at 03:45
  • Is there a particular reason why you are using `DRIVER={SQL Server Native Client 11.0}` for the source connection and `DRIVER={SQL Server}` for the destination? One possibility is that the (really old) `{SQL Server}` driver does not support one of the parameter values in the row that is being returned by the newer `{SQL Server Native Client 11.0}` driver. – Gord Thompson Jan 19 '17 at 16:38

0 Answers0