I have 1 SQL Server 2008 and 1 Oracle database (did not check which version it is).
I'd like to convert them to geodatabases, first that 'MS SQL Server 2008', then Oracle. So, leave Oracle for a while. My question is about SQL Server.
Database has about 200 tables, each under dbo schema. I used sdeupdate, got:
SDE Error(-51) Underlying DBMS error
-51 was since 2002 so I did not even try to dig more.
Let it be underlying.
So, is it possible at all with ArcGIS 10.0 to take a simple database and upgrade to geodatabase?
c:\somewhere> sdesetup -o upgrade -d sqlserver -s . -u sa -p SA_PASSWORD -D DATABASE_NAME -N -l "arcsdeserver,100,************,31-dec-2012,******************"
ESRI ArcSDE Server Setup Utility Tue Feb 28 17:51:49 2012
----------------------------------------------------------------
Upgrading ArcSDE schema.....
Successfully upgraded ArcSDE schema.
Upgrading geodatabase schema:
Error: Underlying DBMS error (-51).
Error upgrading GDB schema.
Geodatabase schema object install not completed.
Seems, it's gonna take some while. Will prepare batch scripts to restore DBs, install profiler (seems it doesn't come bundled with MSSQLSRV2008)…
[Update]
Here I tried to play with empty databases.
On a clean, just created SQL database, using sde schema :
sdesetup -o install -d sqlserver -s . -u sde ...
OK
On a clean, just created SQL database, using dbo schema :
sdesetup -o install -d sqlserver -s . -u dbo ...
All fine again. Esri systables are named now like dbo.GDB_ITEMRELATIONSHIPS, etc.
On a clean, just created SQL database + new empty table dbo.Table_1, using dbo schema :
sdesetup -o install -d sqlserver -s . -u dbo ...
Fine again.
Now I Connect from ArcCatalog Add Spatial DB connection with:
service string= sde:sqlserver:.
user=sa
version: dbo.DEFAULT
OK – great success!
So it's possible to upgrade existing SQL database to geodatabase.
(Seems someone had fk-uped customer DB version, this I'll need to check)…
EDIT, This part is about -51 error.
Found there were sde and arcims users in DB. This probably was left from older esri software installed long ago. Removed both.
In sde_setup.log I see this:
In sde_setup.log
Successfully created GDB_Tables_Last_Modified table...
Error creating GDB_Items table...
ERROR Creating Geodatabase tables
, Error = -51
,EXT_Error = 1934
,EXT_ERROR1 = Microsoft SQL Server Native Client 10.0: CREATE XML INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
,EXT_ERROR2 =
,
It seems for me it may be that I missed SET BLOBSIZE or something options…
UPDATE:
Repeated all steps and additionally ran this SQL script:
-- SQLServer2008R2_SetRequiredMagicForXML.sql
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
Still that ARITHABORT error. Ok there are only 2 options ON and OFF, so will invert that setting and try again. Just too long and useless error message.
Update:
SET ARITHABORT OFF did not work. Played with empty new database, once set it ON and then OFF. And in both cases result was OK. This means ARITHABORT doesn't matter. That's incorrect error message. Too many if-s in code.
Update:
Replaced our code with strategy OLEDb using best practices in the world.
IMPORTANT: I'm playing with backup of customer's database on my machine. Never try to do anything like this quickly on production server without trying it in sandbox first. Use DB backups.
UPDATE: finally proved myself with your help that it's possible when playing with small databases. So final answer is YES.
Always – NO, sometimes you must fail fast.
Later I will check FAQ how to correctly "close a case" here.
Best Answer
Best place to start is probably the help system: Geodatabases in SQL Server