Hello all... I'm using asp to get records from an access database, verysimilar to the way datagrid would work. The title of each column in my tableis a link that alternates the sort order between ascending and descending...my problem is that text WILL change its sort order just fine but nubers arenot always in order. ie: if sort order is ASC (ascending) I might see 2000,234, 789 (should be ordered but its not). I'm guessing that ASP is handingthe string as a text string (?) and getting confused, is there a way toforce ASP into treating the string as numerals if this is the case? anyother ideas? Thanks so much.here is one of my sql commands in case you want to see it. "sort" is avariable containing the recordset to sort by depending on which link isclicked. I hope I didn't confuse the whole issue because of a lack ofcaffiene over here :)strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"Thanks of the help, much appreciated.Eno
Is there any way to change the sort order from case sensitive to case insensitive in SQL Server 6.5 without having to rebuild the master db and all users db's.
hi, if I change the sort order after I install sql server, I have to rebuild the databases. the meaning of rebuilding the Databases is to create a complete database from scrach including tables, store procedures data. or what is the meaning of rebuilding the databases
I have just started with 7.0 and found that one of my enviornments is not the same as production. I am trying to change the Char. set and sort order(rebuild master). In 6.5, install the CD and just update the options. In 7.0, do I have to un-install to do this? I looked on the CD and the local directory and can not seem to find the options to update the master db?
Hi, We ae planning to upgrade SQL 7 to SQL 2000 curretly we have BInar sort order.We have to cahnge the sort order to Dictionary sort order in SQL 2000 as per the recomendation from application vendor. What is the best way Ican do this? Can I cahnge the sort order at the time of upgrade without effecting the data? Thanks in advance Mohan
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
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)
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?
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));
I want to get output like this if i give the strings: output: ==== 25813/3 25813/115 25813/208
here "25813/" must be constant and remaining part must come in orderly way. but in MS SQL the first inserted value is getting first i.e if we give value 25813/115 is getting first. I will show in example:
create table number(num varchar(20)) insert into number values('25818/115') insert into number values('25818/208') insert into number values('25818/3')
The output coming is: color 1 25818/115 2 25818/208 3 25818/3
BUT I REQUIRE THE OUTPUT AS: color 1 25818/3 2 25818/115 3 25818/208
Version 2.2.32.7 1.0.0.0 2.0.0.0 1.2.0.0 2.1.8.8 1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version 1.0.0.0 1.2.0.0 1.4.11.0 2.0.0.0 2.1.8.8 2.2.32.7
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.
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.
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. ?
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!
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 ????
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.
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.
Does 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.
I'm doing some work for a client who has not been backing up his databases. After a server crash he has been left with the .MDF and .LDF files. I have managed to successfully use sp_attach_db on all but two databases. When attempting to use sp_attach_db I get the following error message :
1> sp_attach_db @dbname = N'CSGDEV', @filename1 = N'e:SQL_DataDirDataCSGDEV_Data.MDF', @filename2 = N'e:SQL_DataDirDataCSGDEV_Log.LDF' 2> go Warning: sort order 51 in database differs from server sort order of 52. Warning: Unicode comparison flags 196608 in database differs from server Unicode comparison flags of 196609. Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1 Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server. Warning: sort order 51 in database differs from server sort order of 52. Warning: Unicode comparison flags 196608 in database differs from server Unicode comparison flags of 196609. Msg 1816, Level 16, State 1, Server CSGSERVER, Line 1 Could not attach the database because the character set, sort order, or Unicode collation for the database differs from this server.
I'd appreciate any ideas. I'm not a SQL Server expert (heck, I'm not even a Windows expert...) as you can probably tell.
