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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy