All points hide on a map

MarkLittell

I am using ArcGIS 10.5 Desktop and Server pulling a Query Layer from a MS SQL View.

When I move the map where a point falls of the current map extent, hit refresh, all the points remove from the map.

It does this in ArcMap and as a published service.

Thoughts?

Thanks 

Mark

MarkLittell

Problem solved... let me expand on my issue and the resolution.

Issue:

I had two services going to two different MS SQL views.

View 1 only displayed icons on the map if all the icons were in the current map extent.

View 2 displayed icons on the map regardless if the icons were in the current map extent or not.

When I grabbed the View, I was casting the coordinates to numeric type. This process has worked fine over the past 3 years. This morning, I had the problem stated above.

Things Discovered:

In ArcMAP, I could do an Identify on the layer after they disappeared from the map - no identify results would appear - but an 'Error' did appear on the Identify window. I was able to mouse over the Error and see a popup explaining it was having issues with converting the varchar type to numeric.

View 1 coordinates came to me, from MS SQL as varchar type.

View 2 coordinates came to me, from MS SQL as int type.

In ArcMAP, looking at the fields in the layer properties you can see the Appearance \ Number Format is set to Numeric for both Views. But the Field Details \ Data Type was set to Float for View 1 and Double in View 2.

Resolution:

Perform a second CAST on View 1 to change the coordinates from varchar type to int type, then cast as numeric.

Like so:

CAST(CAST(Latitude AS int) AS numeric) /1000000 as latitude,  CAST(CAST(Longitude AS int) AS numeric) /-1000000 as longitude

The math part is to add the decimal and negative characters back into the values. The varchar and int type removes the decimal and negative characters.