Solved

'80040e14'   The text, ntext, or image data type cannot be selected as DISTINCT. (None of the Fields Are?)

Posted on 2006-06-16
3
520 Views
Last Modified: 2008-01-09
Hello All;

 I receive the following error through my ASP page.
==============================================
Microsoft OLE DB Provider for SQL Server error '80040e14'

The text, ntext, or image data type cannot be selected as DISTINCT.

/BS/Connections/i_utils.asp, line 51
==============================================

This is the code:
==============================================
==============
sSQL = "SELECT distinct A.*, B.CatID, B.Cat, " & _
        " (SELECT COUNT(1) FROM Cats WHERE A.CatTypeID=Cats.CatTypeID) AS ParentCount, " & _
        " (SELECT COUNT(1) FROM Content WHERE B.CatID=Content.CatID) AS ChildCount " & _
        " FROM (CatTypes A LEFT JOIN Cats B ON A.CatTypeID = B.CatTypeID) " & _
        " LEFT JOIN Content C on B.CatID = C.CatID " 
Srs.Open sSQL, Bconn, 1, 3, 1      ' Line 51 is here
==============
==============================================
The FieldName and Data Types are:
CatID = bigint
CatTypeID = int
Cat = varchar

=======
As you can see, none of the Field's Data Types are of "text, ntext (or) Image"

Any idea's on this one?

Thanks All;
Carrzkiss
0
Comment
Question by:Wayne Barron
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 125 total points
ID: 16923457
Well you are doing a select a.*

so what are the columns in the CatTypes? There is a text/ntext or image in one of those columns I am willing to bet.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923462
script your CatTypes table and show it here. I bet you will see the column right there.
0
 
LVL 30

Author Comment

by:Wayne Barron
ID: 16923543
Oh man. That was sweet.

So it was looking at the entire Table of CatTypes. since it was the a.*
I will be darn,
Thanks a bunch.
It was the
CatType - text
I changed it to:
CatType - varchar.

You rock.
Thanks you
Wayne
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

821 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