Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CREATE INDEX failing after changing collation on table. Expand / Collapse
Author
Message
Posted Monday, November 8, 2010 4:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:31 AM
Points: 201, Visits: 270
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
Post #1017086
Posted Monday, November 8, 2010 5:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:05 PM
Points: 1,155, Visits: 4,635
Are you sure you run under correct database and correct schema ?


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1017115
Posted Monday, November 8, 2010 6:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 28, 2013 5:31 AM
Points: 201, Visits: 270
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
Post #1017160
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse