?
Solved

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

Posted on 2014-09-17
6
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

801 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