topic Re: CreateDatabaseConnection odd behavior in 10.1 in Python Questions

CreateDatabaseConnection odd behavior in 10.1

JasonKnisley — Wed, 19 Sep 2012 18:37:54 GMT

I have a Python script that which creates a database connection using arcpy.CreateDatabaseConnection_management(), and while the connection is created it ignores the version that is passed in as a parameter. The version is valid and the function does not return an error. In order to verify that the parameters were being passed appropriately, I even went so far as to edit the method management.py to print the parameters immediately before it calls:

retval = convertArcObjectToPythonObject(gp.CreateDatabaseConnection_management(*gp_fixargs((out_folder_path, out_name, database_platform, instance, account_authentication, username, password, save_user_pass, database, schema, version_type, version, date), True)))

In my code, I call:

arcpy.CreateDatabaseConnection_management(folderName, fileName, "SQL_SERVER", instance, "OPERATING_SYSTEM_AUTH", None, None, "SAVE_USERNAME", dbName, None, "TRANSACTIONAL", "DBO.test", None)

print "Version now set to " + arcpy.Describe(folderName + "/" + fileName).connectionProperties.version

In the attached screenshot the variables have been printed out in management.py immediately before creating the connection. Afterwards, you can see that while the version should be "DBO.test" it is instead "sde.DEFAULT".

How can I use Python to create a SQL Server database connection using Operating System authentication with a transactional version name?

Re: CreateDatabaseConnection odd behavior in 10.1

JasonKnisley — Thu, 20 Sep 2012 15:30:58 GMT

In case this clarifies things further, here is the workflow that I'm trying to model:

User starts ArcMap and wants to connect to a database the first time (their AD account already has permissions to the DB, but they may not yet own a version to use for editing)

A temporary connection is created with the default transactional version (sde.DEFAULT)

Call arcpy.ListVersions() on the temporary connection to see if the user already has a version; if not, use arcpy.CreateVersion_management() to create a private version with the user as the owner (ensures users can only edit under their version)

Remove the temporary connection and create a final connection using the version owned by the user (so far I haven't seen any way to simply modify the existing connection with a script)

I cannot script all four steps because as I mentioned in previous post, arcpy.CreateDatabaseConnection_management() seems ignores the version parameter (at least in the case of SQL Server using OS Authentication). If I script the first three steps, and then manually use the Create Database Connection tool from the ArcCatalog toolbox, the version will be set appropriately. That led me to attempt to build a model linking a script which does the first three steps with the tool that does the 4th step. That fails, however, because if the user doesn't already have a version in the database then the model won't run... I get "ERROR 000800: The value is not a member of sde.DEFAULT | DBO.parent" which is thrown by the Create Database Connection tool, even though I have the output from my script (which creates the version using a temporary connection) set as a precondition to running the Create Database Connection tool in the model. Using variables as input to the Create Database Connection tool doesn't seem to work well.

Does anyone know of a way to successfully automate this workflow? Has anyone had any success in 10.1 with getting arcpy.CreateDatabaseConnection_management() to recognize the version parameter under SQL Server with OS Authentication? Can someone from ESRI please verify that this is indeed a bug with arcpy.CreateDatabaseConnection_management()?

Re: CreateDatabaseConnection odd behavior in 10.1

RussellBrennan — Thu, 20 Sep 2012 15:43:48 GMT

Hi Jason,

Unfortunately this is a bug with the CreateDatabaseConnection the bug number is NIM083195. For the time being you have three options.

1. You can use the CreateArcSDEConnectionFile gp tool to create the connection.
2. After creating the connection you can edit it using ArcCatalog or the Catalog window in ArcMap to make it point to the new version.
3. Run the tool from ArcMap or ArcCatalog, the issue is only reproducible using Python.

Note, if you want to make a direct connection using the first option, you can use the direct connect syntax outlined here in the 10.0 help.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Making_a_direct_connection_from_ArcGIS_Desktop_to_a_geodatabase_in_SQL_Server/002q00000039000000/

Re: CreateDatabaseConnection odd behavior in 10.1

RyanNelson2 — Wed, 17 Apr 2013 21:08:09 GMT

Is there a time frame on a fix for this?

Re: CreateDatabaseConnection odd behavior in 10.1

RandyKreuziger — Wed, 17 Apr 2013 21:46:13 GMT

Is there a time frame on a fix for this?

Now for the bad news. This bug is fixed at 10.2 http://support.esri.com/en/bugs/nimbus/TklNMDgzMTk1
Over on the Desktop forum an ESRI employee wrote that SP2 for 10.1 turned into version 10.2. So don't look for any fixes to 10.1. I hope I'm wrong!

Re: CreateDatabaseConnection odd behavior in 10.1

Edwin — Tue, 18 Feb 2014 15:22:03 GMT

To solve this problem in version 10.1, this may be the alternative:

Perform ArcObject code called for the creation of the connection file

1. Install comtypes library from http://sourceforge.net/projects/comtypes/
2. Enable Python to use ArcObjects

When working to get ArcObjects and Python working together in version 10.1, you'll also need to make some modifications to your comtypes installation. The full solution is abbreviated here:

A. Navigate to your comtypes directory
    (ex. C:\Python\ArcGIS10.1\Lib\site-packages\comtypes)

B. Delete the following files (note that the �??safearray�?� files are not preceded by an underscore):
    automation.pyc
    automation.pyo
    safearray.pyc
    safearray.pyo

C. Open automation.py with a text editor and go to line 794 which is part of the �??ctype_to_vartype�?� dictionary. Add this line (including the comma):

    VT_BYREF|VT_BSTR,

D. Delete everything from the �??gen�?� directory.

E. Copy Snippets.py (module developed by Mark Cederholm) to C:\Python[version]\ArcGIS[version]\Lib

Note: Taken from http://www.sspinnovations.com/blog/2014/02/04/useful-scripts-non-programmer-converting-vba-scripts-python

3. Implementing the Python code with the call

Attachements:

Snippets.py
ArcObjects.py (CreateConnectionFile Phyton Implementation)

Re: CreateDatabaseConnection odd behavior in 10.1

VehaLa — Mon, 11 May 2015 08:20:25 GMT

I was trying to create Database User through Python but still encountered this problem. My ArcGIS for Desktop is 10.2.0.3348.