[dba-SQLServer]Using DTS to get data from SQL to Access

Arthur,

You can do this using Active X scripts which lets you pass parameters.
It's very slick.  I haven't actually done it yet.  We are going over
this right now in a SQL Server User Group I'm in.  I have some stuff I
can send you, but I'm not sure that it will make much sense.

--Susan


-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com] 
Sent: Wednesday, August 20, 2003 9:56 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Using DTS to get data from SQL to Access


On a related subject, I have a bunch of DTS tasks, and regularly create
new ones, to perform a similar operation. But what I do is stupid,
because it's all literal. Imagine a relational tree, and that I want to
pop the Access db with everything flowing down from the topmost table
where PK = ###.

What I currently do is copy one of my DTS tasks to a new name and then
edit its several steps. In sum, the steps look like this (edited for
brevity):

SELECT * FROM Tours WHERE TourID = 123
SELECT * FROM Events WHERE TourID = 123
SELECT * FROM EventPackages WHERE EventID IN( SELECT * FROM Events WHERE
TourID = 123 ) yada^3

Apparently there is a way to declare variables and pass in a value,
which given the uniformity of my tasks would be 100 times simpler. But
the NoHelp lives up to its name. Can anyone tell me how to do this? It
should look, according to the docs, something like:

SELECT * FROM Tours WHERE TourID = ?
SELECT * FROM Events WHERE TourID = ?
SELECT * FROM EventPackages WHERE EventID IN( SELECT * FROM Events WHERE
TourID = ? ) yada^3

And then all you do is assign a value to your variable and it works. But
I can't make it work.

Suggestions? Guidance? Spare change?

Arthur

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com