Schemas with MS-SQL 2008

[Thomas.Schweikert]

Hi,

we've reorganized our tables using schemas from

[DB].[dbo].[OBJECTS (Tables ...)]

to

[DB].[PRODUCT_NAME].[OBJECTS (Tables ...)]

With MS-SQL 2005 no problems at all.
With MS-SQL 2008 we get an error "Can't find object [TABLE_NAME] ...."

The 2 db's are identical. use the same login user.
We actually have no idea what causes this error.

Thomas

[AndreyZ]

Hello,

Please specify the exact situation when this error occurs (opening a table, executing a stored procedure, etc.).

[Thomas.Schweikert]

After we successfully connected to the db the first

select * from [TABLE_NAME] causes the error

Thomas

[AndreyZ]

It seems that you didn't change the default schema for the user in your SQL Server 2008 (dbo by default). Default schema specifies the first schema that will be searched by the server when it resolves the names of objects for the user. Please make sure that the user you are connecting to SQL Server 2008 has PRODUCT_NAME as the default schema. You can find more information here:
http://msdn.microsoft.com/en-us/library ... .100).aspx
http://msdn.microsoft.com/en-us/library ... .100).aspx

[Thomas.Schweikert]

It seems that you didn't change the default schema for the user in your SQL Server 2008 (dbo by default).

No, default schema is set to the correct schema [PRODUCT_NAME].
And with MS-SQL 2005 all work's like expected.

Errormessage:
SQL-Server-Fehler: SQL State: 1, SQL Error Code: 208, Level 16,
procedure:, line: 1

Some more infos:
D7 & XE
dbExpress Version 5.0.3

[AndreyZ]

Please check whether you are able to get access to the table using the following SQL statement:

Code: Select all

select * from [PRODUCT_NAME].[TABLE_NAME]

Also, please check that your user has access to the TABLE_NAME table. For this, perform the following steps:

• open Microsoft SQL Server Management Studio;
• connect to SQL Server 2008 using credentials of your user;
• check if there is the [PRODUCT_NAME].[TABLE_NAME] table in the Tables list.

[Thomas.Schweikert]

Please check whether you are able to get access to the table using the following SQL statement:

Code: Select all

select * from [PRODUCT_NAME].[TABLE_NAME]

Also, please check that your user has access to the TABLE_NAME table. For this, perform the following steps:

• open Microsoft SQL Server Management Studio;
• connect to SQL Server 2008 using credentials of your user;
• check if there is the [PRODUCT_NAME].[TABLE_NAME] table in the Tables list.

select works
and table is in the table list

[AndreyZ]

I cannot reproduce the problem. Please create a backup of your database and send it to andreyz*devart*com .

[AndreyZ]

I have investigated your database. This problem is caused by the fact that your user is a member of the sysadmin server role. The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. You can find more information about that here: http://msdn.microsoft.com/en-us/library/ms176060.aspx
To solve the problem, you should not make your user a member of the sysadmin server role.