Connecting to SDE Versions in Excel

[JeremyLinley]

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.

[JakeSkinner]

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

[JeremyLinley]

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

[JakeSkinner]

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.

[JakeSkinner]

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.

[JeremyLinley]

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.

[JeremyLinley]

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

[JakeSkinner]

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.

[JeremyLinley]

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