Board index » delphi » Problem upgrading Sql Server from 6.5 to 2000

Problem upgrading Sql Server from 6.5 to 2000

I've run into a show-stopping problem with this.

I have a 3-tier app which uses ADO (version 6, SP1) in the middle tier
and TClientDataSets in the client.  I'm using D5.01 with the Nov 2000
ADO service pack and MDac 2.7.

One table has the stucture
  ID Int not nul primary key,
  Folio varchar(20) null,
  Title text null

I have a TClientDataSet which references this as a detail (the middle
tier SQL is 'select * from titles where folio=:folio'). In the CDS I
have a 'folio' parameter (ftString, ptInput) defined.

Here's the problem: In the Delphi IDE, if I dbl-click the CDS and
attempt to add fields (I found this way of reproducing the error while
looking for a way that avoided my client executing), the middle tier
RDM activates and returns the error:

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

Watching what's going on in the MS Query Analyzer, the Sql the middle
tier sends to the server actual specifies the parameter type as text,
not varchar, so it's not surprising I get the error message.

I get the same error if I use a TAdoQuery as the dataset of a local
providerin my client.  Weirdly, if I set the Parameter DataType to the
wrong type, e.g. ftTime or ftInteger, my client app works fine; if I
set it to ftString or ftWideString then I get the above error.

Any ideas?

Cheers, Martyn

 

Re:Problem upgrading Sql Server from 6.5 to 2000


Quote
"Martyn Ayers" <100031.2167atsigncompuserve.com> wrote in message

news:r843cussvlhjl6990us26a13fqn6nnl7bd@4ax.com...

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

I've been trying to get an answer to this question myself. Nobody else
seemed to have the same problem (well, except you now :), so after some
time, I decided to wait no longer and changed my query to use LIKE. Of
course, I also added an index to the field like your folio. The error went
away. Naturally, this is not a perfect solution (not a solution at all), but
helped me move on with my projects.

I hope you'll be of better luck. If you find a solution, please post it back
here.

Also, I'd recommend you posted n-tier questions in
borland.public.delphi.database.multi-tier as it's more on-topic that this
group.

rb

Re:Problem upgrading Sql Server from 6.5 to 2000


rb,

Thanks for your interest.

I found a solution to this that is almost embarassingly simple, which
is just to set PacketRecords = 0.  Then, the IDE fields editor behaves
as expected and my app has been running fine.

After I discovered this by trial and error, I found a couple of
threads in here via Google about M->Details and setting PacketRecords
to zero.  One poster wrote in terms that were almost "Everyone knows
that ..".  Well, I certainly didn't.

Cheers, Martyn

Re:Problem upgrading Sql Server from 6.5 to 2000


Quote
"Martyn Ayers" <100031.2167atsigncompuserve.com> wrote in message

news:vgv5cukclh1een54067j17boqb5inpf51m@4ax.com...

Quote

> I found a solution to this that is almost embarassingly simple, which
> is just to set PacketRecords = 0.  Then, the IDE fields editor behaves
> as expected and my app has been running fine.

Hmm, I tried this before and the error didn't go away. It must be something
else than PacketRecords. I wasn't lazy, I even went on to change the table
and set the field type to integer. The query didn't change and yet I got no
errors. Put the field back to char/varchar - wham!

I guess, my "text/ntext" error user group shrunk down to 1 :)

Thanks a lot.

rb

Re:Problem upgrading Sql Server from 6.5 to 2000


rb,

Quote
>I guess, my "text/ntext" error user group shrunk down to 1 :)

In case it helps any, I thought I'd describe my set-up.  Server is
NT4SP6A with Sql Server 2000 Developer's edition on it.  Client and
middle tier in Win2k SP2 with MDac 2.7. Midas version is 5.0.6.18.

The middle tier app is slightly unusual in that it uses exclusively
TAdoQueries created at RDM-creation time from a config file.  The
relevant one is loaded with the Sql quoted in my earlier msg and does
not have create any TParameters created; it uses a client-side cursor.

I stumbled on the PacketRecords business fiddling with a number of
minimalist middle-tier servers and clients with and without
middle-tier persistent TParameters, and with the M->D relationship set
in the middle tier or on the client.  The set-up that works has the
M->D set up only in the client.

I'm still not convinced that I've got to the bottom of the problem,
but at least I can turn it on and off at will with the PacketRecords
value and it seems at least plausible that with the middle tier and
client set up as I've described, the middle tier resolves the
parameters as and when they're needed and that somegow sidesteps
whatever quirk whereever it is that otherwise gives rise to the
parameter type error.  Not much of a theory, admittedly.

Cheers, Martyn

Re:Problem upgrading Sql Server from 6.5 to 2000


Thanks a lot. I'll certainly try to compare your setup with mine, where
applicable of course.

Thanks once again.

rb

Quote
"Martyn Ayers" <100031.2167atsigncompuserve.com> wrote in message

news:npo8cu49749os1g0ev783lqamnkk6vqigs@4ax.com...
Quote
> In case it helps any, I thought I'd describe my set-up.  Server is
> NT4SP6A with Sql Server 2000 Developer's edition on it.  Client and
> middle tier in Win2k SP2 with MDac 2.7. Midas version is 5.0.6.18.

> I stumbled on the PacketRecords business fiddling with a number of
> minimalist middle-tier servers and clients with and without
> middle-tier persistent TParameters, and with the M->D relationship set
> in the middle tier or on the client.  The set-up that works has the
> M->D set up only in the client.

Other Threads