Ambiguous column in WHERE join on sysobjects
-
Given table OurSysTables with columns:
TableID varchar(8) NOT NULL -- as Primary Key
Name varchar(40) NULL -- but always has a value
SELECT name
FROM sysobjects, OurSysTables
WHERE name = Name
-- returns:
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Name'.
Above works fine in SQL Server 2000, or if set
sp_dbcmptlevel ThisDb, 80
in SQL 2005.
However, following shows that table sysobjects is the culprit.
Why ?
The field 'name' is distinct from 'Name' in all cases.
CREATE TABLE FooLower (thekey int NOT NULL,
name varchar(20) NULL)
CREATE TABLE FooUpper (AKey int NOT NULL,
Name varchar(22) NULL)
INSERT INTO FooLower VALUES (1,'Tom')
INSERT INTO FooLower VALUES (2,'Mark')
INSERT INTO FooLower VALUES (3,'Jillian')
INSERT INTO FooUpper VALUES (1,'John')
INSERT INTO FooUpper VALUES (2,'TableA')
INSERT INTO FooUpper VALUES (3,'Jillian')
INSERT INTO FooUpper VALUES (4,'Tom')
SELECT name
FROM FooLower, FooUpper
WHERE name = Name
-- returns: 'Jillian' and 'Tom', in both versions of SQL.
SELECT name
FROM sysobjects, FooUpper
WHERE name = Name
-- returns error 209 in SQL 2005
-- returns 'TableA' in SQL 2000
Collation for entire server is: SQL_Latin1_General_CP1_CS_AS
i.e. case sensitive.
P.S. we are trying to minimize changes, and this query is used in many procedures that have already been delivered.
-
First, I would rewrite the query using the ANSI standard join syntax:
SELECT name
FROM
sysobjects
inner join OurSysTables
on (name = Name)
Second, I would also rewrite the query as such:
SQL 2000 -
SELECT obj.name
FROM
dbo.sysobjects obj
inner join dbo.OurSysTables systab
on (obj.name = systab.Name)
SQL 2005 -
SELECT obj.name
FROM
sys.sysobjects obj -- or sys.objects obj
inner join dbo.OurSysTables systab
on (obj.name = systab.Name)
If you do this, you should not get the error:
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'Name'.
-
If both tables used in the query/join has the same column name what you are selecting, sql gives that error...
so it is always advisable to use table alias/name before the column name when you use more than 1 table in your query...
MohammedU
Microsoft SQL Server MVP -
the columns are *not* identical ... unless SQL changed the rules for identifiers.
`sp_dbcmptlevel ThisDb, 80` only worked yesterday; it stopped working today. Any chance the SQL install is corrupt?Can anyone reproduce this issue in SQL 2005?
Aliases have issues too - search web on `ORDER BY` and `Ambiguous`.
-
Only time I have had an 'Ambiguous column error' was when I forgot to specify the table name or use a table alias in the query. When ever I have two or more tables in a query, I try to remember to qualify all column names, even when I know that a column name only exists in one table. Reason for that, you never know when a table structure wil change and what was once unambigous is no longer.
As for issues with aliases, you will have to provide me with some concrete examples, as I have not run into any that I can remember.
-
The issue with aliases in SQL 2000 was if you had an alias that was the same as a column name, it would use the column in Order By clauses instead of the alias. For example, if I had a varchar column called Months with the values 1 through 12 in it, and I cast it as Int and aliased it as Months in a query, it would order as text strings instead of integers (SQL 2005 does not have this issue):
Select Cast(Months as Int) As Months From MyTable Order ByMonths
Months
1
10
11
12
2
3
4
5
6
7
8
9
Tom, the rules for identifiers is that database object names (such as tables and columns) use the collation of the database, not the server. What are the collations of the databases in question?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
as stated at top, everything is collation of SQL_Latin1_General_CP1_CS_AS.
Only interested in SQL 2005 (2000 is working fine).
-
What you stated above is that the "Collation for entire server is: SQL_Latin1_General_CP1_CS_AS". Databases can have a different collation defined and it is database level collation that governs the rules for identifiers for database objects. Check the collation of the individual databases involved.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
checked collation. It is correct for all databases & both tables.
-
Then i would check the collation of the columns of sysobjects itself.
Select
c.*
From
sys.system_objects o
Inner
Join sys.system_columns c on c.object_id = o.object_id
Where
o.name = 'sysobjects'
And
collation_name is not null
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
that yields proper result (^ is delimiter):
/* object_id^name^column_id^system_type_id^user_type_id^max_length^precision^scale
^collation_name^is_nullable^is_ansi_padded^is_rowguidcol^is_identity^is_computed^is_filestream^is_replicated^is_non_sql_subscribed^is_merge_published^is_dts_replicated^is_xml_document^xml_collection_id^default_object_id^rule_object_id
-105^name^1^231^256^256^0^0
^SQL_Latin1_General_CP1_CS_AS^0^1^0^0^0^0^0^0^0^0^0^0^0^0
-105^xtype^3^175^175^2^0^0
^SQL_Latin1_General_CP1_CS_AS^0^1^0^0^0^0^0^0^0^0^0^0^0^0
-105^type^14^175^175^2^0^0
^SQL_Latin1_General_CP1_CS_AS^1^1^0^0^0^0^0^0^0^0^0^0^0^0
*/
Note that our table is not in sys.system_objects.
sp_help SysOurTables
/*
Column_name^Type^Computed^Length^Prec^Scale
^Nullable^TrimTrailingBlanks^FixedLenNullInSource^Collation
:::
Name^varchar^no^40^ ^
^yes^no^yes^SQL_Latin1_General_CP1_CS_AS
:::
*/
sp_help sysobjects
/* Column_name^Type^Computed^Length^Prec^Scale
^Nullable^TrimTrailingBlanks^FixedLenNullInSource^Collation
name^sysname^no^256^ ^
^no^(n/a)^(n/a)^SQL_Latin1_General_CP1_CS_AS
*/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply