May 8, 2009 at 3:40 pm
hello all i created a database on sql server 2005 which have tables stored procedures views and functions...now i imported the tables and copied the functions and the view and everything is working perfectly.... but stored procedures when i copy some of them i get this eror....the text ,ntext , and image data types cannot be compared or sorted except when using is null or like operator.
i need the stored procedures how can i overcome this problem
May 9, 2009 at 5:26 am
Hi ibrahim
What do you mean with "import" and "copy"? Did you try to use the SSMS "Generate Scripts" feature? This should work to script procedures, views and functions.
Where do you get the specified error?
Greets
Flo
June 1, 2009 at 7:20 am
Sounds like one of your stored procedures is sorting, joining, or comparing a TEXT column.
TEXT is useful for storing lots of highly variable input provided you only want to store it & never want to compare one value to another.
June 1, 2009 at 7:29 am
TEXT/NTEXT are marked as deprecated in SQL Server 2005. You should switch to VARCHAR(MAX)/NVARCHAR(MAX)
The provided error occurs when you try to use an "=" operation on a TEXT column or try to sort (ORDER BY) a TEXT column.
June 1, 2009 at 7:30 am
If you had varchar(max), the procedure might have changed to TEXT when it moved to 2000. You can't use varchar(max) in 2000, and you can't sort, compare, etc. a TEXT column.
June 1, 2009 at 7:35 am
:w00t:
Sorry, I misread. You want to move from 2k5 to 2k? Forget my advice to use VARCHAR(MAX)/NVARCHAR(MAX)
To compare TEXT/NTEXT either use LIKE or a full-text index.
Greets
Flo
June 1, 2009 at 7:58 am
ok thank you guys for your reply...no i was just copying and pasting it and i put this post a long time ago and i didn't receive any notifications about responses so i changed the format of the stored procedure and it worked ..thank you very much guys
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply