October 24, 2011 at 11:06 am
When trying to create a new view, I get an error saying object already exists. But when you query sys.objects, it does NOT exist. So then I tried dropping the "nonexistent" object and guess what? It tells me it does not exist so nothing is dropped!
Closest thing I could find was KB for SQL 2000 - and this did not help (idea was object_id is actually in use somehow even though object name is not in the catalog - almost like the name and id were orphaned somehow):
http://support.microsoft.com/kb/827448
Version:
Microsoft SQL Server 2005 - 9.00.5266.00 (X64)
Code/messages below...
==========================================================
/****** Object: View [dbo].[CvtyPlan] Script Date: 10/24/2011 08:57:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[CvtyPlan]
AS
SELECT *
FROM cvty_content.dbo.CvtyPlan WITH (NOLOCK)
GO
--Msg 2714, Level 16, State 3, Procedure CvtyPlan, Line 5
--There is already an object named 'CvtyPlan' in the database.
select * from sys.objects where name = 'CvtyPlan' and type = 'v'
--(0 row(s) affected)
===========================================================
Thanks,
--tz
October 24, 2011 at 11:26 am
Select * from sys.objects where name = 'CvtyPlan'
???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2011 at 5:36 am
Gila-M, thanks for your reply. As I stated querying sys.objects (and all other catalog views) returns 0 records. I found the issue -- a table existed with the same name as the view I was trying to create:
I was merging two dbs into one new database and as such had a Table with the same name as a View I was trying to create. Apparently, even though the catalog views distinguish between a view [v] and table , the names must still be unique.
So I was querying sys.objects for a view named XYZ which returned 0 results, and when trying to drop that nonexistent view it rightfully said it didn't exist -- yet the user table of the same name did and that's where the conflict was.
Thanks,
--tz
October 25, 2011 at 5:44 am
TZ.DBGeek (10/25/2011)
Gila-M, thanks for your reply. As I stated querying sys.objects (and all other catalog views) returns 0 records. I found the issue -- a table existed with the same name as the view I was trying to create:
That's why I asked about a query of sys.objects without a filter on type, which would have picked up that table.
Object names must be unique in a database, not just objects of a particular type. So a view can't have the same name as a table which can't have the same name as a procedure, trigger, function, constraint, etc.
The name column in sys.objects is unique (well, in the underlying system tables it is)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply