Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
2  
"-51 was since 2002 so I did not even try to dig more." what does this mean? –  Ragi Yaser Burhum Feb 28 '12 at 21:44
1  
Underlying DBMS error (-51) = An error was returned from the database that was not recognized by ArcSDE or the client application. Check the DBMS logs for more information. –  Mapperz Feb 28 '12 at 22:14
    
"upgrade" really isn't the right term. You need to first install SDE. ArcSDE requires a repository of db tables which are created when you install ArcSDE. "upgrade" applies when you already have an SDE GDB and you want to move from one version to another. –  Derek Swingley Feb 29 '12 at 6:18
    
@RagiYaserBurhum, I mean people were solving similar problems 10 years ago. –  r.pankevicius Feb 29 '12 at 7:09
    
YOu need to install SDE, then run the create schemas sql, to create the system tables, then you need to perform the post install, which populates the SDE schema system tables. It looks like you have done none of this –  Hairy Feb 29 '12 at 8:01
show 2 more comments

2 Answers

Best place to start is probably the help system: Geodatabases in SQL Server

share|improve this answer
    
Thanks, maybe it's right way to start from basic things :) –  r.pankevicius Feb 29 '12 at 7:19
add comment

If it works on the new clean databases, but not with the older customer "test" database, then it could be that the customer database is from an older database version.

There is something about that in the ESRI forums here.

share|improve this answer
    
I tried SET ARITHABORT ON, didn't help though... –  r.pankevicius Feb 6 at 9:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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