0

I have the following issue when using the Create Database View tool in ArcMap.

My query runs fine in Microsoft SSMS and Visual Code (with mssql extension) against the enterprise geodatabase.

But for some reason, when I'm trying to create the view using Create Database View in ArcMap, I get the following error.

Traceback (most recent call last):

  File "D:\Beni\OT\Databases\Actualizare\Tools\Scripts\10_Creare_view.py", line 39, in <module>

    arcpy.CreateDatabaseView_management(in_db,numeView,defView)

  File "c:\program files (x86)\arcgis\desktop10.5\arcpy\arcpy\management.py", line 4318, in CreateDatabaseView

    raise e

ExecuteError: ERROR 999999: Error executing function.

[[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '('.]

DBMS table not found

Failed to execute (CreateDatabaseView).

ERROR 999999: Error executing function.

[[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '('.]

DBMS table not found

Failed to execute (CreateDatabaseView).
  • I can successfully connect to the Enterprise Geodatabase in ArcMap and create other views using the same tool, on the same geodatabase.

  • I am able to create the view via Catalog window in ArcMap, using right-click on geodatabase connection - New - View. It works flawlessly this way and it doesn't complain about syntax.

  • I could create the views manually, via the above-mentioned method, but I am trying to automate the operation via the Create Database View tool. This is not the only query that fails with this tool (I have about 400 of them which work and about 70 which fail).

I checked the syntax a thousand times and I still can't figure it out. Maybe someone here could help me understand the issue. My only conclusion is that this could be a bug in the Create Database View tool, with certain types of queries.

The SQL query looks like this:

SELECT
    CAST (ROW_NUMBER() OVER (ORDER BY ADM.DENUMIRE) AS INT) AS OBJECTID,
    ADM.DENUMIRE,
    CAST(ADM.LOC_COMPONENTE_ALE_MUN_ORAS AS DECIMAL) / CAST(iif(AGR.ANUL_2014 = 0, -1, AGR.ANUL_2014) AS DECIMAL) AS DENSITATEA_ASEZARILOR_URBANE,
    ADM.LOC_COMPONENTE_ALE_MUN_ORAS,
    AGR.ANUL_2014 AS SUPRAFATA,
    ADM.SHAPE
FROM
(SELECT
    JUD.OBJECTID,
    JUD.DEN_JUD AS DENUMIRE,
    ADM.LOC_COMPONENTE_ALE_MUN_ORAS,
    JUD.SHAPE
FROM
(SELECT
    JUDET,
    SUM(LOC_COMPONENTE_ALE_MUN_ORAS) AS LOC_COMPONENTE_ALE_MUN_ORAS
FROM gisdata_owner.ADM010 AS ADM
GROUP BY JUDET) AS ADM
RIGHT OUTER JOIN
gisdata_owner.JUDETESIMP AS JUD
ON JUD.DEN_JUD = ADM.JUDET) AS ADM
INNER JOIN
(SELECT
    AGR.MACROREGIUNI_REGIUNI_DE_DEZVO AS DENUMIRE,
    AGR.ANUL_2014
FROM gisdata_owner.AGR101A AS AGR
WHERE AGR.FORME_DE_PROPRIETATE = 'Total'
AND AGR.MODUL_DE_FOLOSINTA_A_FONDULUI = 'Total') AS AGR
ON AGR.DENUMIRE = ADM.DENUMIRE

Setup info

Windows 10 Pro v. 2004 (build 19041.572)

ArcGIS Desktop Advanced / ArcMap 10.5.0.6491

SQL Server 2014 Developer Edition (12.0.6118.4)

  • That error appears to be generated by the Microsoft driver, so there's probably not much you can do. Note that computing a row_number objectid column could cause difficulty in the selection environment of ArcGIS -- the purpose of an objectid is to uniquely identify a row, to tie the geometry to the attribute record since the row_number doesn't skip rows that it doesn't select, reelection by row_number could return a different row. It is best practice to use an actual field which won't change values for the rowid column. – Vince Oct 28 at 12:31
  • @Vince Thanks for the advice related to OBJECTID. Will certainly keep it in mind. In my particular case, I found out that using a fixed OBJECTID field may produce duplicate values in certain queries... I have to look more into it. Then, if the Microsoft ODBC driver is the real culprit here, I could try using a newer version. I am currently using v.11 and, after a little digging, I found out that ArcGIS 10.5 also supports v.13. I will try to update it and keep you posted if I find an improvement -- desktop.arcgis.com/en/system-requirements/10.5/… – Beniamin Stoica-Fuchs Oct 28 at 19:31
  • 1
    Duplicate objectid values means a faulty query. Adding a row_number to hide the fault doesn't make the result more valid. – Vince Oct 28 at 20:44
  • You're right concerning row_number. I will look into it. ODBC driver update didn't make any difference. After some more digging, I am now pretty sure it's a bug. I analyzed the command output of the Geoprocessing Window while the tool is running and I discovered the SQL query is missing the division sign ("/"). Apparently, the tool chooses to ignore it, hence the error thrown by the ODBC driver. The same SQL query works perfectly with multiplication, so I think it is safe to rule out syntax issues in other parts of the query. I guess I'll contact Esri Technical to report this issue. – Beniamin Stoica-Fuchs Oct 30 at 8:25
  • A bug report on 10.5.0 isn't going to stir action. The current release is 10.8.1. At a minimum, you should be using 10.5.1 with the dozen or so critical patches. If that shows the same behavior, then 10.6.1 with its patches or 10.7.1 should be tried. – Vince Oct 30 at 11:08

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.