ntext Data Type Error - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

ntext Data Type Error

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-01-27 : 05:15:08

Running this query is fine

select distinct o.OrderDate, o.OrderNumber, c.CustomerID, c.FirstName, c.LastName, c.Email, c.Phone, o.OrderTotal, c.CustomerLevelID
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.PromotionUsage pu on pu.OrderID = o.OrderNumber
join dbo.Promotions pm ON pm.ID = pu.PromotionID
and c.CustomerLevelID = '5'
and o.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 7,0)
order by o.OrderDate, c.CustomerID

But when I try to add c.Notes into the select line I get

Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.

Any idea how I can get this to run?

gecew
Starting Member

10 Posts

Posted - 2014-01-27 : 05:33:28

select distinct(c.CustomerID),c.Notes, o.OrderDate, o.OrderNumber, c.FirstName, c.LastName, c.Email, c.Phone, o.OrderTotal, c.CustomerLevelID
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.PromotionUsage pu on pu.OrderID = o.OrderNumber
join dbo.Promotions pm ON pm.ID = pu.PromotionID
and c.CustomerLevelID = '5'
and o.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 7,0)
order by o.OrderDate, c.CustomerID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 07:16:46

quote:
Originally posted by deanglen

Running this query is fine

select distinct o.OrderDate, o.OrderNumber, c.CustomerID, c.FirstName, c.LastName, c.Email, c.Phone, o.OrderTotal, c.CustomerLevelID
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.PromotionUsage pu on pu.OrderID = o.OrderNumber
join dbo.Promotions pm ON pm.ID = pu.PromotionID
and c.CustomerLevelID = '5'
and o.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 7,0)
order by o.OrderDate, c.CustomerID

But when I try to add c.Notes into the select line I get

Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.

Any idea how I can get this to run?

castit to nvarchar(max) and try

ie like below


select distinct o.OrderDate, o.OrderNumber, c.CustomerID, c.FirstName, c.LastName, c.Email, c.Phone, o.OrderTotal, c.CustomerLevelID,CAST(c.Notes AS nvarchar(max))
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.PromotionUsage pu on pu.OrderID = o.OrderNumber 
join dbo.Promotions pm ON pm.ID = pu.PromotionID 
and c.CustomerLevelID = '5'
and o.OrderDate >=dateadd(day,datediff(day,0,GetDate())- 7,0)
order by o.OrderDate, c.CustomerID

Ideally you shouldnt use text,ntext and image as they're deprecated from sql 2005 onwards
You should be using varchar(max),nvarchar(max) or varbinary(max) instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources