Enabling SQL access on geodatabase data from ArcGIS for Desktop

This topic applies to ArcGIS for Desktop Standard and ArcGIS for Desktop Advanced only.

If your data is versioned, or is not versioned but is enabled for archiving, you must use a view to access it with SQL.

Versioned views are automatically created for datasets that are registered as versioned using ArcGIS 10.1 for Desktop or a later release. Views also are automatically created when you enable archiving on nonversioned datasets beginning with ArcGIS 10.2 for Desktop.

However, there are situations when a view will not exist. These are as follows:

• You have tables or feature classes that were registered as versioned prior to ArcGIS 10.1 and did not manually create a versioned view using the sdetable –o create_mv_view command.
• You registered a table or feature class as versioned using ArcGIS 10.1 or a later release, but you did not have the privileges necessary to create a view in the database.
• You enabled archiving on a nonversioned table or feature class, but you did not have the privileges necessary to create a view in the database.
• The view was deleted mistakenly using SQL or database management system tools.

For these situations, you can enable SQL access on the datasets from ArcGIS for Desktop. This will create the type of view needed for you to access the table or feature class using SQL.

Steps:

1. In the Catalog tree in ArcMap or ArcCatalog, connect to the geodatabase that contains the data for which you want to create a view for SQL access.

   Be sure to connect as the owner of the data; only the owner can enable SQL access. Also be sure you have been granted the database privileges required to create a view.

2. Right-click the table, feature class, or feature dataset; point to Manage and click Enable SQL Access.

   A view is created in the database. If you enable SQL access on a feature dataset, one view is created for each feature class in the feature dataset.

   Note:

   You cannot enable SQL access on versioned datasets or nonversioned datasets that are enabled for archiving in DB2 on z/OS databases.