Subquery in DTS No Longer Works After Years

[Docster2005]

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.

[Stu]

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?
Do*********@gmail.com 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.