Post reply

Cannot Create View - Object_Id Sequencing Issue?! Corruption?

  • October 24, 2011 at 11:06 am

    #249819

    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

    #1398825

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2011 at 5:36 am

    #1399033

    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

    #1399039

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply