Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BabyAnn
Starting Member
10 Posts |
 Posted - 2008-03-03 : 04:23:21
|
| Hello, When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz "The text, ntext, or image data type cannot be selected as DISTINCT."But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the differencethanks in advance! |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-03-03 : 05:31:28
|
| Hi,In SQL 2005 use cast(<colname> as NVARCHAR(MAX)) and use u r DISTINCT on the query.About SQL 2000, I am not clear what u r doing? |
 |
|
|
BabyAnn
Starting Member
10 Posts |
Posted - 2008-03-03 : 06:04:26
|
| Thanks for the information!To be more clear,CREATE TABLE TEST( WebDescription Ntext)INSERT INTO TEST (WebDescription) Values ('Test')Select * from TESTCREATE TABLE #List( WebDescription NVARCHAR(255), )INSERT INTO #List (WebDescription) (SELECT DISTINCT WebDescription FROM Test)Select * from #List--DROP TABLE #List--DROP TABLE TestThe above statements got executed without any errors in SQL 2000.I would like to know why? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
ntext data type cannot be selected as DISTINCT