Hi Everybody,
Can anyone tell me apart from "sp_helpsort" system stored procedure, is there any other command to find details of Character Set,Sort Order & Unicode Collation?.
thanks,
Vasu
Hi there,Trying to work with SQL2005 with Hebrew characters. If i use the Unicode character data types do i need to change the database collation???. regards
View 5 Replies View RelatedDoes anyone have any experience handling mixed-character data in Unicode, especially in how to handle sorting it?
For example, if I have a customer database with records from all over the world, with customer names in Chinese, Russian, Swedish, Arabic & Hebrew, and I want to generate a list of all customers who have not upgraded to the latst version yet, it will be sorted in the sort order of the locale I defined at DB installation. Say, in this case, US English. How do the Chinese, Arabic, Swedish, etc. characters get sorted?
I'm interested to know if this is something that people are grappling with out there, or whether it's a non-issue. I think what's probably needed is a special Unicode mixed-character sort order, encompasing all the Unicode codepoints and sorting them in an order that's as usable as possible for the general user. (e.g. Latin characters case-sensitive, including accented characters, then Asian characters in stroke order, etc.) Or maybe serveral different mixed-character sort orders would be the thing... Any feedback would be appreciated.
New SQL Server 2005 install (Stnd edition with SP2) and the Vendor for the Application Software has these requirements:
MS SQL Server SP2 with FULL Text Indexing and Natural Language Query. Collation SQL_Latin1_General_CPI_CI_AS; Date settings Month/Day/Year
When I installed SQL Server I basically installed the Default Collation of SQL_Latin1_General - is that different from their requirements???
Also, not sure about Natural Languarge Query AND the date settings??? is Natural Language Query installed by default and how do I check or change the Date Settings????
I have an O2 Xda IIs Pocket PC running PPC 2003 SE, WWE (Worldwide English) edition.
I want to create a database which contains all Chinese characters that have been mapped under the Unicode Standard. I already have a similar database on my desktop PC. On my desktop PC, I have used the collation Chinese_Hong_Kong_Stroke_90_CI_AS. However, on my Pocket PC, I do not see any Chinese or even Unicode sort orders. The best I get is "General".
I want to use the Chinese sort order to govern the way the characters are sorted (ie., characters with fewer strokes appear first).
How am I able to do this? / Is there anything extra that I may install to make additional collations available?
I'm in the process of migrating an existing sql server 2000 that currently has the following collation set / sort order :
SQL_Latin1_General_CP850_CS_AS.
I would like to know what the corresponding collation set / sort order is for SQL Server 2005. Looking at the option when installing there doesn't seem to be an option for 850 multilingual and case sensitive and accent sensitive. This is when selecting SQL Collations 9used for compatability with previous version of SQL Server).
If I were to select Latin1_General and check the Case sensitive and Accent sensitive would this give me the desired results ?
Ant help appreciated...
i have 2 questions,
1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:
99/06/11 12:59:24.29 spid1 SQL Server's default sort order is:
99/06/11 12:59:24.29 spid1 'nocase' (ID = 52)
99/06/11 12:59:24.29 spid1 on top of default character set:
99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)
so my old installation's character set and sort order is as above,am i right?
2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?
thanks if any suggestions!
welcome to send me mail about the question.
I'm connecting to a SQL Server 2005 database using the latest (beta) sql server driver (Microsoft SQL Server 2005 JDBC Driver 1.1 CTP June 2006) from within Java (Rational Application Developer).
The table in SQL Server database has collation Latin1_General_CI_AS and one of the columns is a NVARCHAR with collation Indic_General_90_CI_AS. This should be a Unicode only collation. However when storing for instance the following String:
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_ЎўЄєÒ?Ò‘_прÑ?туф_ЂЉЊЋ
... it is saved with ? for all unicode characters as follows (when looking in the database):
‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????
The above is not correct, since all unicode characters should still be visible. When inserting the same string directly into the sql server database (without using Java) the result is ok.
Also when trying to retrieve the results again it complains about the following error within Java:
Codepage 0 is not supported by the Java environment.
Hopefully somebody has an answer for this problem. When I alter the collation of the NVARCHAR column to be Latin1_General_CI_AS as well, the data can be stored and retrieved however then of course the unicode specific characters are lost and results into ? So in that case the output is as described above (ie ‚¬_£_ÙÚÜÛùúüû_ÅÆØåæøߣÇçÑñ_¼½¾_??????_??????_????)
We would like to be able to persist and retrieve unicode characters in a SQL Server database using the correct JDBC Driver. We achieved this result already with an Oracle UTF8 database. But we need to be compliant with a SQL Server database as well. Please help.
Thanks in advance for your help.
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Hi,
We want our database to be unicode complaint and i need a generic collation that should do the work irrespective of the fact what data is entered in the database. Can someone suggest me any generic collation for that.
Thanks and regards
Salil
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
Hi,
We got a problem.
supposing we have a table like this:
CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go
insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)
select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde
select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi,
We got a problem.
supposing we have a table like this:
CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go
insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)
select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde
select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.
Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS
for example,
create table test_sort
( description varchar(75) );
insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');
then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));
select
*
from
test_sort
order by
cast( description as nvarchar(75));
Resultset1
----------
Non-A
Non-O
Noni
Nons
Resultset2
----------
Non-A
Noni
Non-O
Nons
Any ideas?
I am reading text file using OPENROWSET command. The read is successful. But the UNICODE characters in the file is not showing properly in the output.I am using a sql like below:
select
*
FROM
OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=ppa20-igloo-fsMODELSTORECR106_GIM_UKGISourceFilesProcess;HDR=Yes;Format=Delimited(|)',
'SELECT * FROM [RUW_P_Reinsurers_Aviva_and_Others_Map.txt]')
how should I read the unicode characters correctly.
Hi,
Wich collation should I use for the right display of the euro character? I now get a '?'.
Thanks!
Roel
I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.
... where customername like char(160) + '%'
returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?
Thanks!
-Dave
The data file is a simple Unicode file with lines of text. BCPapparently doesn't guarantee this ordering, and neither does theimport tool. I want to be able to load the data either sequentially oradd line numbering to large Unicode file (1 million lines). I don'twant to deal with another programming language if possible and Iwonder if there's a trick in SQL Server to get this accomplished.Thanks for any help.Mark Leary----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups---= East/West-Coast Server Farms - Total Privacy via Encryption =---
View 15 Replies View RelatedHi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.
How can i change the sort order to 42.
I am using sql server 6.5
Thanks
Srinivas
We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?
View 2 Replies View RelatedHOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?
HELP
I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.
Thanks,
Rob.
I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.
View 1 Replies View RelatedHow can I set the sort order to 42, nocase when I install sql server 6.5
does Setup gives you some option to check to set sort order ?
For SQL 2000,
Can I assign different sort order on the database level?
thanks!
Hi,
Can any one pls tell me what this sort order id 42 corresponds to and how its different from 52 ?
What options i need to check during installation for sort oreder id. ?
Thanks
Srinivas
How do I create a sort order column in a view? I could use Row_Number() in 2005 but unfortunately I need to create a sort order column in a 2000 view and the View is not letting me to use an ORDER BY.
Any inputs?
Prakash.P
The secret to creativity is knowing how to hide your sources!
Are there any techniques I can use to specify the sort order on an insert statement.
I want to insert data from tableA to tableB in a certain order .
I know I can do it with a cursor, but I'm sure theres a better way
Pargat
Hi,
I want to change the "dictionary Sort Order" in SQL Server 7.0. What will be the impact? What is the safe way to do it?
Under MSSQL7INSTALL there is a file called instcat.sql. Should I run this file to change the catalog settings?
Any help is appriciated.
Thanks
Sri
In Sql6.5 we use binary sort order for a best performance (I think). I hear that it should be highlighted that the case for binary sort order being the fastest method of sorting and searching a database no longer applies at SQL 7.0
Is it right and why ????
Help ...
Does this mean that I have to re-install SQL Server7 on the target server? For some reason, all except one (the source in this case) of our servers were set up taking the defaults (or at least they are all the same). I can not change the source.
Any advise, before I kill myself
Judith
Hi,
I am trying to upgrade peoplesoft database which is in SQL server 6.5 with Dictionary sort order to SQL Server 7.0 Binary sort order. SQL Server Upgrade wizard fails because of different sort order. If anyone knows about upgrading SQL Server 6.5 with Dictionary sort order to SQL 7.0 with Binary sort order, would be appreciated.
Thanks,
Mohana