Edit SQL Server table (not registered with geodatabase) with arcpy

Question

I am trying to update some rows in a SQL Server database table (not registered with geodatabase) with arcpy.da.UpdateCursor. However, when running the deleteRow(), I get the error return without exception set error. Updating geodatabase tables works fine.

I guess it might be a limitation of arcpy which can't edit a non-registered with geodatabase RDBMS table (in this case, SQL Server), but I want to make sure I don't miss anything.

Can ArcSDESQLExecute class help me to update SQL Server table that are not registered in geodatabase (have found no info on that in the Help)? Any other workarounds?

EDIT:

I have ended up using ArcSDESQLExecute which works fine for executing SQL commands (including deleting rows) for tables that are not registered with geodatabase.

sde_conn = arcpy.ArcSDESQLExecute(r"C:\GIS\Temp\nongeo.sde")
sql_statement = """delete from added_table WHERE AddID = 2 """

Answer 1

If you need just basic SQL access on something that is not a registered ArcSDE table, I recommend using a simple ODBC connection object rather than mucking about with the overhead of ArcPy. ArcPy adds so many objects which you likely do not need that it slows down what should be a quick and simple process. My personal choice is pyodbc which can be found at http://code.google.com/p/pyodbc/.

If you just want to delete a row you can use simple SQL statements

import pyodbc
cn = pyodbc.connect('DRIVER={SQL Server};SERVER=YourServerName;DATABASE=YourDatabase;UID=UserName;PWD=Password')
sql = "DELETE FROM table_name WHERE some_column=some_value"
cur = cn.cursor()
cur.execute(sql)
cn.commit()

If your data is registered as an SDE table then you will need to stick with an ArcPy/ArcObjects method to add, delete or update a record to prevent corrupting any part of the database. You can still use the pyodbc (or other odbc connectors) to read the SDE tables.