Why does a geoprocessing service fail every other time it is run?

Question

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)

Answer 1

I have experienced exactly the same problem earlier and was looking for the solution on the Internet. It seems as using pypyodbc library works much better. I was able to publish a GP service and call stored procedures & perform multiple SQL statements obtaining as a result object record sets without any problem.

Pypyodbc is a pure Python ODBC interface module based on ctypes. It has the same installation file (.py file), but since you will work with ArcGIS Server (64bit) yet publishing from ArcMap (32bit) you will need to install the library 2 times (each time specifying different Python path).

I've used cmd to get to a place for 32bit and 64bit Python installed and then called the setup.py file.

C:\Python27\ArcGIS10.2\python.exe setup.py install
C:\Python27\ArcGISx6410.2\python.exe setup.py install

UPDATE: When having the GP service tested heavily for running multiple jobs executing many SQL statements including stored procedures, pypyodbc got crashed now and then with a general error.

Solution: use pymssql (A simple database interface for Python that builds on top of FreeTDS to provide a Python DB-API (PEP-249) interface to Microsoft SQL Server) which works stable so far.