topic Re: Enable SQL Access - Versioned Views in Geodatabase Questions

Enable SQL Access - Versioned Views

Marco_AlejandroBonilla — Thu, 19 Feb 2015 14:31:22 GMT

Hello,

To give a little context on the actual situation;

- We have an enterprise GDB in which changes are made along daytime in large quanitities.

- The GDB is registered as versioned.

- The compress process is made two times in the day at 12h and 0h.

- In a parallel process, another application intends to give a report in real-time using data from an alphanumeric database.

- This application needs to retrieve the spatial data directly from SQL tables.

- Due to some active connections, the compress level never reaches level zero, so the tables in SQL don't reflect all changes made in ArcGIS.

So, my issues are the following:

- The "Enable SQL access" option in ArcCatalog is always disabled (The connection to the GDB is made with a sysadmin user in SQL)

- Is there a way to reach level zero in the compress?

Re: Enable SQL Access - Versioned Views

JoshuaBixby — Thu, 19 Feb 2015 14:42:42 GMT

Starting with ArcGIS 10.1, versioned views are automatically created for tables or feature classes that are registered as versioned. Are you sure the "Enable SQL Access" isn't greyed out because a versioned view already exists?

Re: Enable SQL Access - Versioned Views

JakeSkinner — Thu, 19 Feb 2015 14:44:04 GMT

Hi Marco,

In order to compress to a state of 0, you will need to make sure no users are connected. So, you will need to disconnect all active users, and then perform the compress. Take a look at the following link:

ArcGIS Help 10.1

Re: Enable SQL Access - Versioned Views

Marco_AlejandroBonilla — Thu, 19 Feb 2015 14:58:33 GMT

Joshua,

Inside SQL there are not such thing as versioned views.

I can see a&d tables, also some "i" tables and the tables corresponding to the feature classes but nothing as "evw" tables. Is there another way to reach them?

Tks

Re: Enable SQL Access - Versioned Views

Marco_AlejandroBonilla — Thu, 19 Feb 2015 15:00:43 GMT

I've tried so, recently ESRI people told us to stop the "ArcSOC" process in our server in order to kill those "ghost" connections, I'll try to do so and I'll post it.

Re: Enable SQL Access - Versioned Views

JoshuaBixby — Thu, 19 Feb 2015 15:09:32 GMT

What DBMS are you using?

Re: Enable SQL Access - Versioned Views

Marco_AlejandroBonilla — Thu, 19 Feb 2015 15:22:48 GMT

SQL Server 2012

Re: Enable SQL Access - Versioned Views

JakeSkinner — Thu, 19 Feb 2015 15:40:59 GMT

Below is a link to a script that shows how to start/stop all services. Services accessing your SDE data will need to be stopped to disconnect the user connection. This may be helpful if you script your compress operation.

ArcGIS Help 10.1

Re: Enable SQL Access - Versioned Views

JoshuaBixby — Thu, 19 Feb 2015 17:13:37 GMT

How are you "seeing" the geodatabases tables? Are you logging into SQL Server using SQL Server Management Studio (SSMS) or SQLCMD? If the former, are there no views showing up under the Views folder/tab?

Originally with 10.1, versioned views ended with "_VW" but Esri changed that in 10.2 to end in "_EVW".

Re: Enable SQL Access - Versioned Views

MarcoBoeringa — Thu, 19 Feb 2015 18:11:47 GMT

Marco Alejandro Bonilla schreef:
Joshua,
Inside SQL there are not such thing as versioned views.
I can see a&d tables, also some "i" tables and the tables corresponding to the feature classes but nothing as "evw" tables. Is there another way to reach them?
Tks

They aren't listed under tables, they are database views! You should look under Views in SQL Server Management Studio to find them...

Instead of fruitlessly trying to compress to state 0, you may also consider connecting to a specific version using versioned views. This may give a more "recent" picture, although it won't resolve all your issues, as it will not show edits going on in any other specific versions.

You can find more information in this particular Help topic, especially see the remark about the set_current_version ability of versioned views:

What is a versioned view?

Re: Enable SQL Access - Versioned Views

Marco_AlejandroBonilla — Thu, 19 Feb 2015 19:35:59 GMT

I'm so embarrased!!!

Sorry I made you lose your time!! It seems that I was so into this that I never saw the "Views" folder

Thamk you so much and sorry again

Re: Enable SQL Access - Versioned Views

MarcoBoeringa — Thu, 19 Feb 2015 19:44:27 GMT

Nothing to be embarrased about or to apologize for. We have all been there trying to make sense of the Geodatabase concept and all the documentation.

Glad to have been of help, and certainly check out that Help page I linked as well!