From: "Robert Comer" 

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