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[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[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?
أكثر...
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[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[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?
أكثر...