GP service retruns default result

[wadsonmakari]

Dear all,

I have a python script gp service that is doing something quite simple, connecting to a Netezza data warehouse and return a string of distinct values from one table column see script below

# Import modules
import arcpy,os,time

# Python environment
arcpy.env.overwriteOutput = True

conFile = r"PathToConnectionFile\connection.sde"
arcpy.AddMessage("Set connection file")
inFC = conFile + "\DBNAME.DBA.TABLENAME"
arcpy.AddMessage("Set update table from netezza")
sOrgan = arcpy.GetParameterAsText(0)
startTime = arcpy.GetParameterAsText(1)
endTime = arcpy.GetParameterAsText(2)
Assets = arcpy.GetParameterAsText(3)

arcpy.AddMessage("Input parameters set")

field = "FIELDNAME"
arcpy.AddMessage("Field set")
#where clause
sWhere = "ORG_ID='" + sOrgan + "' and UPDATE_TIMESTAMP BETWEEN timestamp '" + startTime + "' and timestamp '" + endTime + "'"

arcpy.AddMessage("Where clause set")
s=""

arcpy.AddMessage("Selecting distinct values")
try:
    # search cursor wrapped in list generator creating list of all value
    arcpy.AddMessage("Inside try")
    with arcpy.da.SearchCursor(inFC, (field),sWhere,sql_clause=('DISTINCT','ORDER BY FIELDNAME')) as cursor:
        arcpy.AddMessage("Processing data")
        for row in cursor:

            s = s + str(int(row[0]))+ ","
               
    #get Assets list as output
    arcpy.SetParameterAsText(3, s)
    #print arcpy.AddMessage(s)
   
except:
    arcpy.AddMessage(arcpy.GetMessages())
finally:
    arcpy.AddMessage("Process complete")

I have run the script as a script tool, generated the result and published the result to ArcGIS for Server 10.2.

I can consume this GP service from my web app without any problems and return the values as I expect, however after a few hours the GP service will start to return the default result i.e. the result obtained when I published the GP service regardless of what parameters I am passing in.

The annoying thing is that the GP service is not throwing an error but just returns the default result which will be wrong for the parameters passed in.

As you can see in the script above I have put several arcpy.AddMessage statements to try and figure out where the GP service is failing. I run the gp service with the same input values my ArcGIS server services directory and view all the messages and I can report the following. The script is failing on this line i.e. script is not executing the line

with arcpy.da.SearchCursor(inFC, (field),sWhere,sql_clause=('DISTINCT','ORDER BY FIELDNAME')) as cursor:

as the last message I see is "inside try" and I do not see "Processing data" when the script/gp service is failing.

The GP service does not return any errors even though the code is wrapped within a try/catch.

The only thing that is curing this problem for me is to restart the GP service which is not ideal in a Production environment. Any suggestions welcome.

[JonathanQuinn]

Hm, could you be missing the actual error message?  If the except statement is supposed to print the messages from the tool, but it doesn't fail at the tool, then it may not be printing the messages you're expecting to see.  What if you add a statement to print a traceback error, and see if that returns something?  If it's not getting to the "inside try" print statement, then it has to be in the except statement, so you can add additional messages there to see if it isn't an arcpy error.

[wadsonmakari]

Hi Jonathan,

Thanks for responding. The script is definitely getting to "inside try" but not to "processing data" when it fails for some reasons it is the line

with arcpy.da.SearchCursor(inFC, (field),sWhere,sql_clause=('DISTINCT','ORDER BY FIELDNAME')) as cursor:

that is not executing for some reason. I would have expected the script to jump to the except: part and show the message there. Will try the traceback error and see if I get anything useful. The strange thing is works if after I restart the gp service. I don't know if there are any known issues with connections to netezza from esri? recommended setttings etc that I may have missed?

Any pointers will be gratefully received

[wadsonmakari]

Jonathan,

I have added a trace back as you suggested and I get this python error after a few hours

Cannot open

'\\RestofPath\TestActiveAssetsGPService.GPServer\extracted\v101\Connection
to Netezza.sde\SPATIAL.DBA.TABLENAME'

The strange thing is if I restart my gp service it works as I expect it to for a few hours and then it starts throwing the above error again. It looks like access to my Netezza table is denied after a few hours for some reason?

Any reason for this behaviour?

[JonathanQuinn]

Is there anything on the Netezza side that would drop a connection after a certain time?  You can try to set the min instances to 0 for the GP service, so whenever it is requested, it'll spin up an instance and establish a connection to the database.  If that consistently works, then I would think that for some reason, the active connection to the database is dropping.  It may be something on the Netezza side.  You can try to run an SDEINTERCEPT to see the communication between the Server and the database.  This will work for ArcGIS Server and should be set up on the ArcGIS Server machine.