Start a new topic

Wrong SQLServer schema name

[This topic is migrated from our old forums. The original author name has been removed]

DbVisualizer 6.0.12 MS SQL Server 2005 In SQL Server 7 and 2000 a database object's schema and its owner were essentially the same thing. In SQL Server 2005 the owner and schema have been separated. It appears that DBVisualizer is attempting to resolve schema names by mapping to user objects. The result is that an object whose name was *my_schema.db_object* (for example) might show up in the object tree as *some_user.db_object*. In the case I observed, the user_id of "some_user" had the same value as the schema_id of "my_schema". When I look at the object using Microsoft's SMSS tool the schema portion of the object name is displayed correctly.

[This reply is migrated from our old forums. The original author name has been removed]

Re: Wrong SQLServer schema name
OK, I just noticed that view names are fine, but function names are not. When I saw the bad function name I assumed (silly me) that the same problem would apply to other DB objects. I'll try creating a table and report back.
[This reply is migrated from our old forums. The original author name has been removed]

Re: Wrong SQLServer schema name
I did some more testing and found that table names are displayed correctly, but stored procedure names are NOT. So, this problem seems to apply only to the "Functions" and "Stored Procedures" portions of the objects tree.
[This reply is migrated from our old forums.]

Re: Wrong SQLServer schema name
Hi Steve, Thanks for reporting this and sorry for the late reply. I'm looking into this now and started by trying to reproduce the problem. Unfortunately, I'm not able to do so. When connected to a SQL Server 2005 database, I create a procedure without specifying a schema: create procedure withoutaschema as /* comment */ It is then shown as "dbo.withoutaschema" in the Objects Tree. When I specify a schema: create procedure [guest].withaschema as /* comment */ it is shown as "guest.withaschema" in the Objects Tree. The column in the Object View Procedures tab is named "owner" rather than "schema", but for 2005 it still seems to show the schema as the "owner" rather than the user. Can you please clarify what the problem is and what effect it has, or if my test is incorrect, please point out where I'm making a mistake? Thanks, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Wrong SQLServer schema name
Your test is insufficient to reproduce the problem, since you're testing with a system-created schema whose schema_id is the same as the "guest" user account's principal_id. Instead, try creating a new schema which DOES NOT correspond to a user account: CREATE SCHEMA [testschema] Then create a procedure in that schema: CREATE PROCEDURE [testschema].withaschema AS /* comment */ Hopefully that will cause the problem to show up on your system.
[This reply is migrated from our old forums.]

Re: Wrong SQLServer schema name
Hi Steve, Thanks for the hint. Now I'm able to reproduce the problem and will try to have it fixed for the next release. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Wrong SQLServer schema name
Excellent! Thanks for your help Hans.