Hi 🙂
Thank you Lowell and SQL Kiwi for your answers.
Our application send SQL requests one-by-one via ODBC cursor and then fetch next. With the good collation, SQL would return the good one first result and then the fetch next would return the next ones in order.
I've tried SQL Kiwi solution and it doesn't work :hehe:
USE [TestCollation
GO
/****** Object: Table [dbo].[tmp] Script Date: 01/24/2012 09:48:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmp](
[char_name] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
[ascii_val] [smallint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO [TestCollationFrench].[dbo].[tmp]
([char_name]
,[ascii_val])
VALUES
(<char_name, varchar(1),>
,<ascii_val, smallint,>)
First print screen: initial data
Second print screen :SELECT * FROM dbo.tmp ORDER BY char_name. You'll notice that the results are not ordered by the column ascii_val order. Only the 16 firt lines are correctly ordered.
Third print screen: This is what i want to obtain. Results expected (almost ordered by ascii_val column with numbers before letters).
Have a nice day 🙂