Only * (All columns) in EM Query builder when database name contains a . (dot)

I encountered a nasty bug in SQL Server 2000.
Symptoms:
When you try to create a query in the Query Builder of Enterprise
Manager, the selected tables are displayed without columns. Only *
(All Columns) is displayed. Besides that, the table receives an alias
with <table name>_1. You can create such a query by right-clicking on
a table in the database browser and selecting the menu option Open
Table - Query. Queries will run just fine, but you will need to type
column names yourself.
Analysis:
When investigating this, the Profile told me that sp_columns_ex was
executed when a table was added to the diagram pane. When I add Table1
in database test.dot to the diagram pane:
exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
The first parameter to this stored procedure is the name of a linked
server. Apparently, the name causes a misinterpretation of the SQL
statement,
SELECT * FROM Table1 Table1_1
that was created by the query builder.
If the SQL statement is modified to:
SELECT * FROM [test.com].[dbo].[table1] table1_1
brackets are removed automatically and upon execution, the error
message
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
'test' in sysservers. Execute sp_addlinkedserver to add the server to
sysservers.
is given. A linked server with name test and a database with name dot
are searched.
Solution:
Do not use . (a dot) in a database name.
You might not get much response because virtually nobody who really
knows SQL Server will have anything to do with the Query Builder. I've
never opened it in all the years since it became available, and those
who try it don't seem to use it for long.
Query Analyzer does not provide the sort of "help" that Query Builder
does, but at least we only have to deal with the problems we create
for ourselves.
Roy Harvey
Beacon Falls, CT
On 30 Mar 2007 05:02:44 -0700, "Roel Schreurs"
<roel.schreurs@.gmail.com> wrote:

>I encountered a nasty bug in SQL Server 2000.
>Symptoms:
>When you try to create a query in the Query Builder of Enterprise
>Manager, the selected tables are displayed without columns. Only *
>(All Columns) is displayed. Besides that, the table receives an alias
>with <table name>_1. You can create such a query by right-clicking on
>a table in the database browser and selecting the menu option Open
>Table - Query. Queries will run just fine, but you will need to type
>column names yourself.
>Analysis:
>When investigating this, the Profile told me that sp_columns_ex was
>executed when a table was added to the diagram pane. When I add Table1
>in database test.dot to the diagram pane:
>exec sp_columns_ex N'test', N'Table1', N'dbo', N'dot', NULL
>exec sp_columns_ex N'test', N' ', N'dbo', NULL, NULL
>The first parameter to this stored procedure is the name of a linked
>server. Apparently, the name causes a misinterpretation of the SQL
>statement,
>SELECT * FROM Table1 Table1_1
>that was created by the query builder.
>If the SQL statement is modified to:
>SELECT * FROM [test.com].[dbo].[table1] table1_1
>brackets are removed automatically and upon execution, the error
>message
>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
>'test' in sysservers. Execute sp_addlinkedserver to add the server to
>sysservers.
>is given. A linked server with name test and a database with name dot
>are searched.
>Solution:
>Do not use . (a dot) in a database name.
|||On Mar 30, 7:54 am, Roy Harvey <roy_har...@.snet.net> wrote:
> You might not get much response because virtually nobody who really
> knows SQL Server will have anything to do with the Query Builder. I've
> never opened it in all the years since it became available, and those
> who try it don't seem to use it for long.
> Query Analyzer does not provide the sort of "help" that Query Builder
> does, but at least we only have to deal with the problems we create
> for ourselves.
>
Not to mention, nobody who really knows SQL Server would include a dot
in an object name. Why would you do that?
|||On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
<tracy.mckibben@.gmail.com> wrote:

>Not to mention, nobody who really knows SQL Server would include a dot
>in an object name. Why would you do that?
Good point! 8-)
Roy
|||I had not expected any responses, just to be of help at sometime to
anyone who would care to search usenet for clues on this misbehaviour
of the Query Builder.
I am sure you are very clever folks and do not need something as dumb
as a query builder. Personally, I can create queries for analysis
purposes quicker using the Query Builder than the Query Analyzer,
although I never use it to build any stored procedures used by client
software.
Anyway, confronted with someone choosing an unfortunate database name,
containing a version number, this can be a annoying problem.
Not to mention, nobody who spends a lot of money buying Microsoft
products should be pleased with a product that is not fail safe...
On 30 mrt, 15:40, Roy Harvey <roy_har...@.snet.net> wrote:
> On 30 Mar 2007 06:07:34 -0700, "Tracy McKibben"
> <tracy.mckib...@.gmail.com> wrote:
> Good point! 8-)
> Roy