"does not exist or is not supported" - NIM059178

[BKuiper]

I moved an existing sde geodatabase (MS SQL) from one machine to another (production machine).

I initially had problems accessing the data from the geodatabase on the new machine. I was getting the following error:

"DBMS table not found [*********][STATE_ID=0]"

I traced this back to the following problem: NIM059178

Now i can access the data through ArcCatalog and (pre)view the data, but my Python scripts keep failing when they try to access the same feature class:

Failed to execute. Parameters are not valid.
ERROR 000732: Input Features: Dataset <MyFeature> does not exist or is not supported
Failed to execute (Select).

I think this is related to the same problem, but i installed all the current patches and the problem still exists

ArcGIS Desktop 10
 ArcGIS Desktop 10 Service Pack 1
 ArcGIS Desktop 10 Service Pack 2

ArcGIS Server 10 - GIS Services
 ArcGIS Server for the Microsoft .NET Framework 10 - GIS Services Service Pack 1
 ArcGIS Server for the Microsoft .NET Framework 10 - GIS Services Service Pack 2

ArcGIS Server 10 - Web Applications
 ArcGIS Server for the Microsoft .NET Framework 10 - Web Applications Service Pack 1
 ArcGIS Server for the Microsoft .NET Framework 10 - Web Applications Service Pack 2

ArcGIS Server 10 - Web ADF Runtime
 ArcGIS Server for the Microsoft .NET Framework 10 - Web ADF Runtime Service Pack 1
 ArcGIS Server for the Microsoft .NET Framework 10 - Web ADF Runtime Service Pack 2

ArcObjects SDK for the Microsoft .NET Framework 10.0
 ArcObjects SDK for the Microsoft .NET Framework 10 Service Pack 1
 ArcObjects SDK for the Microsoft .NET Framework 10 Service Pack 2

ArcSDE for Microsoft SQL Server 64 bit 10
 ArcSDE 10.0 General Update Patch for SQL Server
 ArcSDE for Microsoft SQL Server 64 bit 10 Service Pack 1
 ArcSDE for Microsoft SQL Server 64 bit 10 Service Pack 2

Any idea how i can fix this or further debug this without having to re-populate all the data!

EDIT:
Note, i'm using the same .sde connection file in ArcCatalog as in my Python scripts. This works on my Dev machine. The users in the database have full access/rights.

[JakeSkinner]

1.  Did you re-synch all the database authenticated logins after restoring the database onto the new machine:

http://resources.arcgis.com/content/kbase?fa=articleShow&d=29672

2.  Did you restore to a different version of SQL Server (ie 2005 to 2008)?  If so, check to make sure the compatibility level updated:

http://resources.arcgis.com/content/kbase?fa=articleShow&d=32979

[BKuiper]

The original SQL server was running 2008 and the new one is running 2008 R2 but i'm not sure if that is causing the problem.

I discovered that specifying the full name, that is <databasename>.<schema>.<featureclass>, works, but just specifying the featureclass doesn't seem to work on the new machine anymore. This works on my development machine.

Jake: thanks for your feedback. Suggestion #2 wasn't relevant because they are both 2008, Suggestion #1 is only relevant if you are running a SDE service. I found that this is not necessary on Windows with MS SQL.

Perhaps you have an idea what is going wrong with looking up the featureclass?

EDIT: this is accessing the files from python. Even running the python script from ArcDesktop shows this behavior. I'm just executing a simple test were I use "Describe" on a feature class.

[JakeSkinner]

Can you post some of your code that is failing?

[BKuiper]

Here is the test script that shows the behavior. My actual code is a ArcGIS 10 Python script, but showing the same behavior.
                    

try:
        # Import system modules
        import sys, string, os, arcgisscripting

        # Create the Geoprocessor object
        gp = arcgisscripting.create(9.3)
        gp.OverWriteOutput = 1

        workspaceName = r"C:\somedirectory\Connection.sde"

        gp.Workspace = workspaceName

        featureClass = r"geodatabase.USER1.parkBoundary" # <- works
        featureClass = r"parkBoundary" # <- fails

        desc = arcpy.Describe(featureClass)

        gp.addMessage("Finished")     
except:
        gp.addMessage(gp.getmessages(2))
        gp.adderror("Error")

thanks for your quick reply!

[BKuiper]

Looking at SQL Profiler, it seems it's picking up the DBO schema instead of my USER1 schema.

declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output,NULL,N'SELECT 1 FROM geodatabase.DBO.<feature> WHERE 1 = 0',1
select @p1

i'm running on MS SQL 2008 R2 64bits on the production machine. I think i have all the SQL user settings the same as on my development machine.

I'm further investigating. I just wanted to give a heads-up on what i'm doing.

[JakeSkinner]

What user are you connecting to the database as?  Is this user an administrator on the SQL Server machine?

I was able to reproduce this behavior.  I logged onto my machine as "Joe", created a connection SDE and created a feature called "Vector.Joe.Parcels".

I then logged off of my machine and logged back on as an administrator.  When I connect to SDE I can see the feature class, "Vector.Joe.Parcels" because I am an administrator.  When I attempt to run my script I receive the error that the feature class does not exist.  The reason being is that it is now using my current windows user, the administrator, and is therefore looking for "Vector.DBO.Parcels".

Unless you are connected to the database as the feature class owner, you will need to specify the full feature class name.

[BKuiper]

Problem solved.

I recreated the user, meaning, I changed the schema owner to dbo, deleted the user login from the database. deleted the user from the database instance and then recreated the user from scratch by doing the following: adding user in databaseinstance login, adding user in database login, assigning the default (thus the schema with the same name as the user) schema, assigning the necessary permissions.

Thanks for your help in solving this problem Jake! Your suggestion put me on the right track although they weren't directly related.