Export shapefile to GDB: Database user name and current user schema do not match

[DavidMills]

Hi,

I'm running into the following error while trying to export a new shapefile into an existing geodatabase via ArcCatalog's "Export -> To Geodatabase (single)" function:

[INDENT]
ERROR 999999: Error executing function.
Database user name and current user schema do not match.[MyGeodatabase.MYGEODATABASE_OWNER.my_output_feature_class]
[/INDENT]

This is a geodatabase that was originally created on SQL Server 2005, where I imported the shape layers that currently exist within the DB, and then moved it to SQL Server 2008. When I worked with it on SQL Server 2005 everything worked just fine, but now that's it's on 2008 I am unable to import new shapefiles.

A few notes:

[INDENT]1. I'm using SQL Server authentication with a user named "mygeodatabase_owner".
2. I created the database using the ArcSDE post-install tool and selected the "dbo" schema.
3. Everything continues to work properly on SQL Server 2008 EXCEPT this import functionality. I'm able to connect to the database via ArcCatalog and query the data currently within the GDB from my front-end with no problem.[/INDENT]

I've searched the forums, and it seems that I'm not the first person to run into this error message. (e.g. http://forums.esri.com/Thread.asp?c=158&f=2290&t=296510). There's a requirement discussed in that thread and other related threads that says there must be a DB schema that matches the user name in the specified credentials. That sure seem like an odd requirement to me. The ArcSDE post-install tool that creates the database offers no such option, and my current configuration (which was created by your tools) has worked just fine in the past.

However, I figured I'd give creating such a schema a try anyway. I added a schema to the database called "mygeodatabase_owner" and changed the default schema on the same-named user from "dbo" to "mygeodatabase_owner". Sadly, this was to no avail; I still receive the same error message.

My one other thought was that perhaps something got out of sync during the DB upgrade process between SQL Servers 2005 and 2008. To test this, I created a brand new geodatabase directly on the SQL Server 2008 instance using the ArcSDE post-install tool. However, trying to export shape files into this database also failed with the exact same error message.

Has ESRI tested the shapefile importing functionality against SQL Server 2008? It seems like the import tool is unable to properly handle the "dbo" schema that the ArcSDE post-install creates. Does ESRI or anyone else have any suggestions on how I may resolve this problem and import a shapefile into a SQL Server 2008 geodatabase?

Thanks,
Dave

[KimPeter]

Dave,

You mentioned you are able to connect to the database via ArcCatalog and query the data currently within the GDB...with no problem, but are you able to do any other "create data" type actions while logged in as the same user?  Can you create a new, empty feature class? (Right-click the geodatabase, point to new, click Feature Class)

Is the mygeodatabase_owner sysadmin in the SQL Server instance?

I just tested exporting a shapefile to an existing ArcSDE geodatabase in SQL Server 2008 using the method you described and it worked, so I don't think it is the tool...Did you close then reopen ArcCatalog after you created the user's schema in SQL Server (just to be sure Desktop was seeing the new info not cached info)?

-Kim

[DavidMills]

Thanks for the reply, Kim. I've been able to successfully import the new shapefile thanks to your recommendations.

First, I tried creating a new Feature Class in the database, like you suggested, and sure enough I received that same "user name and current user schema do not match" error.

I next re-tried creating a schema with the same name as the user. This time I restarted ArcCatalog to refresh the DB configuration, and it made a difference. With a schema named "mygeodatabase_owner" I am able to successfully create both a new Feature Class and import new shapefiles.

I was wondering, can you shed any light on the reasoning behind same-named user & schema requirement? I've never seen schemas used in this way, and your docs don't provide a clear explanation on why it's set up like this. I'm also confused as to why things don't work on the "dbo" schema now, considering I was able to import my shapefiles on the "dbo" schema in the past using the same versions of ESRI software.

Thanks,
Dave

[KimPeter]

Dave,

There are a couple of reasons for the requirement to have user and schema names the same: 1) When ArcSDE was originally implemented, none of the databases ArcSDE used made a real distinction between user and schema and did not allow you to create schemas that didn't correspond to a user. 2) Even now, only two of the five databases (SQL Server and PostgreSQL) that ArcSDE uses make this distinction.  If you would like, you could post a request to the Geodatabase Ideas site for this extra functionality to be available when your geodatabase is in SQL Server and PostgreSQL.  Log in to the geodatabase resource center (http://resources.arcgis.com/content/geodatabases/10.0/about) and click Ideas. I just did a quick check and didn't see this request on there yet, but I may have missed it.

You should still be able to import shapefiles to and create other data in the dbo schema, though that's not a recommended practice - we usually recommend you store data as a nonadministrative user. Any user who is in the sysadmin instance role in SQL Server is seen by ArcSDE as the dbo user; therefore, that user's data gets stored in the dbo schema.  That was one of the scenarios I tried and it worked for me.  In your case, I believe the reason you weren't able to import to the dbo schema was because your mygeodatabase_owner user wasn't dbo in SQL Server.

-Kim

[DavidMills]

Thanks Kim. I really appreciate the assistance.

For the record, the "mygeodatabase_owner" user definitely had "db_owner" permissions assigned, and was set to use "dbo" as it's default schema, but for some reason that setup doesn't work. For my purposes it really doesn't matter what schema is used, as long as I can get the shapefile imported into the DB.

As far as the "Ideas" page, my one suggestion would be for any tool that creates a geodatabase, such as the ArcSDE post-install tool, to set up everything that is required for a GDB, including a schema with the same name as the user, since that seems to be necessary.

Thanks again 🙂

Regards,
Dave

[KimPeter]

One last thing - db_owner (a database role) is not the same as dbo.  The similarity in names can be confusing and took me forever to get used to.  If the user is db_owner, it still has to have a corresponding schema with the same name as the user name.