Publishing Services from direct connection to SQL Server 2014

[Anonymous User]

I have data loaded into a SQL Server 2014. I have validated the connection to the data source in ArcGIS Server 10.4.1.

I can add geographic data to an ArcMap Client and publish to Server.

When trying to view the data from the rest service page the geometry is not displayed and I receive the following error in the Server logs.

There is an error during the draw WISE_Decade_CBAS.DBO.deleteme (1.) DBMS table not found[42S02:[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'WISE_Decade_CBAS,(2.) WISLEG\SA-DEV-ARCGIS,(3.) deleteme',(4.) ].

We do not seem to have this same problem in our 10.3.1 environment. I do see the geometry in ArcMap!

I am also not seeing the same behavior in our SDE based environment.

[Anonymous User]

Yes, the patch has been installed and we are using the same dev instance of SQL.

[ScottFierro2]

So probably a wasted point but didn't notice it anywhere in comments yet. Have you registered the SDE connection with the Server Data Stores?

Was trying to follow the usernames above but we have seen interesting headaches before on new installations. During the initial set-up of the new 10.4.1 SDE in SQL the account that you use IS the owner of the database. However, it's been our experience that this account is best left for that sole purpose. Any remaining accounts are assigned permissions via SQL that are set AS dbo. Trying to avoid getting to far into the weeds but I'd suggest creating a new schema within SQL and assign the Windows account access to the schema as appropriate. This may help better explain the scenario when reading the dbo specific info but essentially its tied to the users default schema along with other SQL side granted permissions https://msdn.microsoft.com/en-us/library/bb669061(v=vs.110).aspx

Last item, the above addresses the issues with being able to ensure the account can properly execute any of the functions within the database (i.e. ESRI stored procedures).

[YOAVRAPPAPORT1]

I also have SQL Views (tables) and I would like to "join" these to features (both poly and points) in a Geodatabase using a unique Identifier, and then publish to ArcGIS Server. The problem is that the SQL Views are updated daily. Is there a way to automate this process and update the REST on a regular basis (one a day for example). This is similar to some posts i've seen by jskinner-esristaff‌ any leads are much appreciated.

[MattNicol]

I just ran into this in 10.5.  I put more detailed instructions here:

https://community.esri.com/thread/189576-there-is-an-error-during-the-draw-layer-name1-dbms-table-no...

To summarize though, remove the database name from the query in your layer in ArcMap.

Layer Properties > Source > Change Query

In my case, the query was:  select <columns> from <database>.<schema>.<table>

Removing the <database> qualifier resolved it.

[ChristopherJohnson1]

Thanks for this post, Matt!