Error when using arcpy.ArcSDESQLExecute() to connect to SQL Server - ArcGIS 10.4

Question

I am able to connect to a MS SQL Server 2012 geodatabase with the arcpy.ArcSDESQLExecute() when using the .sde connection file pre-created in ArcGIS.

I am not able to connect to a MS SQL Server 2012 geodatabase with the arcpy.ArcSDESQLExecute() when not using the .sde connection file pre-created in ArcGIS.

arcpy.ArcSDESQLExecute(server='mymachinename', instance=None, database='mydbname', user='myuser',password='mypass')

It seems as ArcSDESQLExecute can only connect when providing the log-in info to the SDE App Server and not using the Direct Connect:

RuntimeError: Object: Connection error Entry for SDE instance not found in services file

I have also tried to use the standard port for SQL Server (which is 1433) and the default name for SQL Server instance (MSSQLSERVER in my case):

arcpy.ArcSDESQLExecute(server='mymachinename', instance=1433, database='mydbname', user='myuser',password='mypass')

and

arcpy.ArcSDESQLExecute(server='mymachinename', instance='1433', database='mydbname', user='myuser',password='mypass')

This time another error is thrown:

RuntimeError: Object: Connection error SDE not running on server

I don't have any SDE App Server; I just want to connect to an SQL Server geodatabase with direct connect without first creating .sde connection file.

Is it a limitation of the ArcSDESQLExecute that you cannot connect on-the-fly to a geodatabase or am I missing some parameters here?

Answer 1

Use sde:sqlserver:mymachinename for your server instance

arcpy.ArcSDESQLExecute(server='mymachinename', instance='sde:sqlserver:mymachinename', database='mydbname', user='myuser',password='mypass')

This makes your Direct Connect work rather than using an SDE service instance.

I recall this happening a couple of versions ago when we were moving from SDE services to Direct Connects and had to change the SDE connections in ArcCatalog from the port number like 5151 to using the sde:sqlserver:servername string. This appears to have been replaced in the most recent versions where we can just enter the server name in ArcCatalog GDB connections, however there are still some tools around (like your ArcSDESQLExecute) that ask for the instance.

See Geodatabase Connections.