topic SQL Server - changing version within arcpy.ArcSDESQLExecute in Geodatabase Questions

SQL Server - changing version within arcpy.ArcSDESQLExecute

Steve_Salas — Wed, 25 Jan 2023 22:49:36 GMT

I am testing some methods for quality control of data edited within a version (traditional, not branch) that is not the Default version. I am exploring using the versioned views through arcpy.ArcSDESQLExecute (currently via ArcGIS Pro 2.9.5). To change the version, I am calling an "execute" on the object created with ArcSDESQLExecute

EXEC dbo.set_current_version "versionname"

That seems to work fine if I am the owner of the version and can just supply the version name. However if I need to switch to a version owned by someone else and the owner format is domain\username (SQL Server Active Directory authentication), then I am trying to pass domain\username.versionname of some sort, usually returning "version not found". If the owner of the version is not of the domain\username format, I seem to be able to make this work.

I am quite new to SQL Server so I might be failing to understand something there, or I'm not handling the backslash well for Python, or this might just be a really bad idea! Thank you if anyone has a hint, otherwise I'll keep plugging away at this.

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

MarlonAmaya — Thu, 26 Jan 2023 01:30:39 GMT

Hi @Steve_Salas ,

Are the versions owned by the domain accounts set to public?

Versions owned by AD user's is not a problem.

To see if we can even change to a AD version using your account, you can also try using the arcpy change version in python. This will let us know what the correct syntax would be.

https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/changeversion.htm

Marlon

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

Steve_Salas — Thu, 26 Jan 2023 15:47:13 GMT

Hi Marlon, the versions are actually Protected, so visible and I can Change Version:

arcpy.management.ChangeVersion("CS Pasture Polygons", "TRANSACTIONAL", r'"domain\username".EDITS-20230123-105229', None, "INCLUDE")
<Result 'CS Pasture Polygons'>

If I try to transfer that same format to ArcSDESQLExecute:

ASEObj.execute(r'EXEC dbo.set_current_version "domain\username".EDITS-20230123-105229')
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "C:\Program Files\ArcGIS\Pro\Resources\ArcPy\arcpy\arcobjects\arcobjects.py", line 44, in execute
return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args)))
AttributeError: ArcSDESQLExecute: StreamPrepareSQL ArcSDE Extended error 102 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '.'.

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

MarlonAmaya — Thu, 26 Jan 2023 16:36:18 GMT

Hi @Steve_Salas

Please try

ASEObj.execute(r'EXEC dbo.set_current_version '"domain\username".EDITS-20230123-105229'')

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

Steve_Salas — Thu, 26 Jan 2023 22:11:13 GMT

Definitely flagged the unbalanced quotes in the Python window in ArcGIS Pro, but I hit return anyway...

ASEObj.execute(r'EXEC dbo.set_current_version '"domain\username".EDITS-20230123-105229'')
File "<string>", line 1
ASEObj.execute(r'EXEC dbo.set_current_version '"domain\username".EDITS-20230123-105229'')
^
SyntaxError: invalid syntax

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

MarlonAmaya — Fri, 27 Jan 2023 22:58:51 GMT

@Steve_Salas

You may want to submit a support ticket.

Marlon

Re: SQL Server - changing version within arcpy.ArcSDESQLExecute

Steve_Salas — Thu, 16 Feb 2023 15:16:53 GMT

Worked with @MarlonAmaya via an ESRI support ticket and he arrived at this:

ret = ASEObj.execute( '''exec dbo.set_current_version '"DOM\USERNAME".EDITS-20230210-084247' ''')

And for scripting purposes with version name as a variable I ended up with:

qcVers = '"DOM\USERNAME".EDITS-20230210-084247'
ret = ASEObj.execute( '''exec dbo.set_current_version '%s' ''' % qcVers)