I need to sort by an ntext field, but it won't let me do it.
However, if I cast the field as nvarchar(100), I can use ORDER BY on that.
Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).
I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
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)
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))"> but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please! Thanks Jez
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,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
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 have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
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 have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
I have some hierarchical data in a table. Say for example:
Parent Child ------------------------ NULL 1
1 2
1 3
2 4
2 5
3 6
3 7
5 8
5 9
7 10
7 11
11 12
11 13
Now I want to be able to use CTE's to be able to traverse this tree in 1) level by level order 1,2,3,4,5,6,7,8,9,10.... 2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...
What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.
Hi! For the Orders table (let's assume for the Northwind database), I'm trying to get the order id of the latest order for every customer. That means that the result should be one record per customer and that would display CustomerID and OrderID.
hi all, I am new to sql server. I need some help regarding ntext value.I am using an insert stored proc to insert the value into ntext field, till now i have been using a varchar variable to pass the value. but i think this doesn't work when the size exceeds the limit of varchar and also what if i want to perform group insert? and the major issue i am concerned about is, i don't want to update this field quite often.I need to check whether the existing ntext value and the new value which i want to insert are different.I want to do it in a better way than processing both values entirely.I want to make sure that the update statement is executed only when both values differ.
HI all, I Need some help over ntext datatype.one of my table columns is of ntext datatype. how to insert or update this thru storedprocs. any special notes regarding ntext column.
Hi, I'm using a column (ntext) to store some long strings. An example of the string that I need to store is the following:
436;Implementing A Gang - Awareness Program/And A Middle School Gang Prevention Curriculum. Doctoral Th;Samuels;Donald J;Miami IV;Administrators; At Risk; Child And Youth Studies; Community; Community Members; Drop Out Prevention; Educational Leadership; Law/Criminal Justice; Peer Counseling; Principals; Secondary Education
I wrote a store procedure that does it, but it seems that the column space is not enough for the above string. The string in fact, is truncated and only the following portion is stored in my table:
436;Implementing A Gang - Awareness Program/And A Middle School Gang Prevention Curriculum. Doctoral Th;Samuels;Donald J;Miami IV;Administrators; At Risk; Child And Youth Studies; Community; Community Members; Drop Out Prevention; Educational Leadership
Hi, I have the following problem. I cannot alter column which is ntext under sql server 2000. I get the following error:25-Jul-2007 09:27:11 ERROR [sol.cm.dbsetup.StatementReader] Error occured while executing the statement: Cannot alter column 'asz' because it is 'ntext'. SQLState = S0001 ErrorCode = 492825-Jul-2007 09:27:11 ERROR [sol.cm.dbsetup.StatementReader] SQL statement was: ALTER TABLE a ALTER COLUMN asz NTEXT NOT NULL I cannot use varchar because it is to short. Please tell me if there is a magic way to do this. I've already read in documentation that I cannot alter column which is ntext. Is there really no way to do this. Please help :)
I am working in asp and using sql server 7. I have a section where the input is to long to use a char field so i had to set the field as ntext. Now i am trying to do a section where i wanna look and see if the field is null and if not then it is to perform an operation. If it is null it is to skip and go to the next field in the record. However it is not performing this operation. It sees the first field and does pass the second one because it is null. If i put in a character into the field it will go on until it gets to a null again and then stops. Any suggestions?
Hallo. I need help, how pull out some inquiry string from type "ntext" in MS SQL(it is xml document). Sring has invariable length, in note is always on other position and includes variable text (e.g .:<actionId>xx</actionId>) . Position I can find out by the help of "patindex" but I don't know what then. I tryed to write procedures, but I had trouble with declaration variables (data type). Thanks and sorry for my horrible English.
Maybe a stupid question but I'm new to the db admin work so please bear with me.
I've imported an Access db into SQL, in the Access db the field type was 'memo' to accomodate the large amount of text (on avg ruffly 4100 chars. with spaces). Now in SQL the field in the table I have set up as an ntext field, which I understood to be equivalent to a memo field in Access.
My problem is when saving data to the field the first time it saves all the data correctly with the exception of the field in question. The data in the field is '<LongText>', now when I try to update the data in the table I get a 'Data Truncated' error message and no update takes place throughout the table.
After testing this and trying different things, I've found that if I shorten this one field and try to save to the db I still get the 'Data Truncated' error message. If I shorten the data in the field AND delete the record from the SQL table then it will save just fine from there on out (which won't work for the reports).
I'm not sure what I'm missing here to get this to work the way it did in Access.
I need to save some news text in an SQL table. The text can be long. 1. Should I use nvarchar(MAX) or nText? 2. And what is the difference between nText and Text?
