topic Re: MS SQL Server and Missing SDE 10.1 Base Table in Data Management Questions

MS SQL Server and Missing SDE 10.1 Base Table

JeffCronce — Thu, 14 Feb 2013 18:09:27 GMT

Hello,

Is SDE 10.1 for MS SQL Server storing Base Table information differently? I've created a GIS Feature Class that I'm able to view in ArcGIS 10.1. When I go within MS SQL Server Management Studio, the base table is not present. If I go to MS Access to link a table, I can see the feature class table but I receive a "Reserved error -7747; there is no message for this error." when I try to link (ODBC). I'm confused as to why MS Access can see the feature class table and MS SQL Server Management Studio cannot.

Thanks for any insight and tips.

Re: MS SQL Server and Missing SDE 10.1 Base Table

AZendel — Wed, 11 Feb 2015 16:16:42 GMT

I have a point feature class in a SQL Express ("Personal ArcSDE") geodatabase. I also get the same error when I attempt to create an ODBC linked table to said feature class from within Access . I'm able to link to all non-spatial tables, even those that are registered with the geodatabase from within the ESRI realm. The two point feature classes in my database have both a clustered index and a spatial index. I suspect that one of those indexes is breaking the ODBC connection. Unlike you, I am able to see the feature class base tables in SSMS.

My work around was to create a view of the feature class in SSMS and then create the ODBC link to the view and not the table. This may not be possibly in your situation since you can't even see the base table in SSMS. However, I first tried creating the view using something to effect of:

CREATE VIEW [dbo].[vw_MyView]

SELECT dbo.MyFeatureClassBaseTable.*

FROM dbo.MyFeatureClassBaseTable

I was able to create the ODBC link from Access to this view. But when I attempted to edit a cell's value in Access, I was getting some sort of error. I deleted the view and the link and then created a new view that lists all of the columns that I need, such as

CREATE VIEW [dbo].[vw_MyView]

SELECT dbo.Col1, Col2, Col3

FROM dbo.MyFeatureClassBaseTable

Now I can edit cell values in Access.

ESRI constantly warns users about monkeying with geodatabase tables outside of the ArcGIS environment. I agree that it comes with risks of corruption. But at the same time, SQL is often a much, much more efficient way of managing data. I've been using Access and SSMA to manipulate geodatabase data for over a decade. I have never encountered a problem using Access or SQL as long as:

-- you don't monkey with any versioned tables or feature classes in any way

-- you don't delete features in a feature class (sometimes the spatial indexes are in separate tables and the corresponding row would also need to be deleted)

-- you don't add a row to a feature class (for the same reason as above)

-- you don't add a row to a non-spatial table that is registered with the geodb and therefore has an OBJECTID field that ArcGIS libraries should manage

-- you don't delete any tables or FCs that are registered with the geodatabase

Re: MS SQL Server and Missing SDE 10.1 Base Table

JoshuaBixby — Thu, 12 Feb 2015 15:21:12 GMT

Providing a bit more information would be helpful. You mention ArcSDE 10.1, have you applied any Service Packs or patches? What edition of ArcSDE (Personal, Workgroup, Enterprise)? What version and edition of SQL Server are you using? What version of MS Access are you using? What driver(s) and version(s) have you tried?

Re: MS SQL Server and Missing SDE 10.1 Base Table

DaleBridgford — Mon, 04 Feb 2019 14:50:07 GMT

I had experienced the same issue. What I had found was that when I created the view, I had to exclude the Shape field, then I was able to add in and edit the view. Note that this was not a versioned feature layer.

Dale