Cannot resolve collation conflict for column 1 in SELECT statement

Question

Post reply

Cannot resolve collation conflict for column 1 in SELECT statement

zach_john

SSCertifiable

Points: 5922
More actions
November 13, 2008 at 9:05 am

#193784

Why I'm getting this error It occurs when I try to add the name or type_desc fiels I've tried casting and converting the type_desc field
select 'DROP INDEX ' + object_name(s2.object_id) + '.' + s1.name + '--' + s1.type_desc
from sys.indexes s1
join sys.objects s2 on s1.object_id = s2.object_id
where s2.type_desc = 'user_table'
and s2.name not like 'ms%'
-- and s1.type_desc = 'CLUSTERED'
-- and s1.type_desc != 'CLUSTERED'
-- and s1.is_primary_key = 1
and s1.is_primary_key = 0
and s1.name is not null

John Zacharkan

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i got the same error on one of my databases when i tested your code;

adding collation to the first field fixed it for me:

select 'DROP INDEX ' + object_name(s2.object_id) + '.' + s1.name + '--' + s1.type_desc COLLATE Latin1_General_CI_AS

from sys.indexes s1

join sys.objects s2 on s1.object_id = s2.object_id

where s2.type_desc = 'user_table'

and s2.name not like 'ms%'

-- and s1.type_desc = 'CLUSTERED'

-- and s1.type_desc != 'CLUSTERED'

-- and s1.is_primary_key = 1

and s1.is_primary_key = 0

and s1.name is not null

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joseph-465703 SSCrazy Points: 2122 More actions · Answer 2

if your intention is to drop the index then i think you dont have to specify field name,

you need to specify like this:

DROP INDEX [tablename].[indexname]

for example

DROP INDEX authors.au_id_ind

zach_john SSCertifiable Points: 5922 More actions · Answer 3

My intention was to just drop them but wanted to point out the cluster indexes prior to execution.

Loweel thanks for the collation part of the script I haven't had a chance to use that, guess old dogs get to learn new tricks after all.

Zach

John Zacharkan