Error selecting text from a SQL database

Hello,
I have an application that used an access database and I am currently
migrating to SQL Server. The software is designed to use Odbc object
found in the .Net framework version 1.1. I have a simple select
statement that is driving me nuts. It works fine with the Access
databases but dies under SQL Server.
The statement is:
SELECT
settingValue
FROM
tblSettings
WHERE ( owner=@.owner and setting=@.setting )
settingValue is an ntext field and owner and setting are varchar fields.
When I try to execute the query I get an error:
ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
ntext, and image data types cannot be compared or sorted, except when
using IS NULL, or LIKE operator.
There are no indexes on any of the fields so I cannot figure out what
the problem is.
Mageos
Try this to see if you still get an error:
SELECT
owner,
setting,
settingValue
FROM
tblSettings
WHERE owner = @.owner
and setting=@.setting
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mageos" <matt.dot.osborne@.cox.dot.com> wrote in message
news:vbGyd.2281$wZ2.581@.newssvr13.news.prodigy.com ...
> Hello,
> I have an application that used an access database and I am currently
> migrating to SQL Server. The software is designed to use Odbc object
> found in the .Net framework version 1.1. I have a simple select
> statement that is driving me nuts. It works fine with the Access
> databases but dies under SQL Server.
> The statement is:
> SELECT
> settingValue
> FROM
> tblSettings
> WHERE ( owner=@.owner and setting=@.setting )
> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
> There are no indexes on any of the fields so I cannot figure out what
> the problem is.
> Mageos
|||> settingValue is an ntext field and owner and setting are varchar fields.
> When I try to execute the query I get an error:
> ERROR [42000][Microsoft][ODBC SQL Server Driver][SQL Server]The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL, or LIKE operator.
The error tells you exactly where the problem is. You are trying to do a
compare on an ntext field. Double check your create table statement. I think
you will find that either owner or setting is not of the datatype you
expected. What method of creating the tables in SQL did you use. I find that
often when I let SQL help me out converting data from an outside source, it
comes up with insane assumptions about what the datatype needs to be.
I tried reproducing the error with the script below and it works fine for
me.
create table test(
settingvalue ntext,
owner varchar(5),
setting varchar(5)
)
insert test (settingvalue, owner, setting)
values ('jgdfkjgiugsv8ohyohfef mne hevbe vmnerv', '1', '2')
declare @.owner varchar(5)
declare @.setting varchar(5)
set @.owner = '1'
set @.setting ='2'
select * from test where owner = @.owner and setting = @.setting