Tip: How to Attach an ArcSDE Personal Database File After Moving It

by James Richards April 13, 2011

I recently needed to move an ArcSDE Personal database file from my local hard drive to another location. The directory where the MDF and LDF files are located is different on the target computer. This caused problems when attempting to attach the database in ArcCatalog. I received the following error message:

Problem selecting this Geodatabase file
File activation failure. The physical file name
"C:\GIS Data\MyDatabase.LDF" may be incorrect.

It seems that ArcCatalog does not figure out that log file has moved along side the MDF file. To solve the problem, I attached the database using SQL Server Management Studio, which automatically fixes the path to the LDF file.

But this created a second problem. Now that the database is attached to the local SQLEXPRESS instance with Management Studio, attempting to attach it in ArcCatalog causes this error:

Problem selecting this Geodatabase file
CREATE FILE encountered operating system error 32(The process cannot
access the file because it is already being used by another process.) while
attempting to open or create the physical file 'D:\GIS
Data\MyDatabase.mdf'.

This was easily solved by detaching the database in SQL Server Management Studio and then attaching it in ArcCatalog.

Summary

To move an ArcSDE Personal database to a new location with a different directory path, first attach the MDF file to SQLEXPRESS using SQL Server Management Studio. This will fix the broken path to the log file. Then detatch the database and attach it in ArcCatalog.

Hope this helps!

Tags: , ,

ArcGIS Server | ArcSDE | How To

How To Create an ArcSDE Spatial View With an Outer Join

by James Richards July 16, 2009

Overview

ArcSDE Spatial Views are a useful tool for organizing information from multiple feature classes and geodatabase tables into a single “virtual feature class” or table at the database level. Similar to a database view, they allow administrators to join commonly used information so that the users do not need to perform the same actions repeatedly in a client (in this case, ArcMap).

Spatial Views are created with the “sdetable –o create_view …” command. When creating a Spatial View in this manner, the default join type is INNER JOIN. This fact cannot be altered via the command line syntax. INNER JOIN is the most restrictive join type and only records that match on both sides of the join will be in the resulting view. But once a default INNER JOIN Spatial View has been created, the join type can be changed after the fact either with the ALTER VIEW SQL statement, or through a database administration tool.

This article demonstrates how to create a Spatial View and then change the join type using SQL Server Management Studio. The same principles can be applied to other databases using whatever management tool you have available. [more]

Discussion

Step 1: Create the Spatial View

To create the Spatial View, use the sdetable –o create_view command. The syntax for this command is:

sdetable -o create_view -T <view_name> -t <table1,table2...tablen>
-c <table_col1,table_col2...table_coln>
[-a <view_col1,view_col2...view_coln>] [-w <"where_clause">]
[-i <service>] [-s <server_name>] [-D <database>]
-u <DB_User_name> [-p <DB_User_password>] [-N] [-q]

The important parameters that we will be working with in this example are:

  • -T : The name of the view to create
  • -t : The list of tables to include in the view
  • -c : The list of columns to include in the view
  • -a : The list of field alias as they will appear in the view
  • -w : A where clause to define the view
  • -u : The name of a user with ArcSDE administration privileges
  • -p : The password for the aforementioned user

Here’s an example that joins a geodatabase table to a feature class on a common ID field called Join_ID:

sdetable –o create_view –T MyView –t “MyLayer, MyTable”
-c “MyLayer.OBJECTID, MyLayer.Shape, MyLayer.Join_ID, MyTable.Join_ID, MyTable.Field1”
-a “OBJECTID, Shape, Layer_Join_ID, Table_Join_ID, Table_Field1”
-w “MyLayer.Join_ID = MyTable.Join_ID”
-u ****** –p ******

One thing that’s a little odd about this syntax is that the join information is given in the WHERE clause. In a typical SQL CREATE VIEW statement, the join information would be found in the FROM clause.

Also note that if you want the resulting view to be a virtual feature class that can be used with ArcGIS Desktop, you will need to include the OBJECTID and Shape fields from the original feature class in the list of columns to include.

For reference, the sample data in MyLayer and MyTable looks like so:

image

Notice that there is no record with a Join_ID of 5 in MyTable, and no record with a Join_ID of 7 in MyLayer.

Step 2: Modify the View Definition in SQL Server Management Studio

As was mentioned in the overview, the default behavior of “sdetable –o create_view” creates a view with an INNER JOIN. If we add the newly created view to ArcMap, only the 5 matching records are shown in the view:

image

To modify the view definition so that it uses an outer join, open SQL Server Management Studio and drill down to the Views folder under the sde database. Next, right click on the view you just created and choose Modify.

 image

Change the phrase “INNER JOIN” in the FROM clause to “LEFT OUTER JOIN” and save the changes.

image

Now if we look at the View in ArcMap again, we will see all records from MyLayer, with null values for any records with a Join_ID in MyLayer that was unmatched in MyTable:

image

You can use other join styles such as FULL OUTER JOIN, but keep in mind that if the primary OBJECTID (or Row ID) column contains multiple null values, client behavior will become unpredictable.

Wrap Up

When creating ArcSDE Spatial Views using the “sdetable –o create_view” command, the default join type is the most restrictive INNER JOIN. But you can change the view definition to use other join types using the ALTER VIEW SQL command or your database’s management tool. The article showed an example of how to make such a change with SQL Server Management Studio.

I hope you find this helpful!

Resources

 

Tags: ,

ArcSDE | How To | Planet GS

Powered by BlogEngine.NET 1.6.0.0
Theme by Mads Kristensen | Modified by Mooglegiant
Creative Commons License This work is licensed under a Creative Commons Attribution 3.0 United States License.

Welcome

James Richards

Hi, I'm James Richards the CTO and co-founder of Artisan Global LLC. We make location-aware mobile apps with maps. I'm the author of QuakeFeed and I helped launch Zaarly at LASW Feb 2011. I also enjoy surfing, snowboarding, golfing, yoga, and music. I love my family: Linda, Sequoya and our cats Remy and Twiggy. Thanks for stopping by, I hope you find something helpful here.

Subscribe by RSS   Follow me on Twitter   Connect on Facebook   View my profile on LinkedIn


Amazon Associates

Some of my posts may contain Amazon Associates links. I only include these links when it makes sense within the context of the article. If you are in the market for one of these items, please consider clicking on an affiliate link to make your purchase. You still get the same great deal from Amazon and it will help me pay for hosting and bandwidth. Thanks!