From: "Robert Comer" 

Found it!  Made my Friday.

It wasn't the SQL statement or the stored procedure that was the problem,
but thanks Ellen, I'm putting that one in my bag of tricks for later use.

It was not setting up the proper CursorDriver (should have been rdUseODBC,
instead it defaulted somehow to rdUseIfNeeded in this instance.)

- Bob Comer 


"Robert Comer"  wrote in
message news:40e56598{at}w3.nls.net...
> Okay, I got that in but the darn thing still gets the same error when
> called, it's got to be a coding issue in the app, probably something not
set
> up correctly.  Maybe a default change or something as it works with an
older
> ODBC driver. :(
>
> Just a little background, it's a VB5 (originally) using an ODBC connection
> to a SQL Server 6.5, which in turn ODBC's to our AS/400.  This particular
> part isn't touching the AS/400 at all.
>
> - Bob Comer
>
> "Ellen K"  wrote in message
> news:509841.e20b14{at}harborwebs.com...
> > I'm here via Dale's mirror, got stuck in sleep disconnect and now can't
> get
> > back to your message, but here's my reply:
> >
> > If you mean it all has to look like one table, that's easy, just make a
> derived
> > table out of it:
> > SELECT x.* FROM
> >   (SELECT w.WRKNO
> >   , w.RELNO
> >   , c.CO_ID
> >   , c.LOTID
> >   , s.COLOR
> >   , CONVERT(char(6), w.WRKNO) + '  ' + c.LOTID + '  ' +
SFC_Colors.COLOR)
> AS
> > LISTCOL
> >   FROM WorkOrder w
> >   INNER JOIN CombOrder c
> >   ON w.WO_ID = c.WO_ID
> >   LEFT JOIN SFC_Colors s
> >   ON c.COLOR_ID = s.COLOR_ID
> >   WHERE w.ACTIVE = 1
> >   AND c.ACTIVE = 1) x
> > ORDER BY x.WRKNO, x.LOTID
> >
> > (You can't put the ORDER BY in the definition of a derived table.)
> >
> > Note that a derived table is something SQL Server really likes and can
use
> very
> > efficiently, it's not at all the same thing as a subquery in terms of
> > performance.
> >
> > Whether you can open a cursor on it is another question though.
> Personally I
> > don't use cursors so I'm not knowledgable about their ins and outs.
> Cursors
> > are considered not really "SQL-ish" (for lack of a
better word) and are
> also
> > reputed to have a really bad effect on performance.  Are you sure a
cursor
> is
> > the best way to solve your problem?
> >
> > P.S.  I'm writing this from Montreal, this is the first time I've
checked
> in in
> > several days... I feel bad that I wasn't there when you first asked your
> > question.
>
>

--- BBBS/NT v4.01 Flag-5