Page 1 of 1
Schemas with MS-SQL 2008
Posted: Mon 13 Aug 2012 13:05
by 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
Re: Schemas with MS-SQL 2008
Posted: Mon 13 Aug 2012 14:28
by AndreyZ
Hello,
Please specify the exact situation when this error occurs (opening a table, executing a stored procedure, etc.).
Re: Schemas with MS-SQL 2008
Posted: Mon 13 Aug 2012 14:50
by Thomas.Schweikert
After we successfully connected to the db the first
select * from [TABLE_NAME] causes the error
Thomas
Re: Schemas with MS-SQL 2008
Posted: Tue 14 Aug 2012 05:47
by 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
Re: Schemas with MS-SQL 2008
Posted: Tue 14 Aug 2012 09:28
by Thomas.Schweikert
AndreyZ wrote: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
Re: Schemas with MS-SQL 2008
Posted: Tue 14 Aug 2012 10:40
by 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.
Re: Schemas with MS-SQL 2008
Posted: Tue 14 Aug 2012 13:32
by Thomas.Schweikert
AndreyZ wrote: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
Re: Schemas with MS-SQL 2008
Posted: Thu 16 Aug 2012 06:42
by AndreyZ
I cannot reproduce the problem. Please create a backup of your database and send it to andreyz*devart*com .
Re: Schemas with MS-SQL 2008
Posted: Tue 21 Aug 2012 06:17
by 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.