Query Analyzer strange behaviour

Dear Support!

When I choose a certain database in Query Analyzer (our company internal database), following error message box popos up:

The same happens when opening the treeview node of that database appearing in the selection list of a different database (e.g. master). My database's tables are not too specific, I believe:

Query Analyzer version 8.00.194, SQL Assistant 1.0.68

I'd appreciate any comment, as this database is quite central to me ![/img]

-regards,
Roland

Hi,

Could you please translate for me the first 2 lines of the error?

There is no error number and without understanding the text, I am unable to explain this message and suggest how to deal with it.

Thank you

Sorry, it reads:
Conflict of Sorting for column 1 in SELECT statement could not be resolve.

I presume it's error 451 (at least the description: "Konflikt der Sortierung für Spalte %1! in der %2!-Anweisung kann nicht aufgelöst werden." matches best)

-regards,
Roland

Thank you for the translation. Now I know what is going on. You have mixed collations. The default collation in the database OEBRA is not the same as in the master database. As a result the UNION fails because the UNION operator is collation sensitive and SQL Server doesn't know how to sort by name column and how to find distinct values.

To fix this issue please double-click on SQL Assistant icon in the system tray. The Options dialog will popup. Click DB Options tab and then select "Schemas (MSSQL)" query type. In the query text add explicit collation clause, in other words after each occurrence of the "name" column paste something like " COLLATE Latin1_General_CI_AS "

The result should look like below

Code:

if @@version like 'Microsoft SQL Server 2005%'
select
   name COLLATE Latin1_General_CI_AS,
   case when type in ('S','U','G') then 'SC' else 'SR' end,
   principal_id
from
   /*db.*/sys.database_principals
where
   type in ('S','U','G','A','R')
union all
select
   name COLLATE Latin1_General_CI_AS,
   case when type = 'R' then 'SR' else 'SL' end,
   principal_id
from
   /*db.*/sys.server_principals
where
   type in ('S','U','G','R')
else
select
   name COLLATE Latin1_General_CI_AS,
   case when issqlrole + isapprole > 0 then 'SR' else 'SC' end,
   cast(uid as int)
from
   /*db.*/dbo.sysusers
where
   issqluser = 1 or isntuser = 1
or issqlrole = 1 or isapprole = 1

union all
select name COLLATE Latin1_General_CI_AS, 'SL', 0
from master.dbo.syslogins

Thanks a lot, works alright !

-regards,
ROland