topic Re: How can I list the target location associated with a replica? in Python Questions

How can I list the target location associated with a replica?

RebeccaStrauch__GISP — Thu, 23 Feb 2017 23:44:46 GMT

SOLUTION NOTE: Make sure to look at the final solution (after the one marked as answer)

https://community.esri.com/message/669452-re-how-can-i-list-the-target-location-associated-with-a-replica?commentID=669452&…

Q: Does anyone know how to access the "target path" information from existing replicas? ....in a python script.

With the answer in my related thread where I use arcpy.da.ListReplicas("mySDEGDB" to get a list of all my replicas, and can extract attributes like the common name (vs how SQL lists it). However, it doesn't look like the "target path" is one of the attributes that I see as exposed. ListReplicas—Help | ArcGIS for Desktop

This will eventually be part a few tools in an addin.

Desktop 10.3.1 (soon 10.5.x)

ArcSDE 10.3.x - SQL 2008 (soon to be 2012, 2014 or 2016)

I have also submitted a tech support call, but have not heard anything back yet. My guess is it will take something more than arcpy.

tagging Geodatabase‌

Re: How can I list the target location associated with a replica?

RebeccaStrauch__GISP — Fri, 10 Dec 2021 22:00:20 GMT

With the help of tech support, I am partially to my goal. Using the SQL command they supplied, I'm able to query and get the results I need in MS SQL Manager Studio

use my_database
SELECT ITEMS.Definition.value( '(/GPReplica/SibConnectionString)[1]', 'nvarchar(max)') 
   AS "child path" from DBO.GDB_ITEMS 
   AS ITEMS INNER JOIN dbo.GDB_ITEMTYPES 
   AS ITEMTYPES ON ITEMS .Type = ITEMTYPES .UUID 
   WHERE ITEMTYPES. Name = 'Replica'
‍‍‍‍‍‍

pretty slick. Then looking the help ArcSDESQLExecute—Help | ArcGIS Desktop I tried to run the script within python but was getting an error " AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65"

Some browser searching brought me to a couple stackexchange (no help) and to Joshua Bixby comments How can I access a SQL Server 2012 database sequence value in arcpy which seems to suggest I need to use a cast() for my SQL statement.

This is new to me, and although I have tried many combinations, I have not found the correct location for my cast parenthesis, for lack of a better description, to get this to work. My python snippet is below.....spacing/tabs for the SQL statement might be off to in this sample. I have tried adding continuation and or stringing it out all in one line, with no luck. I think it is the "sql = " statement that is cause me problems.

  
setOwner = "dbo"
masterConnect = "__prod-me@wcgis_master.sde"
masterGDB = r'Database Connections\{0}'.format(masterConnect)
sde_conn = arcpy.ArcSDESQLExecute(masterGDB)

sql = '''SELECT ITEMS.Definition .value( '(/GPReplica/SibConnectionString)[1]', 'nvarchar(max)') 
AS "child path" from {0}.GDB_ITEMS 
AS ITEMS INNER JOIN {0}.GDB_ITEMTYPES 
AS ITEMTYPES ON ITEMS .Type = ITEMTYPES .UUID 
WHERE ITEMTYPES. Name = 'Replica';
'''.format(setOwner)

sde_return = sde_conn.execute(sql)
for i in sde_return:
  print('{}: {}'.format(*i))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Any suggestions to get this to work?

Re: How can I list the target location associated with a replica?

JoshuaBixby — Tue, 28 Feb 2017 23:13:56 GMT

I don't fully get why nvarchar(max) is causing an issue, but change it to nvarchar(1000) or some other large-ish value on Line 07.

Re: How can I list the target location associated with a replica?

RebeccaStrauch__GISP — Fri, 10 Dec 2021 22:00:23 GMT

Full Solution: of what I was trying to get to. This would work pretty much as a standalone, with a few modifications for local use. I plan to have this included in several of my tools, and a tool just to give me some type of status.

Joshua Bixby ...we (or should I say tech support) figured that out last night. I was just about to post my solution and mark as assumed when your comment came in (now will give you the check for answering).

import arcpy

# shows messages for python window or tool
def myMsgs(message):
     arcpy.AddMessage(message)
     print(message)

# Note: sample uses arcpy.ArcSDESQLExecute(mySDEConneciton) to
#   extract the xml string, then sde_conn.execute(sql) to 
#   grab the SibConnectionString value, which is the child path. 
#   This method could also extract other by modifying sql = type line.
#   Most likely, addition tests and "splits" would be need to pull
#   out the infomation.  This includes:
#         Name
#         ReplicaVersion, CreationDate, AccessType, ModelType,
#         DatasetName (and other information about the datasets)
#     However, "replica." (used in MyMsg/Print) allows for most 
#     things needed (but not path I wanted).  These include
#         hasConflicts, isParent, isSender, lastReceive, lastSend, name
#         owner, type, version

setOwner = "dbo"     # maybe be sde or other
masterConnect = "__prod-me@wcgis_master.sde"    # your connection with admin rights
masterGDB = r'Database Connections\{0}'.format(masterConnect)
sde_conn = arcpy.ArcSDESQLExecute(masterGDB)

# refresh replica list after process, and list all replicas and last sync date  
replicaList = arcpy.da.ListReplicas(masterGDB)
replicaCount = len(replicaList)
# get list of the target/child path
try:
     #sde_conn = arcpy.ArcSDESQLExecute(masterGDB)
     # NOTE: error came when nvarchar(max) was used. 1000 works for my current needs.
     sql = ("SELECT ITEMS.Definition.value('(/GPReplica/SibConnectionString)[1]','nvarchar(1000)') from {0}.GDB_ITEMS AS ITEMS INNER JOIN {0}.GDB_ITEMTYPES AS ITEMTYPES ON ITEMS.Type = ITEMTYPES .UUID WHERE ITEMTYPES. Name = 'Replica';".format(setOwner))
     childpathList = sde_conn.execute(sql)
     gotChildPath = True
except:
     gotChildPath = False
     myMsgs("not able to get child replica path list")


# This section is to print the info only. In tool, will do more.
if replicaCount == 0:
     myMsgs("No replicas found\n")  
elif replicaCount == 1:   # the path is a [] if more than one
     myMsgs("\n{0}\n  List current replicas for: {1}".format("*" * 80, masterGDB))
     for replica in replicaList:
          myMsgs("    - Name: {0} path: {2}  last update: {1}".format(replica.name, replica.lastReceive, childpathList.split(";")[0][9:]))
          #myMsgs("   {0}".format(replica.lastReceive))
     myMsgs("\n  #### Note: strange date means no syncronization has happened yet ####\n{0}".format("*" * 80))  
else:
     count = 0
     myMsgs("\n{0}\n  List current replicas for: {1}".format("*" * 80, masterGDB))
     for replica in replicaList:
          myMsgs("    - Name: {0}  last update: {1}".format(replica.name, replica.lastReceive))
          myMsgs("         Last sync:   {0}".format(replica.lastReceive))
          cpath = childpathList[count]
          if gotChildPath:
               myMsgs("         child-path:  {0}".format(cpath[0].split(";")[0][9:]))
          count =+ 1

     myMsgs("\n  #### Note: strange date means no syncronization has happened yet ####\n{0}".format("*" * 80))  

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This will produce an out put similar to:

I hope this will help others in the future.

Re: How can I list the target location associated with a replica?

ManviLather — Wed, 01 Mar 2017 01:59:46 GMT

Manvi Lather