Download
1 / 54
540 likes | 700 Views
Using MS Access to View and Modify the Contents of SDE for SDE Administrators. Geospatial 09, Snowbird, UT April 27 – May 1, 2009 John Courtright, GIS Analyst, BLM Idaho. The SDE Administrator is responsible for the loading and maintenance of the data into SDE.
E N D
Using MS Access to View and Modify the Contents of SDE for SDE Administrators Geospatial 09, Snowbird, UT April 27 – May 1, 2009 John Courtright, GIS Analyst, BLM Idaho Geospatial 09, Snowbird UT, 2009
The SDE Administrator is responsible for the loading and maintenance of the data into SDE. The SDE Administrator needs to be able to view the contents of the numerous tables in SDE. This requires that they have software that can connect to, display, and view the tables in the SDE database. Traditionally this was accomplished through the use of the command line driven ArcSDE Administration Commands. This was a rather daunting process due to the difficulty of correctly implementing the commands. A more simplified process is needed. Geospatial 09, Snowbird UT, 2009
The purpose of this presentation is to demonstrate the use MS Access and SAS DataFlux to assist in the manipulation of an MS SQL Server SDE database. The target audience is the SDE Administrator who needs to know how to examine the contents of the SDE tables to find and resolve problems. The user needs to be very careful in the use of ODBC connections as changes are made to the base tables. Geospatial 09, Snowbird UT, 2009
The SDE Administrator faces many problems in the support of SDE. These include table locks, determining who has a feature class locked, what privileges have been assigned to what feature datasets or feature classes. This presentation will present multiple ways of viewing the SDE database using different techniques. These same techniques can also used to quickly and easily make changes within the SDE tables.
ArcSDE Administration Commands Geospatial 09, Snowbird UT, 2009
The SDE Administration Commands are a series of executable commands that can be loaded on any PC. The directory is then included in the PATH statement so the commands can be run from any directory. Typically the commands can be difficult to figure out however they can be useful in determining the status of SDE, in viewing the contents of SDE tables, and to fix problems. SDE Administration Commands include: SDEMON -o info –I users –i 5151 –s SDETABLE -o describe -t test -i 5151 –D ide1 –u idsd1 –p SDEVERSION -o describe -V default -u idsd1 -i 5151 Geospatial 09, Snowbird UT, 2009
The sdemon –o info command provides information on who is currently on a SDE database. This information is necessary if you need to contact all of the users to get them off of SDE. This command can show all of the current state, layer and table locks as shown in the next screen shot. In both cases the information is incomplete and the SDE Administrator needs to investigate further. Geospatial 09, Snowbird UT, 2009
How can the SDE Administrator Gain Access to Easily View the SDE Database and Tables? Geospatial 09, Snowbird UT, 2009
Creating an ODBC connection for MS Access or SAS DataFlux As in ArcGIS, the user must first create a connection to the database. This connection can be a database authenticated connection or an operating system authenticated connection. This ODBC connection can be used for MS Access as well as SAS Dataflux. Geospatial 09, Snowbird UT, 2009
Establishing a MS Access ODBC Connection to the SDE Database Geospatial 09, Snowbird UT, 2009
Using SAS DataFlux to Connect to SDE to View the Data Tables Geospatial 09, Snowbird UT, 2009
The previous slide showed many tables with different owners and purposes. Not all of the these tables are created for every feature. idsd1. PASTURES… Business table for PASTURES… idsd1.a340 Adds table for PASTURES… idsd1.d340 Deletes table for PASTURES… idsd1.f340 Features, geometry for PASTURES… idsd1.s340 Spatial index for PASTURES… idsd1.i340 Sequencing for PASTURES… sde.xxx SDE tables sys.xxx System tables Only a few of the SDE tables are regularly examined by the SDE Administrator. Geospatial 09, Snowbird UT, 2009
Listing of Most Used SDE Tables information_schema.table_privileges List of table privileges information_schema.schemata List of names known to SDE sde.SDE_table_locks Feature classes with table lock sde.SDE_state_locks Feature classes with state lock sde.SDE_process_information Who is signed on by PID sde.SDE_table_registry Lists IDs for each Feature Geospatial 09, Snowbird UT, 2009
Back to MS Access to View the SDE Data Tables Geospatial 09, Snowbird UT, 2009
Coding for Query Statement …(table name) not like ‘[a-z]#*’ and … Geospatial 09, Snowbird UT, 2009
Adding Complexity and Multiple Tables to a MS Access Query Geospatial 09, Snowbird UT, 2009
Building Reports from a Query Geospatial 09, Snowbird UT, 2009
