What does 'DBMS table not found' message mean?

Question

I have a plain postgressql table (version 8.2). I am trying to connect to it to it from ArcGIS desktop 10.0, and use as a query layer, as given in this document: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Connecting_to_a_database/00s500000037000000/

I can see the tables, but I get the error message 'DBMS table not found' enter image description here

What exactly is the issue? How do I connect to it?

• Mapperz: DBMS table not found (-37) help.arcgis.com/EN/arcgisdesktop/10.0/help/index.html#//… though the it can be the esri provided st_geometry.dll no being correct forums.arcgis.com/threads/…
• Sunil: Which geometry you are using (ST_Geometry or PostGIS) ? Are you able to connect it form Arc-catalog ?
• Devdatta Tengshe: This is a Plain Postgres Table. There is no PostGIS/ST_Geometry or SDE involved.
• til_b: if i recall correctly postgres table names are case sensitive, maybe thats the problem?

Answer 1

Here is the solution that I found. My Table's names contained capital letters. Once I used a table with the name in all small letters, I got no error message, and could use the Query layer.

Aside

How did if find out this is the cause, you ask? I was searching for Postgresql and Query layers, and I found this post on the ArcGIS Forums, which says that ArcGIS has problems connecting to Databases with capitals in their name.

My database did not have capital letters in the name, but the Tables did have. You can even see that in the "List of Tables Window", ArcGIS list the table with names in all small.

This is probably why it gives the error message saying 'DBMS table not found' since in the query it tries to find a table with names is all small, which does not exist.

• RyanKDalton: Ah, yes, I forgot about case sensitivity. See page 43 of the presentation Administering your PostgreSQL Geodatabase from the Esri 2012 User Conference. The presenter said "Please store everything in lowercase", for just this reason.
• RyanKDalton: You may also find this question to be useful: Postgresql for Qgis and Arcgis - What to do/What not to do
• Maja Ott: Six years later and yesterday I faced the same problem, incredible :( Thanks that you wrote your solution to the problem, it was exactly the issue I faced.

Answer 2

If by plain table, you mean a table without spatial information you don't need a query layer, you can use an OLE connection. You can then use that OLE connection to connect to the table and add it to your map document making it available for joins to your other layers.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//006m0000006p000000.htm

• Devdatta Tengshe: An oleDb connection has several limitations. And I'll ideally like to make joins in the query and use the result.
• tomfumb: @DevdattaTengshe I think you should encapsulate data concerns at the data layer. Use an OLE connection to query a view that handles all your table relations. In a perfect world your GIS shouldn't need to know how your database schema is structured.
• Devdatta Tengshe: @tomfumb: Query Layers in ArcGIS are meant for exactly the user case I am describing. ArcGIS will function as a client for the RDBMS, just like PgAdmin. They are great for one off dynamic queries. In the case when there queries will be used again and again, yes a view will be better.
• Devdatta Tengshe: @tomfumb And In my perfect world, I would be able to use ArcGIS Desktop(or any other software) to work with any data source, be it shapefiles, Rasters, Geodatabases, .xls, geoJson, RDBMS, csv etc in a single software. ArcGIS isn't there yet, but it is getting there.