2

I am trying to populate a folder with ArcSDE database connection files for all the databases within a server instance. I need to be able to do this for several of our servers.

Although I know within SQL Server how to get a list of DB names from the sys.databases table, using the arcpy function CreateDatabaseConnection I cannot specify "sys" or "master" to make the connection. Therefore, I need to know the name of a database currently in the server. I am not sure how to make the check without hard-coding a name.

import arcpy
arcpy.env.overwriteOutput = True

#Input the location where the database connection files will be stored
connectionLocation = "F:/FolderName"

#Get the list of servers
arcpy.CreateDatabaseConnection_management(out_folder_path= arcpy.env.scratchFolder,out_name= "GIS_USERCONFIG" ,database_platform="SQL_SERVER",\
                                          instance = "XXXXXXX" ,account_authentication="DATABASE_AUTH",username="user",password="password",\
                                          save_user_pass="SAVE_USERNAME",database= "GIS_USERCONFIG",schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")
sdeConn = arcpy.ArcSDESQLExecute(arcpy.env.scratchFolder + "/GIS_USERCONFIG.sde")
serverListSQL = sdeConn.execute("Select name from userservers")
serverList = []
for i in range(0,len(serverListSQL)):
    serverList.append(serverListSQL[i][0])

#Make a folder for each server instance and build the connection files for all DBs in the server instance
for server in serverList:
    folder = connectionLocation + "/" + server
    if arcpy.Exists(folder):
        print server + " folder already created"
    else:
        try:
            arcpy.CreateFolder_management(connectionLocation, server)
            print "Created Folder " + server
        except:
            print "failed to create folder " + server

    #
    arcpy.CreateDatabaseConnection_management(out_folder_path= arcpy.env.scratchFolder,out_name= server ,database_platform="SQL_SERVER",\
                                          instance = server ,account_authentication="OPERATING_SYSTEM_AUTH",\
                                          database= "master",schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")
    sdeConn = arcpy.ArcSDESQLExecute(arcpy.env.scratchFolder + "/" + server + ".sde")
    dbListSQL = sdeConn.execute("Select name from sys.databases")
    dbList = []
    for i in range(0,len(serverListSQL)):
        dbList.append(dbListSQL[i][0])
    for db in dbList:
        arcpy.CreateDatabaseConnection_management(out_folder_path= folder ,out_name= db,database_platform="SQL_SERVER",\
                                          instance = server ,account_authentication="OPERATING_SYSTEM_AUTH",\
                                          database= db,schema="#",version_type="TRANSACTIONAL",version="dbo.DEFAULT",date="#")

Basically my issue is with the 2nd call for the function "CreateDatabaseConnection_management" since it needs a database that exists in the database (and I could not use "master or sys"). If you open this as a tool in arcmap, the input is a drop-down box that populates choices based on the databases in the chosen server instance. Is there a way to do this in the code?

migrated from stackoverflow.com Oct 13 '14 at 12:30

This question came from our site for professional and enthusiast programmers.

2

You may use ListWorkspaces method (once you make the connection) to list the existing databases within SDE:

Lists all of the workspaces within the set workspace. Search conditions can be specified for the workspace name and workspace type to limit the list that is returned.

workspaces = arcpy.ListWorkspaces("*", "SDE")
    for workspace in workspaces:
        print workspace

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.