Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CREATE INDEX failing after changing collation on table.


CREATE INDEX failing after changing collation on table.

Author
Message
gbd77rc
gbd77rc
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 272
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
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4838
Are you sure you run under correct database and correct schema ?

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

gbd77rc
gbd77rc
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 272
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search