SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Query Analyzer strange behaviour

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Query Analyzer strange behaviour
Author Message
rkapl



Joined: 12 Feb 2007
Posts: 3
Country: austria

Post Query Analyzer strange behaviour Reply with quote
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
Mon Feb 12, 2007 6:55 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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
Mon Feb 12, 2007 10:32 pm View user's profile Send private message
rkapl



Joined: 12 Feb 2007
Posts: 3
Country: austria

Post Reply with quote
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
Tue Feb 13, 2007 5:51 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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

Tue Feb 13, 2007 11:28 pm View user's profile Send private message
rkapl



Joined: 12 Feb 2007
Posts: 3
Country: austria

Post Reply with quote
Thanks a lot, works alright !

-regards,
ROland
Fri Feb 16, 2007 6:40 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.