Post reply

CREATE INDEX failing after changing collation on table.

  • November 8, 2010 at 4:42 am

    #231310

    Hi All,

    I dropped an index to change the collation of a column and now I am getting the following error message below when I try to recreate it.

    Location: sobuild.c:3392

    Expression: CompareRow ( rightPageRow, *m_pSEBindings, keyCnt, *pHobtAccess, PAGE_LEAF_LEVEL)

    SPID: 53

    Process ID: 19528

    Msg 1088, Level 16, State 12, Line 1

    Cannot find the object "dbo.company" because it does not exist or you do not have permissions.

    The reason for the cannot find object is that the database is switched to the master database! I have recreated other indexes successfully on other columns in the same table and other tables. I ran DBCC CHECKTABLE and it reports no errors. Original collation was Latin1_General_CI_AS and I altered it to SQL_Latin1_General_CP1_CI_AS. Tried to google sobuild but got no valid hits on this type of error. There 80,000 records in this table. The datatype of the column is varchar(100) NULL. The maximum length of any name in this table is 87 characters.

    I am temped to leave this column alone, but it will mean that this column will have different collation to the rest of the database.

    Regards

    Richard...

    http://www.linkedin.com/in/gbd77rc

  • November 8, 2010 at 5:36 am

    #1247599

    Are you sure you run under correct database and correct schema ?

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • November 8, 2010 at 6:48 am

    #1247635

    Hi,

    Yes it is ran in the database where dbo.company is located, but the error message 'object is not found' happens because the database context is switched to master because of the Location: sobuild.c:3392 error.

    I use the USE [database] before running the CREATE INDEX and manually selected in SSMS before running the CREATE INDEX command.

    Now I have found out after playing with the order of creating indexes it started to work. If I create the idx_company_id first then it works. If I create the index cp_name_o first then it gives the location error etc.

    CREATE NONCLUSTERED INDEX [idx_company_id] ON [dbo].[company]

    (

    [id] ASC

    )

    INCLUDE ( [name])

    go

    CREATE NONCLUSTERED INDEX [cp_name_o] ON [dbo].[company]

    (

    [name] ASC

    )

    go

    Regards

    Richard...

    http://www.linkedin.com/in/gbd77rc

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

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