June 20, 2008 at 11:37 am
I have a straight forward select statement which happend to include a ntext column 'ResponseData'. When I try to run it I get:
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I have included the code below:
Select c.OrderGRoupNumber,c.CCLogID,
c.ConsultantID,
C.OrderID
,c.[UserName]
,c.[CCProcessorXID]
,c.[TransactionTypeXID]
,c.[TSPaymentTypeXID]
,c.[RequestDate]
,c.[RequestDateXID]
,c.[RequestDateTimeXID]
,c.[CCName]
,c.[CClastFour]
,c.[Amount]
,c.[TransactionID]
,c.[ResponseDate]
,c.[ResponseDateXID]
,c.[ResponseDateTimeXID]
,c.[ResponseStatus]
,c.[ResponseCode]
,c.[AVSResponseCode]
,c.[ResponseText]
,c.[ResponseAuthorizationCode]
,c.[ResponseData]
,c.[XMLDocXID]
,c.[BatchID]
,c.[ChangeType],
MAX(c.XID)
FROM orders.cclog c
Inner Join orders.OrderGroup g ON c.OrderGroupNumber = g.OrderGroupNumber
WHERE g.OrderCreateDate > '05/06/2008' AND g.OrderCreateDate <= '2008-06-20 09:56:38.617'
GROUP BY c.OrderGRoupNumber,c.CCLogID,
c.ConsultantID,
C.OrderID
,c.[UserName]
,c.[CCProcessorXID]
,c.[TransactionTypeXID]
,c.[TSPaymentTypeXID]
,c.[RequestDate]
,c.[RequestDateXID]
,c.[RequestDateTimeXID]
,c.[CCName]
,c.[CClastFour]
,c.[Amount]
,c.[TransactionID]
,c.[ResponseDate]
,c.[ResponseDateXID]
,c.[ResponseDateTimeXID]
,c.[ResponseStatus]
,c.[ResponseCode]
,c.[AVSResponseCode]
,c.[ResponseText]
,c.[ResponseAuthorizationCode]
,c.[ResponseData]
,c.[XMLDocXID]
,c.[BatchID]
,c.[ChangeType]
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
June 20, 2008 at 12:03 pm
the error is saying you can't put such column in a "GROUP BY"
* Noel
June 20, 2008 at 12:07 pm
I found that if I CAST it as a varchar then it returns data but then the next question how will that effect the data. This query is the basis of a data source that will be used in a SSIS package that is moving data from one server to another. I am just wondering if I am going to have data issue if I use CAST.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
June 20, 2008 at 12:16 pm
Probably with varchar(max) you are ok. It will slow the query but maybe that is good enoug for what you want.
* Noel
June 20, 2008 at 12:33 pm
I did that and it worked. Now I make that query the source for my SSIS package and I recieve the following error:
"ResponseData" cannot convert between unicode and non-unicode string data types.
I CASTed them as NVARCHAR(MAX) and VARCHAR(MAX) and it gives me the same thing.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply