SQL Table view is visable in SSMS but not in ArcCatalog

[RachelAlbritton]

I am working in SSMS 2012 with a linked database to create views. I have done this successfully with three tables and have been able to see the results of these views in both SSMS and in ArcCatalog (10.3 - Enterprise). I'm all of the suddent having issues with a fourth view that I'm trying to create. I execute the query in SSMS to create the view, and the query is successful. I can see the data in SSMS, however, the table in ArcCatalog is empty. I'm not sure why this one particular view would be different then the others that I've created. Does anyone have any suggestions?

Here's my query that I used to create the view that I can successfully query in SSMS but can not see in ArcCatalog:

CREATE VIEW dbo.fiber_conduitcapacity_edit_test1  
AS
SELECT CAST(row_number()OVER (ORDER BY CB_ID) AS INT) as ID, cb_id,route, average_fill, max_fill, min_fill, open_conduit, capacity, 
CASE WHEN open_conduit<1 THEN 'no' ELSE 'yes'
END AS 'open'
FROM CAMPDB..INFRASTRUCTURE.CONDUITBANK_CAPACITY_V;

What's really odd is that is I take the CASE statement out, thequery runs successfully and I can see the results in ArcCatalog so I'm assuming the issue centers around the CASE statement, but I'm not 100% sure since I can see the results in SSMS.

Thanks in advance.

[ChristianWells · Accepted solution]

Hi Rachel,

I noticed a few things here that we could try:

1. In your case statement, could you try removing the single quotes around "open"?

1a. Also, you can try replacing the single quotes with double quotes since it is an identifier.

2. If that does not work, can you try using a different name for that column?

Thanks,

Christian

[RachelAlbritton]

Christian -

Thank you for your suggestions. The new column name still remained in quotes (single or double didn't seem to matter but they had to be there), and I had to change the column name.

[ChristianWells]

Thanks for checking Rachel. I am glad renaming allowed for the data to be displayed. It may be due to 'open' being a reserved keyword in SQL Server.

Reserved Keywords (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms189822.aspx

I am going to test on my end as well to verify if its due to the keyword.