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 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)
Have you tested this in a newer version of ArcMap 10.8.x or within ArcGIS Pro 2.6.x?
If you think it is a bug, it would be best to work with Esri Technical support to get it documented.
Thanks for the reply.
My enterprise license is available for ArcGIS 10.1-10.5. I haven't been able to test with a newer version.
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'll follow your advice and contact Esri Technical.
If it helps, I'm running Pro 2.6 and am experiencing the same issue (with both the python command line, and the interactive Geoprocessing tool). I tried creating the database view in MS SQL Management Studio but then ArcGIS doesn't seem to recognize the OBJECTID so I haven't yet come up with a workaround. The calculations are such that I can't work around needing the divide symbol. Please let us know what you hear back from technical support!
UPDATE: I tried it in ArcCatalog 10.8.1, using the interactive approach (right mouse click in the database, new: view) and didn't get the error, and I can see the result successfully now in Pro as well. (It still doesn't like my OBJECTID, but that's a problem for another day.)
So it fails in Pro, but succeeds in Map.
I am having the same issue in Pro 2.8.1. In trying to create a database view that uses division, the "/" division operator seems to disappear when used in the view definition box in the gp tool.