I have a ArcGIS Server 10 geoprocessing service right now that takes in a string and runs a parameterized query to SQL Server 2008R2 through pyodbc. The service works, but only every other time. I can run it on my machine locally every time and also on the machine this runs on (also every time), but I can't run this through ArcGIS Server but every other time, either through the web interface or the ArcGIS JavaScript API.
Every other time, I get this error: Error Executing Task. Error processing message
... along with a 500 error.
Is it maybe a problem with pyodbc? Or am I overlooking something small?
Here's what I'm working with (snipped SQL and a little extra). There's two input parameters, SearchTerm and SearchType (only can be "standard" in this snippet), and one output parameter. All parameters are strings.
import arcpy
import pyodbc
import sys
import urllib
connection = None
cursor = None
def search(search_term, search_type):
try:
global connection
search_term = urllib.unquote_plus(search_term)
connection = pyodbc.connect(...)
if search_type == "standard":
return string_search(search_term) # snipped out other use case.
else:
return "[]"
except:
print_message(sys.exc_info(), True)
return '{"error": "' + str(sys.exc_info()) + '"}'
def string_search(search_term):
global cursor
cursor = connection.cursor()
sql = """SELECT * FROM Resources WHERE ResourceName LIKE ?"""
search_term = "%" + search_term.upper() + "%"
cursor.execute(sql, search_term)
return return_results()
def return_results():
global connection
global cursor
return_ids = ""
for row in cursor:
resource_name = ""
if row.ResourceName is None:
resource_name = "Not Available"
else:
resource_name = row.ResourceName
return_ids = return_ids + ',{"ResourceId":' + str(row.ResourceId) + '}'
return_ids = return_ids[1:]
cursor.close()
connection.close()
return "[" + return_ids + "]"
def print_message(message, is_error=False):
message = str(message)
if is_error:
print "ERROR: " + message
arcpy.AddError(message)
else:
print message
arcpy.AddMessage(message)
if __name__ == '__main__':
if arcpy.GetArgumentCount() > 0:
return_text = search(arcpy.GetParameterAsText(0), arcpy.GetParameterAsText(1))
arcpy.SetParameterAsText(2, return_text)
else:
arguments = ["chicken", "standard"]
argv = tuple(arguments)
print search(*argv)