Creating a geodatabase on a Multiple instance SQL Server

[PeterBirksmith]

Hi All,

Some background

Esri Server

Windows 2012 R2 Standard SP1

ArcMap 10.3.0.4322

SQL Server Native Client 2008 R2 10.50.4000.0 (SP2)

SQL Server Database

Windows 2008 R2 Standard SP2

SQL Server 2008 R2 10.50.4000.0 (SP2)

Multiple instance including

DEVELOPMENT

UAT

PRODUCTION

E.g. <server>\DEVELOPMENT or <server>,port to connect

I've attempted to create a geodatabase on this multi instance SQL Server and failed,

I spun up a single instance SQL server on a VM and can successfully create a geodatabase.

The error I get is as follows when it attempts to create the GDB_ITEM table at the definition column which is the first XML datatype.

[Tue Apr 14 09:30:41 2015] Successfully created GDB_Tables_Last_Modified table...

[Tue Apr 14 09:30:41 2015] Error creating  GDB_Items table...

[Tue Apr 14 09:30:41 2015] ERROR Creating Geodatabase tables

, Error = -511

,EXT_Error = 24000

,EXT_ERROR1 = [Microsoft][SQL Server Native Client 10.0]Invalid cursor state

,EXT_ERROR2 =

Has anyone else come across this issue?  Did you manage to get passed the error?

Any tips, tricks, that I can use to dig deeper into this issue?

Cheers

Peter

[EmadAl-Mousa]

did you check your local ArcGIS Deskto logs for more information ?

also, are you sure "sde" account has the right permissions ?

take the "Sql definition" of the GDB_Items table from a different geodatabase and try to create the table manually using sde account, what error are you going to receive ?

[PeterBirksmith]

He Emad

I'm actually creating the database via ArcMap

In the search, you type in create enterprise and this returns the wizard that you can use to create the database on the server

The SDE account is created via this process and assigned the appropriate permissions.

If I create the GDB_Items table using the script, the table is created successfully, although if I then try to build the database around it, Esri assume the entire database exists and stops.

I have a support call open and Esri have indicated that they can't replicate the conditions I'm experiencing.

I ran a trace using SQL Profile while building the database via this wizard.

My next step in this process is to concentrate on the error

EXT_ERROR1 = [Microsoft][SQL Server Native Client 10.0]Invalid cursor state

I will try to determine causation on occurrence of error.

Thanks for the suggestions.

Cheers

Peter

[PeterBirksmith]

Hi all,

For those of you that have viewed this thread.  I've now agreed to close the call with ESRI as it appears to have been an issue with the upgrade from 2003 to 2008 SQL Server and the method used by the DBA to migrate content for these instances.

On one sql server we had two instances but decommissioned all databases prior to the upgrade as we separated DEV and PROD on the same server.

DEV instance was restored but the PROD instance was cleared.

The problem instance when creating a geodatabase happens to be DEV.  When I then tested against the PROD instance that had been cleared prior to the upgrade, I was not able to replicate the invalid cursor error.

Unfortunately this same scenario has also been seen in the new PROD sql Server.

PROD has the issue where as UAT doesn't.

We'll be upgrading to SQL Server 2014 shortly and will employ a different method of migrating content to hopefully stop the error.

Cheers

Peter