469,609 Members | 1,107 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

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.

Oct 12 '06 #1
1 1372
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.
Oct 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by coryjflynn | last post: by
2 posts views Thread by edself | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.