Creating an OLE DB connection to a SQLServer database

Question

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

JamesCrandall · Answer

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.

JamesCrandall · Answer

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.

KeithSandell · Answer

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.