1

I have the following sql update query that I am using as part of a larger python script. It essentially takes data from one Feature Class (FC) to update a Versioned View of a different FC.

Important to note the SQL query runs with no errors in SQL Server Management Studio but it fails when trying to run in python.

Code:

#Set Variable for SDE connection
# ArcSDESQLExecute allows SQL statements to be run inside the Python script
sdeConn = arcpy.ArcSDESQLExecute(Environ)
sql_statement = '''EXEC devsde.sde.set_current_version "SYOKLIC1.test_1";
       EXEC devsde.sde.edit_version "SYOKLIC1.test_1", 1;
       BEGIN TRAN;

       UPDATE [devsde].[syoklic1].[BOOSTER_evw]
       SET [devsde].[syoklic1].[BOOSTER_evw].[MaintenanceArea] =
          (
            SELECT [devsde].[syoklic1].[BOOSTER_Join].[MaintenanceCode]
            FROM [devsde].[syoklic1].[BOOSTER_Join]
            WHERE[devsde].[syoklic1].[BOOSTER_Join].[VMID]
            = [devsde].[syoklic1].[BOOSTER_evw].[VMID]
            AND [devsde].[syoklic1].[BOOSTER_Join].[MaintenanceArea]
            != [devsde].[syoklic1].[BOOSTER_Join].[MaintenanceCode]
          );

         COMMIT;
         EXEC devsde.sde.edit_version "SYOKLIC1.test_1", 2;'''
sql_statement_list = sql_statement.split(";")
for query in sql_statement_list:
    try:
        # you first need to designate which transactioanl version sql should be inteacting with
        sdeConn.execute(query)
        print "execution started " + "{{{" + query + "}}}"
    except Exception as exc:
        print "execution failed " + "{{{" + query + "}}}"
        print traceback.format_exc()
        print exc
        pass

The error comes up for the BEGIN TRAN and COMMIT statements

Error text:

ArcSDESQLExecute: StreamExecute ArcSDE Extended error 266 [Microsoft][SQL
Server Native Client 11.0][SQL Server]Transaction count after EXECUTE
indicates a mismatching number of BEGIN and COMMIT statements. Previous
count = 1, current count = 0.

How do I work around this?

Perhaps a way to format so that ArcSDESQLexecute can read the entire statement where the number of Begins/Commits are equal.

  • 1
    The purpose of that capability is to execute singleton statements, not complex multi-statement scripts. The transaction environment should be under the exclusive control of the ArcGIS connection. I can't see this ever working. – Vince Jul 18 '17 at 19:32
  • 1
    instead of using arcpy maybe you could try one of the python SQL Server library and issue the commands directly. I've used pyodbc in the past, but for non geodatabase queries, should still work though. mkleehammer.github.io/pyodbc – Dowlers Jul 18 '17 at 19:51
  • 1
    Use pymssql Python package. It's been running in productions I take care of for several years and it's been working for us really great. – Alex Tereshenkov Jul 18 '17 at 20:27
  • @AlexTereshenkov do you know of any helpful install guides for that module? – Simon.y Jul 20 '17 at 16:56

Your Answer

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

Browse other questions tagged or ask your own question.