how can I get this query to work?

Forum|Forum|16 years ago
May 8, 2009
1 reply
1134 views

HI

SELECT TOP 200 [word] ,COUNT(word) AS count
          FROM [COMMENT_WORDS]
          where word NOT in (SELECT wordText FROM word)
            GROUP BY word

            ORDER BY COUNT desc

gives me this error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

please help

Database access

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

count is a keyword. Don't use it as an alias.

The square brackets probably don't help. They might actually hurt.

"Not in " tends to be really slow. Better options depend on your db.

nikos101Author

Inspiring

Thanks I changed the count name but I am still getting

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I also went into the word database and ran

ALTER DATABASE [word] COLLATE

Latin1_General_CI_AS

and now both databases have the same colaltion but I still get the error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

I am using ms sql 2005

nikos101Author

Inspiring

I have went into the problem table and tried to change the collation in a column(database currently set to SQL_Latin1_General_CP1_CI_AS) using a dropdown list. However the Latin1_General_CI_AS drop option is not in the list even though the database is set to Latin1_General_CI_AS

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded