topic Re: There is an error during the draw <layer name>(1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name '<DB name>,(2.) <service Account>,(3.) <view/table>',(4.) ]. in ArcGIS Enterprise Questions

There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

TracyArchibald — Wed, 01 Feb 2017 22:53:53 GMT

I've done a search and tried a few things and nothing is working.

This is the error we're getting:

There is an error during the draw <layer name>(1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name '<DB name>,(2.) <service Account>,(3.) <view/table>',(4.) ].

It's ONLY happening on non-SDE databases. They reside on the same SQL server as SDE.

It's ONLY happening after upgrading to 10.4.1 from 10.3.1. Prior to the upgrade, it all worked fine.

We can view the data just fine in ArcMap, but once the service is published, we get errors in the Service when we try to load it and nothing draws.

What we have checked:

We can replicate it on other servers, so it's clearly not a one off.

MSSQL DB version is 2014 Enterprise edition.

Up to date DB drivers.

All ArcGIS for Server updates are done.

Service accounts have permissions in the DB.

Data store is registered and validates.

Service doesn't load in browser OR in ArcMap. Which is as I would expect since I suspect the server.

We occasionally get : Failed to rename cache folder.

We have even tried a test server, creating a new service from scratch and publishing as the service account with no luck.

I'm looking for some ideas. What have we missed?

Re: There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

MattNicol — Mon, 27 Feb 2017 14:59:53 GMT

I ran into this in 10.5. Here's how I resolved it:

In ArcMap:

Layer properties > Source tab > Change Query

The query was in the form of "select <columns> from <database>.<schema>.<table>".

I removed the <database> qualifier, and after saving the map document and overwriting the map service, the features correctly displayed in the preview in ArcGIS Server Manager and in my Web application.

So, change the query in ArcMap to this:

select <columns> from <schema>.<table>

Re: There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

TracyArchibald — Wed, 29 Mar 2017 20:35:31 GMT

Matt's solution works!

ESRI support suggested that we either make the service account a system admin (AHAHAHAHA! No) or we use a database user. These solutions work as well

Re: There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

AbraamPankhurst — Wed, 29 Mar 2017 20:52:27 GMT

Hi Tracy and Matt,

Did Esri Support give any explanation as to why this occurs?

Was Esri Support able to reproduce the issue in their testing environment?

Are you currently in the process of having a defect logged for this issue with Esri Support?

Thanks for updating this thread!

Re: There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

TracyArchibald — Wed, 29 Mar 2017 20:58:28 GMT

I talked with ESRI Canada and it sounds like it's been logged with ESRI Corporate.

I'd guess probably the same thing that "fixed" secured printing with web tier credentials...

Re: There is an error during the draw (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name ',(2.) ,(3.) ',(4.) ].

MattNicol — Thu, 30 Mar 2017 12:20:43 GMT

I didn't log this with Esri support, but I'm guessing it's related to the format of the query that the ODBC driver is expecting.

In SQL Server Management Studio, for instance, you can issue the query in the form that's set in ArcMap by default, and it will run successfully. (SELECT * FROM <database>.<schema>.<table>) So I would think that either ArcMap is transforming the query somehow before sending it or the ODBC driver is somehow mishandling the query in such a way that removing the database qualifier fixes it.