Python Forum

Hi,
I'm trying to use Python to insert data into a SQL Server. pyodbc
I'm basically using python to export data from Server1 & Import into Server 2. with the same database / table names.
I hold the data in a dataframe called DATA
The export works & I can see the rows in my DATA dataframe.
When I try to import the data using the following code to Server 2 I receive an error

Error:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The INSERT permission was denied on the object 'dimEmployee', database 'MIS_DM_Common', schema 'conform'. (229) (SQLExecDirectW)")

My test python INSERT code

for index, row in DATA.iterrows():
     cursor.execute("INSERT INTO MIS_DM_Common.conform.DimEmployee (EmployeeConfKey,SalaryNumberWWWGDWEmployeeKey,WWWEmployeeID,WWWEmployeeNumber,Surname,FirstName,Middlename,PreferredName,EmployeeTitle,CurrentActiveIndicator,InsertEventLogKey,UpdateEventLogKey) values('1','B','C','D','E','F','G','H','I','J','1','1','1')")
connectionB.commit()
cursor.close()

I've checked that I have permission on SQL server.
in Microsoft SQL Server Manager Studio I can execute & it works fine

INSERT INTO MIS_DM_Common.conform.DimEmployee (EmployeeConfKey,SalaryNumber,WWWGDWEmployeeKey,WWWEmployeeID,WWWEmployeeNumber,Surname,FirstName,Middlename,PreferredName,EmployeeTitle,CurrentActiveIndicator,InsertEventLogKey,UpdateEventLogKey)
values('1','B','C','D','E','F','G','H','I','J','1','1','1');

(1 row affected)

Checking with shows that the data has been inserted, so I know I have write permissions.

SELECT * FROM MIS_DM_Common.conform.DimEmployee

I've also ensured that I've ran the following on SQL server, as EmployeeConfKey is a PK that I want to specify.

SET IDENTITY_INSERT MIS_DM_Common.conform.DimEmployee ON

My python connection works as I execute a COUNT query on Server2 before executing the INSERT & everything works.

I've also used this code on a 2nd project (Moving data from Server1 to Server2 (but a different table)) & it has worked successfully. My other project did not have a PK as a sequential number, so I'm assuming it has something to do with the PK.

Thanks in advance.
Steven

Do you think you have two database connections st the same time? Can you show that code?

(Feb-25-2023, 08:11 PM)deanhystad Wrote: [ -> ]Do you think you have two database connections st the same time? Can you show that code?

You were correct.
I did not close Server1 correctly & used the same code for Server2.

Thanks for the info..

Do you believe you are currently connected to two databases simultaneously? Could you display that code? planet clicker