1

I could not find anything on this topic already discussed. We converted our file-based system in ESRI to an SDE instance on Sql Server. We made changes in our code to open using the connection string and we change ArcMap to use the SDE as the default database.

We cannot open our GP services to republish them which is an indicator that ArcMap cannot find the schemas mentioned in the GP service ParameterInfo. When I look at the database through ConfigurationManager the names have been changed from {schemaname} to {dbname}.DBO.{schemname} and ESRI does not let me change the names back.

So do we have to alter every program to use the new schema name? And are we locked into a given database name? (If our client published to a different named server I can see us losing our ability to find the schemas once again)

Personally I think it is shortsighted to put the server name into the name of all of the fields in the ESRI database. Surely there is a way around this???

2

Welcome to the world of enterprise databases.

As Vince already mentioned, the Database name is required because you can have multiple databases in a single server. I have 14 different SDE databases running on one box and 10 of them have the exact same schema.table names. Only the database name makes them unique.

SQL Server is the one that has forced the [schema].[table] structure you see. If you look at any table in SQL Server you will see this naming convention. In your case the schema name is DBO (Database Owner) because the user who loaded the data was assigned the dbo schema when the login was created (likely by default). If you assign a different schema to that login prior to loading the data you can control this. Once the data is in SDE you are stuck with it unless you want to redo your upload or copy the data with a different user schema.

When every user is created in SQL Server a schema is assigned for that user. Some IT shops reuse a single schema and others create a new schema for each user. With straight SQL calls, if the user has the same schema as the table owner they do not need to include the schema prefix in the SQL statements (a shortcut but considered bad form by some).

SELECT fieldname FROM table

If the schemas are different then the schema name must be used.

SELECT fieldname FROM schema.table

All ESRI is doing is keeping the proper form for this data structure as defined by Microsoft. All you can do in ArcCatalog once the data is loaded is rename the table portion of the name unless you want to reload the data or copy it in to a new schema with a user that has been assigned a different schema.

Do not attempt to make such changes directly in SQL as you will most likely corrupt some of the geodatabase tables and have to reload everything from scratch.

| improve this answer | |
  • Thanks. I grasp the issue now and we found that Alias can point us back at the original name without tying our code to server names. – SASS_Shooter May 30 '14 at 20:18
1

All objects in a database need an owner -- that's how they work. Most RDBMSes partition data by database, and since they support multiple databases, the only unique reference to a table is by "database.owner.table". Server names are not directly part of table references (the connection property handles that), but if you change the database naming between connections you'll need to update the sources, much the same as if you had renamed a directory with a file-based data source.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.