Error in Select Numeric type column in pyodbc with ArcCatalog?

Question

I created a python script that uses "pyodbc" plugin to connect a SQL Server database and write out data. It was imported in an ArcGIS 10.1 tool and run from there. try:

arcpy.AddMessage('FLAG 0')
cnxn = pyodbc.connect('DSN=' + DSN + ';UID=' + UID + ';PWD=' + PASS)
cursor = cnxn.cursor()

arcpy.AddMessage('FLAG 1')
# Retrieves all the records from the All Obs for KML view
if cnxn is None:
    arcpy.AddMessage('cnxn is None')
else:
    arcpy.AddMessage('cnxn is not None')

if cursor is None:
    arcpy.AddMessage('cursor is None')
else:
    arcpy.AddMessage('cursor is not None')

cursor.execute('SELECT LocationName, Lat from VW_Obs_for_KML_Exp')
for row in cursor:
     arcpy.AddMessage(row.LocationName + ', ' + str(row.Lat))

arcpy.AddMessage('FLAG 2')

except (pyodbc.IntegrityError, pyodbc.ProgrammingError, pyodbc.Error):
    arcpy.AddMessage("PYODBC Error. Details: " + str(sys.exc_info()[:2]))
except TypeError, e:
    arcpy.AddMessage("TypeError. Details: " + str(sys.exc_info()[:2]))
else:
    arcpy.AddMessage("No error occurred.")

The script accesses a Table and outputs 'Lat' as Numeric(38, 8) data type. It worked perfect the first time from ArcCatalog. However, it threw out an error the second time and onwards. Detailed error stack trace is as follow:

FLAG 0
FLAG 1
cnxn is not None
cursor is not None
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "C:\Python27\ArcGIS10.1\Lib\decimal.py", line 543, in __new__
    m = _parser(value.strip())
TypeError: 'NoneType' object is not callable
TypeError. Details: (<type 'exceptions.TypeError'>, TypeError("'NoneType' object is not callable",))
Process is complete.

So I had to restart ArcCatalog to successfully run it again. By the way, it also worked well when run from Python in the command line prompt. So this error only occurred when run from ArcCatalog.

Is there anyone who has worked with "pyodbc" plugin and experienced similar problem before?

Answer 1

I came across this looking for something else, however I think your issue may be with the connect string and the DSN parameter, try adding Trusted_Connection=yes; after the DSN in the connection string.

I usually run my arcpy scripts with their own database user and permissions without using Microsoft Authentication and the connection string looks like this

'DRIVER={SQL Server};Server=' + machinename + ';DATABASE=' + DBName + ';UID=' + DBUser +';PWD=' + DBPass

It also appears that with a DSN you may not want to be sending a username.

more info here: http://code.google.com/p/pyodbc/wiki/ConnectionStrings