AGS 10.1 Publishing geoprocessing service that references data on sde connection

[StéphaneCouderq]

I'm publishing a geoprocessing service (python script), which constains a connection to an SDE database.
I have declared (imported .sde file) in Arcgis server manager and checked "Same as publisher database connection".
I then publish the service.
Is this the correct way of publishing script + data from arcsde on 10.1 Server ?

I run into the following problem.
The script publishes fine and works on the server, accessing the arcsde data as planned.
However, when I try to publish the same script again under another name (the idea is to make changes to it), the script doesn't work. I checked the connection to the data in the python script on the server and see that it is absent (as on screen capture). On the first publishing, I had : g_ESRI_variable_1 = os.path.join(arcpy.env.packageWorkspace, u'mySdeFileName.sde')

Why is this ? What am I doing wrong ?

[BrettElliot]

I'm having a similar problem.

If you find out, please post the answer here!

[RickScott]

So, near as I can tell, the process that creates the variable substitution has a bug.  Go back to your original code, and determine what was substituted.  Based on what you've written, I'd say you need to replace this:

g_ESRI_variable_1 = os.path.join(arcpy.env.packageWorkspace, u'')

with this:

g_ESRI_variable_1 = os.path.join(arcpy.env.packageWorkspace, u'mySdeFileName.sde')

and possibly even this (this was my problem):

g_ESRI_variable_1 = os.path.join(arcpy.env.packageWorkspace, u'mySdeFileName.sde//mySdeFeatureClass')

HTH,
Rick Scott

[KevinHibma]

If this issue is the one I think it is, it'll be fixed in 10.2.

I'd however need the complete script/workflow to be absolutely certain.
There are workarounds to this in 10.1 (in addition to the suggestions Rick has of updating the published script).
Again, I'd need to see the whole workflow to provide workaround code.

[NilsBabel]

Hi, I think I'm having a similar problem.  But I'm not sure I understand the workaround.  Kevin, can you help clarify?  I have a script I'm trying to publish with 10.1.  My script uses a feature class from an enterprise database.  I have the feature class hard coded in the script because there is no need for the user to choose it as a parameter.  I'm referencing the feature class like this: r"Database Connections\gisReader@BaseData.sde\..."  The script publishes correctly.  But when I try and run it I get a log message that it can't find the feature class.  The database is registered with AGS and the "same as publishers connection" is checked.  The registered database name is gisReader@BaseData09.  Apparently when AGS runs the published script the registered database is not stored at Database Connections...  So how do I reference my feature class correctly so the geoprocessing service can find it?  Is it in the package workspace?

[KevinHibma]

If its not finding the sde connection in the published service, its probably due to the above reasons I've mentioned.

A couple quick things to try:
You said you're using "Database Connections\sde.sde.features" as the path to build up (yeah, thats a legit path in Desktop), but I'm pretty sure we got the bugs out of this particular path in 10.2. So, from the Database Connections node, right click your SDE connection and say "copy". Move to your working folder with your toolbox/script and right click and paste. Then update your script to reference the .SDE connection file from there. Like "c:\\data\\scripts\\connection to sde.sde\sde.sde.features"
If for some reason this doesn't work, use the OS.PATH trick:  os.path.join("c:\\data\\scripts\\connection to sde.sde", "sde.sde.features")

Hope this helps.

[NilsBabel]

That makes sense.  Thanks.

[magdakosior]

I had this problem as well.  If you want to fix your original python script (instead of the one that the server creates), you can also pass in the name of the sde filename as a parameter.  I've pasted my script below.  I am using Server 10.22 and it seems to me that the variable substitution is still messed up. Hope this helps

import arcpy, os

#This next line ensures that your .sde file gets copied from whereever you have it #to D:\arcgisserver\directories\arcgissystem\arcgisinput\service_folder\servicename.GPServer\extracted\v101

dbconn = os.path.join(r"D:\arcgisserver\directories\service_scripts", u'DB_NAME.sde')

conn = arcpy.GetParameterAsText(0) #here you pass in your DB_NAME.sde again

#this line will use arcpy.env.packageWorkspace (copied .sde) and the .sde filename

arcpy.env.workspace = os.path.join(r"D:\arcgisserver\directories\service_scripts", conn)

try:

    datasets = arcpy.ListDatasets("*", "Feature")

    for ds in datasets:

         print ds

except arcpy.ExecuteError, ex:

    arcpy.AddMessage("An error occurred : " + str(ex))

arcpy.SetParameterAsText(1, "output")