sql server (alternate): Using SQL Server 2000 sp1 on windows xp pro.

We sometimes use the formatmessage() function in a union query.to return a
language-specific label such as '<All>', which we want at the top of the
list. We've noticed that it seems to affect the sort order of the second
select in a subtle way. Here's an example using a table of clients:

/************************************************/
create table clients(clientName varchar(50) not null primary key)
go

insert into clients(clientName) values ('McMaster')
insert into clients(clientName) values ('Macbride')
insert into clients(clientName) values ('Micheals')
insert into clients(clientName) values ('Mowen')
insert into clients(clientName) values ('M-Moose')
insert into clients(clientName) values ('M-Miner')
insert into clients(clientName) values ('Marlborough')
insert into clients(clientName) values ('Mavis')
go

-- if we use a regular string the sort order is as expected:
select clientName, 1 as sort
from clients
union all
select '<All>', 0
order by sort, clientName
go

-- however if I use the formatmessage() function to return a value from the
system messages table the sort order changes
select clientName, 1 as sort
from clients
union all
select coalesce(formatmessage(50001),'<All>'), 0
order by sort, clientName
go

drop table clients
go

/****************************************/

Specifically, the second query seems to be ignoring the '-' character in the
client name. The workaround is easy, just declare a string variable and
asisgn it the formatmessage() return value beforehand. But I'd be interested
to know what is going on here.

I actually get the same sort for both SELECT statements but it would appear
that your result is different because the FORMATMESSAGE function makes the
Clientname column into an NVARCHAR with a different sort order in your
collation. Try casting it as VARCHAR:

SELECT clientname, 1 AS sort
FROM clients
UNION ALL
SELECT CAST(COALESCE(FORMATMESSAGE(50001),'<All>') AS VARCHAR(50)), 0
ORDER BY sort, clientname

--
David Portas
SQL Server MVP
--

I actually get the same sort for both SELECT statements but it would appear
that your result is different because the FORMATMESSAGE function makes the
Clientname column into an NVARCHAR with a different sort order in your
collation. Try casting it as VARCHAR:

SELECT clientname, 1 AS sort
FROM clients
UNION ALL
SELECT CAST(COALESCE(FORMATMESSAGE(50001),'<All>') AS VARCHAR(50)), 0
ORDER BY sort, clientname

--
David Portas
SQL Server MVP
--

[quoted text, click to view]


Hello David - yes, that is what is happening - I forgot that formatmessage()
function returns a nvarchar. Many thanks for your help.