What does this error mean? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

What does this error mean?

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-02-26 : 08:40:23

Hi,

I've got this:

SELECT A.VehicleRef, A.CapID, A.Manufacturer, A.Model, A.Derivative, A.AdditionalFreeText, MIN(A.CH) AS MinCH, B.StudioImages, A.CVehicle_ShortModText,A.Source,A.[type] FROM vwAllMatrixWithLombardAndShortModel AS A LEFT JOIN dbPubMatrix.dbo.tblCarImages AS B ON A.CapID = B.CAPID WHERE A.VehicleRef = 12861 AND A.Term = 3 AND A.MilesPA = 10000 AND a.[Type]='car' GROUP BY A.VehicleRef, A.CapID, A.Manufacturer, A.Model, A.Derivative, A.AdditionalFreeText, B.StudioImages,A.CVehicle_ShortModText,A.Source,A.[type]

UNION

SELECT A.VehicleRef, A.CapID, A.Manufacturer, A.Model, A.Derivative, A.AdditionalFreeText, MIN(A.CH) AS MinCH, B.StudioImages, A.CVehicle_ShortModText,A.Source,A.[type] FROM vwAllMatrixWithLombardAndShortModel AS A LEFT JOIN dbPubMatrix.dbo.tblCommImages AS B ON A.CapID = B.CAPID WHERE A.VehicleRef = 12861 AND A.Term = 3 AND A.MilesPA = 10000 AND a.[Type]='commercial' GROUP BY A.VehicleRef, A.CapID, A.Manufacturer, A.Model, A.Derivative, A.AdditionalFreeText, B.StudioImages,A.CVehicle_ShortModText,A.Source,A.[type]

I get

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Both sides of the UNION execute fine on thier own.

Thanks

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-02-26 : 08:43:31

UNION returns a DISTINCT result - UNION ALL does not,
Change the query to UNION ALL and see if it works.

if each side of the union was run with select distinct then you would get the same error.

Duane.