Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

The text, ntext, and image data types cannot be compared...

Posted on 2014-09-17
6
Medium Priority
?
277 Views
Last Modified: 2014-09-17
In the following query, I get  an error:

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

FYI,  [Description] is Text, not null
SELECT 
	s.Description AS Software
	, SUM(d.Quantity) AS TotalQuantity
FROM tblSoftware s 
INNER JOIN tblOrderDetails d
ON s.SoftwareID = d.SoftwareID 
GROUP BY s.Description;

Open in new window

When I replace s.Description with s.SoftwareID in two places, it runs okay.

Question: Is Description seen as memo field? And that is why I am getting this error?

Can I somehow convert s.Description to string to make it run?

If not, the other choice I see is dump the result to a temp table with SoftwareID and then update a blank desciption field in the temp table.

Thanks for your comment in advance.
0
Comment
Question by:Mike Eghtebas
  • 4
  • 2
6 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 40329441
Is Description seen as memo field? And that is why I am getting this error?

YES!

that field is probbaly TEXT or NTEXT, and this part of the error message is the reason it will not work:

text, ntext, and image data types cannot be .... sorted

When attempting to use it in the GROUP BY clause you need to sort that field - which it cannot do.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40329448
Meanwhile I produced this rather lengthy solution what possibly could be handled easily:
-- dump the result in a tempTable ta with s.SoftwareID replacing s.Description
-- and then uptate ta to get ta.Description populated:
DECLARE @ta table (
	ID int
	, [Desc] varchar(50)
	, Quantity int)

INSERT INTO @ta (
	ID
	, Quantity)
SELECT 
	s.SoftwareID
	, SUM(d.Quantity) AS TotalQuantity
FROM tblSoftware s 
INNER JOIN tblOrderDetails d
ON s.SoftwareID = d.SoftwareID 
GROUP BY s.SoftwareID;
 


UPDATE
    @ta
SET
    t.[Desc] = s.Description
FROM
    @ta t
INNER JOIN
    tblSoftware s
ON 
    s.SoftwareID = t.ID

 Select * from @ta

Open in new window

0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 2000 total points
ID: 40329451
>>"If not, the other choice I see is dump the result to a temp table with SoftwareID and then update a blank description field in the temp table."

Well you might be able to do that, but why not just join back to the table instead?

SELECT
      od.description
    , q.TotalQuantity
FROM tblOrderDetails AS od
      INNER JOIN (
                  SELECT
                        s.SoftwareID
                      , SUM(d.Quantity) AS TotalQuantity
                  FROM tblSoftware s
                        INNER JOIN tblOrderDetails d
                                    ON s.SoftwareID = d.SoftwareID
                  GROUP BY
                        s.SoftwareID
            ) AS Q
                  ON od.SoftwareID = Q.SoftwareID
;

Open in new window

BUT I suspect you need more than just this.
If you do need more please provide some sample data PER TABLE (not the query result)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40329457
By the way you need to look ahead on this issue:

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
ntext, text, and image (Transact-SQL)
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40329462
Thank you.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40329468
& thank you. Cheers, Paul
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question