SQL Server - changing version within arcpy.ArcSDESQLExecute

[Steve_Salas]

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. 

[MarlonAmaya]

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

[Steve_Salas]

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 '.'.

[MarlonAmaya]

Hi @Steve_Salas 

Please try

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

[Steve_Salas]

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

[MarlonAmaya · Accepted solution]

@Steve_Salas 

You may want to submit a support ticket. 

Marlon

[Steve_Salas]

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)