Subquery in DTS No Longer Works After Years

Hi folks,

A DTS package we have run for years now no longer works. The specific
part that is not working is a subquery in the SOURCE object of a
transformation. The source is based on a Microsoft Data Link to a
Sybase database (DSN changed a couple months ago but the connection
string was updated successfully for the new 12.51 version of ASE) and
the destination is a link to a local SQL Server 2000 database.

The transformation has always worked and when I remove the subquery
everything works OK. The problem is that I need the subquery!

Does anyone have a clue what is going on?

Here is the full query.

select TableKey = RVSN_TYPE_ID,
TableCode = RVSN_TYPE,
RevisionDate = RVSN_DATE,
RevisionReasonCode = RSN_CODE,
RevisionGroup = RVSN_GRP_ID,
RevisedField = (select L.FieldID
from tempdb.guest.lkpRevisedField L
where L.TableID = R.RVSN_TYPE
and L.FieldName = R.CHNG_FLD),
RevisedValue = OLD_FLD_VAL,
RevisionTimestamp = RVSN_TIMESTAMP
from RVSN R,
tempdb.guest.MaxTimeStamp TS
where R.RVSN_TIMESTAMP > TS.Rtimestamp
and R.RVSN_TIMESTAMP is NOT NULL

John H.

When you say "it doesn't work", what do you mean? Do you get no data,
or does it throw a syntax error? Does it just stall and hang?


wrote:
> Hi folks,
>
> A DTS package we have run for years now no longer works. The specific
> part that is not working is a subquery in the SOURCE object of a
> transformation. The source is based on a Microsoft Data Link to a
> Sybase database (DSN changed a couple months ago but the connection
> string was updated successfully for the new 12.51 version of ASE) and
> the destination is a link to a local SQL Server 2000 database.
>
> The transformation has always worked and when I remove the subquery
> everything works OK. The problem is that I need the subquery!
>
> Does anyone have a clue what is going on?
>
> Here is the full query.
>
> select TableKey = RVSN_TYPE_ID,
> TableCode = RVSN_TYPE,
> RevisionDate = RVSN_DATE,
> RevisionReasonCode = RSN_CODE,
> RevisionGroup = RVSN_GRP_ID,
> RevisedField = (select L.FieldID
> from tempdb.guest.lkpRevisedField L
> where L.TableID = R.RVSN_TYPE
> and L.FieldName = R.CHNG_FLD),
> RevisedValue = OLD_FLD_VAL,
> RevisionTimestamp = RVSN_TIMESTAMP
> from RVSN R,
> tempdb.guest.MaxTimeStamp TS
> where R.RVSN_TIMESTAMP > TS.Rtimestamp
> and R.RVSN_TIMESTAMP is NOT NULL
>
> John H.