topic SQL view not accessible in Catalog in Geodatabase Questions

SQL view not accessible in Catalog

LancasterGIS — Wed, 27 Jun 2018 13:20:07 GMT

At some point in the past I've written a SQL view (pulling data from a linked server) within my main Geodatabase to use in ArcGIS, these function properly. Now I have a need for more information from the same server so I wrote more views, however I cannot access or even preview them Catalog, but I can see them in the catalog tree.

Did I miss something in the process? I've compared the script I used to create each and there isn't anything standing out as different to me. Any insight is greatly appreciated.

Re: SQL view not accessible in Catalog

VinceAngelo — Wed, 27 Jun 2018 13:42:51 GMT

ArcGIS won't preview tables that contain unsupported datatypes. Please provide a more complete description of the view SQL with a complete description of the member tables.

- V

Re: SQL view not accessible in Catalog

LancasterGIS — Wed, 27 Jun 2018 13:46:20 GMT

I'm not sure what you mean by a more complete description. Here is the script I used to create this view:

create view [dbo].[vw_Sanidata] as
SELECT [location_id],
[parcel_num],
[location_class],
[account_num],
[name],
[address],
[service_sanitation],
[rate_code_sanitation],
[rate_code_description_sanitation],
[rate_code_amount_sanitation]
FROM [server].[database].[dbo].[ud_gis_sanitation_data]

Here is the script from the process that created a similar table that works properly:

create view [dbo].[vw_waterdata] as
select [location_id],
[location_class],
[parcel_num],
[address],
[rate_code_water],
[rate_code_description_water],
[rate_code_amount_water],
[service_wellhead],
[rate_code_wellhead],
[rate_code_description_wellhead],
[rate_code_amount_wellhead],
[rate_code_sewer],
[rate_code_description_sewer],
[rate_code_amount_sewer],
[rate_code_fireline],
[rate_code_description_fireline],
[rate_code_amount_fireline]
from [server].[database].[dbo].[ud_gis_water_data]

Does this help?

Re: SQL view not accessible in Catalog

LancasterGIS — Wed, 27 Jun 2018 17:16:29 GMT

Problem solved!!! some of the table names exceeded 31 characters, which prevented ESRI software from reading. The final answer was to add an "as" statement.

[rate_code_description_sanitation] as rate_code_desc_sani,