Upgraded 9.3.1 Geodatabase to 10.2.1 (ArcCatalog) unable to create query layers

[JasonReid]

I've confirmed this with two geodatabases so far.

Steps.
Create backup of 9.3.1 database from SQL 2008 R2
Restore Backup of 9.3.1 into new SQL 2012 database
Connect to new database in ArcCatalog and upgrade the geodatabase

When I view the features in ArcCatalog I can see the Shape field is saying it's a geometry field, when I look at the table itself in SQL the Shape field is still an INT field. I am unable to create any Query layers and have them act like a Query Layer using any of these features.

If I create a new geodatabase, and copy the features into it, the field does convert to geometry so it's not the data. the Upgrade Geodatabase button apparently is not actually upgrading anything. Is this a known issue, or something special I ran into?

[AsrujitSengupta]

Jason,

The default storage type changed to GEOMETRY starting from 10.1, so you may not be observing the same behaviour in the new geodatabase.

I assume, that the storage type being used in your 9.3.1 sde gdb was SDEBinary. Even when you upgraded the sde gdb to 10.2.1, it remained as SDEBinary(as expected and as designed).

[VinceAngelo]

If you want to use Query Layers, the geometry storage type must be native (or ST_GEOMETRY,
where available).  The geometry storage is not changed by an upgrade, but it can be changed
by using the Migrate Storage tool (in Data Management Tools -> Geodatabase Administration)
or the 'sdelayer -o migrate' utility.

- V

[JasonReid]

Thanks when I looked into it some more sure enough it's still SDEBinary type.

When I try to run the Migrate Storage geoprocessing tool I am getting an error that the user must be the table owner.

The connection I used was the owner of the tables, and the schema. Any idea why this would happen? That user has db_owner rights the database and also sysadmin on the server.

[VinceAngelo]

Microsoft takes the "dbo" username over the local username if the user is a database owner
(or a member of the Admninstrators group).  I avoid granting dbo to anyone to avoid this
sort of issue.

- V

[AsrujitSengupta]

what is the complete name of the owner of that data?

example: dbname.owner.object_name

[JasonReid]

The complete name of of of the tables is:

databasename.gdbo.tablename

gdbo is the schema and owner of the the features. It's an SDE geodatabase where SDE ownes the SDE objects and the user GDBO owns the features in their own schema.

[AsrujitSengupta]

So as Vince mentioned, the error is occurring as you have granted the login "gdbo"  sysadmin privilege.

Remove sysadmin role for that user and then try to migrate the storage.