topic Re: Creating an OLE DB connection to a SQLServer database in Python Questions

Creating an OLE DB connection to a SQLServer database

ClaireParsons — Tue, 27 Mar 2012 14:23:44 GMT

Hi

I'm fairly new to Python, so please bear with me.
I need to create a link to a SQLServer database using OLEDB and i haven't got a clue where to start.
Can someone help please? 😞

Many thanks
Claire

Re: Creating an OLE DB connection to a SQLServer database

KeithSandell — Mon, 02 Apr 2012 01:00:06 GMT

Start here: http://code.google.com/p/pyodbc/

You'll have to download and install pyodbc, make sure you get the version that matches the python version you have installed, most likely the version that installed with ArcGIS.

###########

import arcpy, pyodbc

cnxn = DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

cursor = cnxn.cursor()

cursor.execute("{any legal SQL stmt you would execute in SSMS}")

cnxn.commit()

###########

I use it to get the functionality out of ArcGIS and the speed of SQL.

Re: Creating an OLE DB connection to a SQLServer database

JamesCrandall — Tue, 20 Nov 2012 14:13:42 GMT

Start here: http://code.google.com/p/pyodbc/

You'll have to download and install pyodbc, make sure you get the version that matches the python version you have installed, most likely the version that installed with ArcGIS.

###########

import arcpy, pyodbc

cnxn = DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password

cursor = cnxn.cursor()

cursor.execute("{any legal SQL stmt you would execute in SSMS}")

cnxn.commit()

###########

I use it to get the functionality out of ArcGIS and the speed of SQL.

Have you or others experienced issues of secondary runs when pyodbc is run from ArcGIS/ArcCatalog(9.3.1) Toolbox?

I get a 9999998 Error when the script/tool with pyodbc is run a second time.

note: I don't have this issue with cx_Oracle. Just exploring alternative ways to connect to non-spatial data repositories.

Re: Creating an OLE DB connection to a SQLServer database

JamesCrandall — Wed, 21 Nov 2012 14:36:41 GMT

Apparently ArcCatalog simply doesn't like pyodbc all that much. Supposedly it has to do with the way ArcCatalog handles conneciton pooling.

...sticking with cx_Oracle for the time being.