topic Re: Connecting to SDE Versions in Excel in Data Management Questions

Connecting to SDE Versions in Excel

JeremyLinley — Tue, 30 Oct 2012 14:18:14 GMT

I have a .SDE database (Version 10.1) through SQL Server that utilizes versioning. I also have a cost estimate sheet in excel that contains formulas that need to use attribute data from some of the versioned feature classes. Essentially, I need to be able to connect to the .SDE database and its versions in excel so that the cost estimate sheet can be automatically updated whenever there are adds/deletes/edits made to the feature classes.

I don't need to bring anything from excel into ArcMap; I just need to bring attribute information into excel.

Re: Connecting to SDE Versions in Excel

JakeSkinner — Tue, 30 Oct 2012 15:21:02 GMT

Hi Trista,

First, create a versioned view of the feature class you need to add to excel. Versioned views allow you to read or edit versioned data in a geodatabase table or feature class. Here is some more information:

http://resources.arcgis.com/en/help/main/10.1/index.html#//006z0000000q000000

http://resources.arcgis.com/en/help/main/10.1/index.html#//006z000000vr000000

After you create the versioned view you can add this to Excel. Here is a helpful link I found:

http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

Re: Connecting to SDE Versions in Excel

JeremyLinley — Tue, 30 Oct 2012 16:40:32 GMT

I have created versioned view, however it only accesses the default version data

Re: Connecting to SDE Versions in Excel

JakeSkinner — Tue, 30 Oct 2012 16:44:37 GMT

Make sure you are connected to the version you want to create the view for. You can change the version by right-clicking on the geodatabase > Geodatabase Connection Properties.

Re: Connecting to SDE Versions in Excel

JakeSkinner — Sun, 12 Dec 2021 02:25:17 GMT

Hi Trista,

Sorry, I was incorrect before. You will actually have to set the version in SQL Server:

http://resources.arcgis.com/en/help/main/10.1/index.html#/in_SQL_Server/006z0000000z000000/

What you could do is set the version, then write the results of your query to a new table. Ex:

EXEC sde.set_current_version 'edit_1'

select *
into Airports_VW_Edit
from Airports_VW

What the above codes does is sets the geodatabase to the version ("edit_1") I want to query. Then it queries the versioned view ("Airports_VW") and writes the query to a new table ("Airports_VW_Edit"). You can then add the "Airports_VW_Edit" to Excel.

Re: Connecting to SDE Versions in Excel

JeremyLinley — Wed, 31 Oct 2012 17:45:44 GMT

I got that to work however I still have a few issues/questions.

Would it be beneficial to use multiversioned views? What is the difference?

I have tried repeatedly to create multiversioned views via the sde command but keep getting an error saying failure to access the DBMS server <-409> and could not create a connection on server for user (see attached)

Also, from my understanding using versioned views/multiversioned views and accessing them makes the table static/state is cached and therefore if any changes are made you can't see them because the state changes.

I just want to be able to connect to the versions in excel and have my excel spreadsheet update automatically every time any changes or additions are made to my feature classes.

Re: Connecting to SDE Versions in Excel

JeremyLinley — Wed, 31 Oct 2012 19:21:05 GMT

Okay I got the connection figured out but now it is saying Error: DBMS view exists <-238> but I deleted the view before I tried to run the create mv view command

Re: Connecting to SDE Versions in Excel

JakeSkinner — Sun, 12 Dec 2021 02:25:19 GMT

Hi Trista,

A multiversioned view is the same as a versioned view. See the 'Legacy' note here.

A versioned view is dynamic, however the table you created using the query I previously sent is static. I tested to see if I could create a trigger on the A & D tables so that when the feature class is updated, the query:

EXEC sde.set_current_version 'edit_1'

select *
into Airports_VW_Edit
from Airports_VW

is executed again. This would make the 'Airports_VW_Edit' table, in this example, dynamic. It worked for the most part but will not show the last edit. I'll let you know if I am able to determine what is causing this.

Re: Connecting to SDE Versions in Excel

JeremyLinley — Wed, 31 Oct 2012 20:51:13 GMT

Also, is there any way to run the code you sent me for an entire dataset rather than by each feature class individually?