OK

You have a Foxpro table that you want to import into SQL Server then use the SQL Server table to filter the SELECT for another foxpro table?
Why not just filter on the foxpro side only? otherwise you will have to link back to Foxpro from SQL Server

Have a look at this for lookups

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)


The DDQ is really at it's best when you want to possbly UPDATE,DELETE or INSERT from your destination based on records in the source.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"SWu" <swu (AT) ridleygroup (DOT) com.au> wrote

Hi,

I can't seem to find to-the-point, easy-to-follow instructions for this.

1. My DTS package imports some records from a FoxPro table to a matching SQL 2000 table.
2. I want to select from this SQL2000 table to filter what records I get from another FoxPro table.
3. I have tried to do this with a Data Driven Query Task.

Questions:
1. Is a Data Driven Query Task suitable for this?
2. If so, how do you set up the Data Driven Query Task?

Notes:
1. On the "Source" tab, I assume this means the FoxPro table containing the data. I used a query like:

"SELECT column1, column2, column3 FROM table2 WHERE (transid = ?)".

2. I presume that I can fill the "?" in the source with a lookup but am not sure how to do this. I tried creating a lookup connected to SQL like:

"SELECT transid FROM Table1 WHERE ProcessedFlag = 0".

In other words, I assume that the Lookup is used to filter what comes out of FoxPro.

3. I specify an INSERT statement in the "Queries" tab like:

"INSERT INTO Table2(transid, accountid, deptid) VALUES(?, ?, ?)".

I presumed that these question marks are supposed to filled by the "Source" query.

4. When I run this, I get an error message saying:

"[Microsoft][ODBC Visual FoxPro Driver]Wrong number of parameters".

Thanks in advance,
Stephen

thanks. I've since decided to bring everything across and filter once everything's in SQL Server. I was having all sorts of trouble trying to filter FoxPro on a date, trying use input parameters, etc.

the LookUp article will still be useful to know, thanks!
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

OK

You have a Foxpro table that you want to import into SQL Server then use the SQL Server table to filter the SELECT for another foxpro table?
Why not just filter on the foxpro side only? otherwise you will have to link back to Foxpro from SQL Server

Have a look at this for lookups

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)


The DDQ is really at it's best when you want to possbly UPDATE,DELETE or INSERT from your destination based on records in the source.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"SWu" <swu (AT) ridleygroup (DOT) com.au> wrote

Hi,

I can't seem to find to-the-point, easy-to-follow instructions for this.

1. My DTS package imports some records from a FoxPro table to a matching SQL 2000 table.
2. I want to select from this SQL2000 table to filter what records I get from another FoxPro table.
3. I have tried to do this with a Data Driven Query Task.

Questions:
1. Is a Data Driven Query Task suitable for this?
2. If so, how do you set up the Data Driven Query Task?

Notes:
1. On the "Source" tab, I assume this means the FoxPro table containing the data. I used a query like:

"SELECT column1, column2, column3 FROM table2 WHERE (transid = ?)".

2. I presume that I can fill the "?" in the source with a lookup but am not sure how to do this. I tried creating a lookup connected to SQL like:

"SELECT transid FROM Table1 WHERE ProcessedFlag = 0".

In other words, I assume that the Lookup is used to filter what comes out of FoxPro.

3. I specify an INSERT statement in the "Queries" tab like:

"INSERT INTO Table2(transid, accountid, deptid) VALUES(?, ?, ?)".

I presumed that these question marks are supposed to filled by the "Source" query.

4. When I run this, I get an error message saying:

"[Microsoft][ODBC Visual FoxPro Driver]Wrong number of parameters".

Thanks in advance,
Stephen