Unable to publish map service with data in enterprise database using Server 10.4

[ThomasPuthusserry]

I have upgraded ArcGIS Server 10.3.1 to 10.4 and facing issues with publishing map services with data stored in enterprise databases.

The server is hosted in Amazon AWS with following infrastructure and works fine:

Windows 2012 R2

Server 10.3.1

PostgreSQL 9.3 (in the same server - localhost) with SDE enabled

PostgreSQL as AWS RDS with SDE enabled

However, when the server upgraded to 10.4, the service with data in enterprise databases (both localhost and AWS RDS) which used to publish are no longer working. The error is

The ArcServer log file shows the following details

Error executing tool. PublishServiceDefinition Job ID: j5a9a52478ffc49d19328e5ceefbb609f : ERROR 001487: Failed to update the published service with the server-side data location. Please see the server's log for more details. ERROR 001369: Failed to create the service. Failed to execute (Publish Service Definition).                System/PublishingTools.GPServer

Failed to create the service.: Updating the server connection string for layer sdedb1.web.%gwstatmaster_staging Events failed. Attempted connection string was ENCRYPTED_PASSWORD=00022e68334e624d44786b57627653553542393435702b3845413d3d2a00;SERVER=xxxxxxx.c11tt3rfoa7s.eu-west-1.rds.amazonaws.com;INSTANCE="DSID=a8960749-e49c-41db-a095-a8a257a693b4";DBCLIENT=postgresql;DB_CONNECTION_PROPERTIES=xxxxxx.c11tt3rfoa7s.eu-west-1.rds.amazonaws.com;DATABASE=sdedb1;USER=web;VERSION=sde.DEFAULT;AUTHENTICATION_MODE=DBMS. Table name is sdedb1.web.%gwstatmaster_staging. Please verify the data exists on the server.                System/PublishingTools.GPServer

If I uninstall 10.4 and reinstall 10.3.1 on the same server, everything back to normal and working fine.

Has anyone else experienced the same issue?

Thanks and any suggestions much appreciated

Thomas

[AstridGladys1]

Hi

Did you try to save your mxd as a new one in order to get a mxd in 10.4 ?

Best,

Astrid

[JonathanQuinn]

Is that query layer?  I'm curious as to why there's a % sign in the table name:

sdedb1.web.%gwstatmaster_staging

[ThomasPuthusserry]

Yes, this is a query / view from PostgreSQL in AWS RDS

[BrianOevermann]

If you drag and drop a database table into ArcMap vs. using the Add Data dialog, the drag and drop process adds a percent sign to the table name. The Add Data dialog process does not.

I found this bug at version 10.2 while using the Flex Viewer. At first I thought it was a Flex widget issue. That was not the case. I opened a support ticket and we eventually found the workaround. Apparently there is a bug that was logged at v. 10.1 regarding this. [#NIM093503] Apparently the bug has not been resolved.

FWIW, ArcMap seems perfectly happy with the percent sign. Publishing a map service--or maybe consuming said published map service--is where the issues creep in.

Bug report:

http://support.esri.com/bugs/nimbus/TklNMDkzNTAz

My initial forum post regarding my issue along with multiple updates:

https://community.esri.com/thread/90592

Doesn't solve the original question but answers the "percent sign in the table name" question...

Brian

[BrianGallegos]

I have come across this publishing issue from time to time for a little while now.  Adding the data through the tool rather than drag and drop worked today!  I'm pretty sure this has been my issue in the past, so nice to know what's been going on finally!

Thanks Brian

[MichaelJenkins]

Thank you, Brian!   This perfectly describes the mystery what we were trying to solve.   A table from a non-spatial DB brought in to ArcMap (drag an drop) and joined to a feature class.   No problem in ArcMap, and no problem publishing to ArcGIS Server, but "incorrect SQL" errors when we try to query that layer if we specify any of the table fields because the table name has a % added to the beginning.

[MarkLittell]

I am experiencing the same issue - has anyone found a solution?

Jonathan Quinn - Yes it is a query layer.

I do not want to re publish 90+ services by going back to 10.3.1.

I am trying to use 10.4.1

Please advise

- Mark

[ChristianWells]

Hi Mark,

There are some defects regarding publishing in 10.4 that we have identified for some layer types. Are you able to open a Esri Support case so we can attach you to the defects as appropriate?

BUG-000096292: When the database view is joined to a definition que..

Thanks,

Christian

[MarkLittell]

ESRI's Alternate Solution does not work either.

I uninstalled ArcMap 10.4.1 and installed 10.3.1, tried to publish the layer, and get the same error.

Current ArcMap is 10.3.1.4959

Current ArcGIS Server is 10.41

I do not want to revert back to server 10.3.1 - I have over 90 services that will need redeploying.

Should I start a new thread?