• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 956
  • Last Modified:

SQL Server Data type error

Hi Experts,

I am facing an data type error while running below query. Please advise how to fix below error.

SELECT  Inquiries,[PO Number], max(Revision) AS MaxRevision
FROM [LinkedServer_1].[Database_1].dbo.CPOA  WITH(NOLOCK)
GROUP BY Inquiries ,[PO Number]

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

Data type:
---------
Inquiries varchar(max)
[PO Number] varchar(50)
Revision varchar(50)

Source server version:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Destination version (lnked server version):
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
0
sqldba2013
Asked:
sqldba2013
1 Solution
 
Pratima PharandeCommented:
I think Inquiries field is having text datatype

work around for this

SELECT
CAST(Inquiries AS NVARCHAR(1000)) Inquiries
 ,[PO Number], max(Revision) AS MaxRevision
FROM [LinkedServer_1].[Database_1].dbo.CPOA  WITH(NOLOCK)
GROUP BY CAST(Inquiries AS NVARCHAR(1000))  ,[PO Number]
0
 
sqldba2013Author Commented:
--
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now