Ambiguous column name in VIEW using Microsoft SQL Server Management Studio
ON SQL 2005 SP1
Run Following script
USE [master]
GO
DROPDATABASE [JCTEST]
GO
CREATEDATABASE [JCTEST]
GO
CREATETABLE [dbo].[JCTEST1](
[preferred_name] [char]
(50)NULL,[surname] [char]
(50)NULL,[expected_end_date] [char]
(10)NULL)
ON
[PRIMARY]GO
SETANSI_PADDINGOFF
GO
CREATEVIEW JCTEST
AS
SELECTTOP(100)PERCENTUNICODE(surname)AS surname, expected_end_dateAS fred
FROM dbo.JCTEST1
ORDERBY surname
GO
SELECT*FROM JCTEST
and, as expected you get a recordset with 2 columns and no rows
Now go into Microsoft SQL Server Management Studio and "Modify" View JCTEST.
Use '!' to run view and it fails with "Ambiguous column name 'surname')
WHY!!!
Hi John,
I just tested your code and made some changes. I get no problems with opening the view, but I do get problems when running the code as posted - top statement. Don't believe it is a good idea to call your view by the same name as the db. not sure if this is much help, but at least I hope so.
USE [master]
GO
DROPDATABASE [JCTEST]
GO
CREATEDATABASE [JCTEST]
GO
-- -
-- Added use statement
-- -
USE [JCTEST]
CREATETABLE [JCTEST1](
[preferred_name] [char](50)NULL,
[surname] [char](50)NULL,
[expected_end_date] [char](10)NULL
)ON [PRIMARY]
GO
SET ANSI_PADDINGOFF
GO
CREATEVIEW JCTEST
AS
-- errors with TOP (100)
SELECTTOP 100PERCENTUNICODE(surname)AS surname, expected_end_dateAS fred
FROM dbo.JCTEST1
ORDERBY surname
GO
SELECT*FROM JCTEST
I'm pretty sure you can name it whatever you want, but if you're going to duplicate object names across object types, make sure you fully qualify the name, ie dbname.dbo.viewname or whatever your hierarchy actually looks like.
Hi,
I have repro'ed your problem on the latest internal builds (9.0.3020 SP2 candidate). If you run SQL Profiler when you click the "!" button in the query designer, you will see it emits this code
SELECT TOP (100) PERCENT UNICODE(surname) AS surname
, expected_end_date AS fred
, preferred_name
, surname
FROM dbo.JCTEST1 ORDER BY surname DESC
The query designer is modifying the original query, resulting in an error.
I believe it is related to this issue
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124819
I am forwarding this information to the appropriate program manager.
Thanks for reporting this.
Hi,
Re: "Don't believe it is a good idea to call your view by the same name as the db" ...
Database names and object names live in separate namespaces.
Suppose you have database X and table X. If you now use X in a 1-part name reference, like this:
SELECT * FROM X
T-SQL looks in the default schema for object X.
There is no ambiguity from the T-SQL POV (although perhaps from the reader's POV! :-)
Thanks
