473,703 Members | 2,922 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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,
RevisionReasonC ode = RSN_CODE,
RevisionGroup = RVSN_GRP_ID,
RevisedField = (select L.FieldID
from tempdb.guest.lk pRevisedField L
where L.TableID = R.RVSN_TYPE
and L.FieldName = R.CHNG_FLD),
RevisedValue = OLD_FLD_VAL,
RevisionTimesta mp = RVSN_TIMESTAMP
from RVSN R,
tempdb.guest.Ma xTimeStamp TS
where R.RVSN_TIMESTAM P TS.Rtimestamp
and R.RVSN_TIMESTAM P is NOT NULL

John H.

Oct 12 '06 #1
1 1497
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*********@gma il.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,
RevisionReasonC ode = RSN_CODE,
RevisionGroup = RVSN_GRP_ID,
RevisedField = (select L.FieldID
from tempdb.guest.lk pRevisedField L
where L.TableID = R.RVSN_TYPE
and L.FieldName = R.CHNG_FLD),
RevisedValue = OLD_FLD_VAL,
RevisionTimesta mp = RVSN_TIMESTAMP
from RVSN R,
tempdb.guest.Ma xTimeStamp TS
where R.RVSN_TIMESTAM P TS.Rtimestamp
and R.RVSN_TIMESTAM P is NOT NULL

John H.
Oct 13 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2131
by: GO | last post by:
I have a custom Perl application (programmed by myself) that is used to rename files. For some reason, within the last month, it is no longer working properly. Prior to this I've been using it for several years without problem. It's a relatively simple program that reads the file names from the current directory and replaces strings of text issued from command-line parameters. But now, for some unknown reason, it looks in the root of...
0
14999
by: Murali | last post by:
Hi All I was reading thro the posting(s) of Thomas Kyte and his nifty approach to doing updates without the need for unnecessary correlated subqueries. An alternative to correlated subquery using this technique is: update ( select columnName, value from name, lookup
2
14672
by: coryjflynn | last post by:
I am try to update the Gender field for all females of a database with about 15,000 records. So how I started was by searching baby girl names on the web and manipulated some of there lists to create my own table of female first names (HSNames). I can then use this table to update the Gender field to 'F' (female) in the master table. This process works great using the following query... Update HSContacts Set Gender = 'F' Where...
7
9218
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into a table (c) re-queries another table using a subquery which references the inserted table (correlated or not)
2
3016
by: edself | last post by:
Greetings, I am semi-new to Access and have a query question. I presume the solution is easy, but need some help. I have created a database with a Contact table. The contact table contains address fields among other things. Because some contacts share the same address, I included a boolean field, PrimaryContact. If true, a given contact's record contains the address info for that contact. If PrimaryContact is false, then another...
8
19595
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
1
7197
by: Tim Marshall | last post by:
A2003. I am getting this error message when I try to set a report's recordsource to an SQL statement or a saved querydef that uses sub-queries. I've debug.printed the SQL, and run it as a stand alone query, as a rowsource for a list box and as a recordsource for a form. There's no problem. I'm only encountering this in reports. As a stop gap, I'm going to use a form for a printe report, bvut I wonder if anyone has encountered...
5
6587
by: Rod | last post by:
I have a client site where the code below has been working happily for at least four months. The site is using SQL Server 7. The code is ASP.NET Last week an error appeared related to the following SQL statement. INSERT INTO OrderItems (ClientID, ProductID, OrderHeaderID, Quantity, Dispatched, BackOrdered) SELECT ClientID, ProductID, 1371 AS OrderHeaderID, Quantity, Dispatched, BackOrdered FROM Basket WHERE RequisitionID = 1369 The...
1
7754
NeoPa
by: NeoPa | last post by:
Access QueryDefs Mis-save Subquery SQL Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "" and (often) add an extraneous "." after it. For instance, a simple example illustrates how it works : SELECT subA.* FROM (SELECT , FROM ) AS subA becomes (after saving as a querydef in Access) : SELECT subA.*
0
9239
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9102
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8951
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5920
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4674
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2424
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2055
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.