Posted by Docster2005 on 10/12/06 12:50

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.

• Next in forum: Re: Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?
• Prev in forum: Re: table copy
• Thread view: Subquery in DTS No Longer Works After Years

[Reply to this message]