Problem settings arcpy.env.workspace to SDE connection in separate module on arcgis server

[MaximeDemers]

I have problem defining arcpy.env.workspace in a separate module when publishing the script as a Geoprocessing Service on arcgis server.

For instance:

my main tool script:

from myModule import myTool

workspace = "Database Connections\\GEOP011@demma18.sde"

tableName = "DEMMA18.MyTable"

myTool.execute(workspace, tableName)

myModule.myTool:

import arcpy

def execute(workspace, tableName)

    arcpy.env.workspace = workspace

    doSomething(tableName)

def doSomething(tableName)

     with arcpy.da.SearchCursor(tableName, ["OBJECTID"]) as cursor:
            for row in cursor:

                  print(row)

When published to the server, this script will return error : cannot open DEMMA18.MyTable

However, if I put the same code in a single .py file it runs on the server without error. Any Idea what is going wrong?

[JoshuaBixby · Accepted solution]

What if you specify the full path, one that Server can access to, instead of using "Database Connections" shortcut?

[MaximeDemers]

I think it is a good idea. I would like to try, but I dont manage the server myself, so I dont kow the path of the Database Connections folder on the server. I will ask for this information and come back with the result.

[MaximeDemers]

Joshua Bixby: but if I put the server path instead, wont I be able to publish the service because it will fails on my desktop?

[JonathanQuinn]

The publishing process should package up the .sde connection file and extract it to the Server directories.  The path to the .sde file will also be updated within your script, so it'll be able to use the copied .sde connection to connect to the data.

[MaximeDemers]

I now use the full path to a .sde file located somewhere that both my desktop and the server have access. That solve the problem as expected.

However, there is still something strange that happen. I have many GP Services to publish, and I have to close/reopen ArcMap between every publishing process. If not, I got the cannot open table error in the server logs when I call the service through REST.

Any Idea why? All GP Services use the same path to the same .sde file as workspace.

[JoshuaBixby]

https://community.esri.com/community/developers/gis-developers/python?sr=search&searchId=e99ed088-51...‌, https://community.esri.com/groups/geodatabase?sr=search&searchId=e50806e2-11de-4c1f-a0a3-8d04cab44c1...‌, https://community.esri.com/community/gis/enterprise-gis/arcgis-for-server?sr=search&searchId=f7fb6cc...‌

[MaximeDemers]

Joshua Bixby suggestion to use an absolute path to the SDE connection file instead of using the "Database Connection" shortcut was a good idea.

I copied the desired connection file into a shared server that both my desktop and arcgis server have access. I now use this file to define the arcpy.env.workspace, and it works.

[MitchHolley1]

I would give Joshua Bixby the credit for answering the question.