Using Global parameter to extract data from non SQL Server DBs

#1

I am trying to add a parameter to a SQL query for a data
transform but am having no success. Basically, I would
like to limit the data I extract from a data source to
only the data entered into the source since the last
download (seems logical). To do this, I thought I would
use a date parameter in my SQL Query. somehting like:

Select col1, col2 col3
from source_tablename
where last_update_date = ?

This works when I run the cahnge/save and run the
transform against a SQL Server database but when I try to
save the query to run against a different database, using
an ODBC DSN I get an error that the "?" isn't recognized.

Is there another way to extract a subset of data from a
data source other than using parameters.

I am using SQL Server 2000. The other data bases are
Access and TopSpeed (Don't ask)

TIA

#2

In article <6cdb01c3586c$b6072660$a001280a (AT) phx (DOT) gbl>, Julie
<julie.rodgers (AT) mapinfo (DOT) com> writes

Quote:

I am trying to add a parameter to a SQL query for a data
transform but am having no success. Basically, I would
like to limit the data I extract from a data source to
only the data entered into the source since the last
download (seems logical). To do this, I thought I would
use a date parameter in my SQL Query. somehting like:

Select col1, col2 col3
from source_tablename
where last_update_date = ?

This works when I run the cahnge/save and run the
transform against a SQL Server database but when I try to
save the query to run against a different database, using
an ODBC DSN I get an error that the "?" isn't recognized.

Is there another way to extract a subset of data from a
data source other than using parameters.

I am using SQL Server 2000. The other data bases are
Access and TopSpeed (Don't ask)

TIA

To use parameters you ODBC/OLE-DB driver must fully implement parameter
support, part of the ODBC/OLE_DB driver specification. Of course not all
vendors fully implemented all features in their drivers, so maybe
TopSpeed is on of those. Access should be fine though.

The alternative is to code the full SQL statement, and change the entire
statement dynamically at run-time based on a variable value-

Global Variables and SQL statements in DTS
http://www.sqldts.com/default.aspx?6,102,205,7,1

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

#3

I have more information.

I tried to create an import that uses a SQL query from an
Access database. I create the query and add a parameter to
the where clause, i.e. "where date >= ?"
When I go into the properties for the task and try to open
the Parameters control, I get the following error:

"Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description: Provider cannot derive parameter
information and SetParameterInfo has not been called"

I am using SQL Server 2000 and Access 2000.

I've read the previous reply, but I am afraind I do not
know where I would go about editing the SQL statement as
suggested. I thought I would try some more things with
Access, since it was suggested that Access should work.

Thanks Again

Quote:

-----Original Message-----
I am trying to add a parameter to a SQL query for a data
transform but am having no success. Basically, I would
like to limit the data I extract from a data source to
only the data entered into the source since the last
download (seems logical). To do this, I thought I would
use a date parameter in my SQL Query. somehting like:

Select col1, col2 col3
from source_tablename
where last_update_date = ?

This works when I run the cahnge/save and run the
transform against a SQL Server database but when I try to
save the query to run against a different database, using
an ODBC DSN I get an error that the "?" isn't recognized.

Is there another way to extract a subset of data from a
data source other than using parameters.

I am using SQL Server 2000. The other data bases are
Access and TopSpeed (Don't ask)

TIA
.

#4

OK

So it seems the Access 2K driver gets a little confused. Here is what I did

1. I did not create my Global variable before the DataPump task
2. Source in Datapump task looks like

SELECT *
FROM tblReports
WHERE (ReportID >=
?)

3. I hit the parameters button.
4. I create a new Global Variable
5. assign to the ?
6. I now get your error on "Preview"

The datapump task still works as expected though.

--

----------------------------
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

"Julie" <julie.rodgers (AT) mapinfo (DOT) com> wrote

Quote:

I have more information.

I tried to create an import that uses a SQL query from an
Access database. I create the query and add a parameter to
the where clause, i.e. "where date >= ?"
When I go into the properties for the task and try to open
the Parameters control, I get the following error:

"Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description: Provider cannot derive parameter
information and SetParameterInfo has not been called"

I am using SQL Server 2000 and Access 2000.

I've read the previous reply, but I am afraind I do not
know where I would go about editing the SQL statement as
suggested. I thought I would try some more things with
Access, since it was suggested that Access should work.

Thanks Again
-----Original Message-----
I am trying to add a parameter to a SQL query for a data
transform but am having no success. Basically, I would
like to limit the data I extract from a data source to
only the data entered into the source since the last
download (seems logical). To do this, I thought I would
use a date parameter in my SQL Query. somehting like:

Select col1, col2 col3
from source_tablename
where last_update_date = ?

This works when I run the cahnge/save and run the
transform against a SQL Server database but when I try to
save the query to run against a different database, using
an ODBC DSN I get an error that the "?" isn't recognized.

Is there another way to extract a subset of data from a
data source other than using parameters.

I am using SQL Server 2000. The other data bases are
Access and TopSpeed (Don't ask)

TIA
.

#5

Certainly my driver is

4.00.6200.00 22/7/2002

--

----------------------------
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

"Julie" <julie.rodgers (AT) mapinfo (DOT) com> wrote

Quote:

Thanks for your reply. I followed the steps as you stated.
Unfortunately, it's still not working for me. Do you know
which version of the Access driver you are using? I am
using 4.00.4403.02. From the date of the file - it's
pretty old - 12/7/99(Although I am using Access 2000)

Thanks again!
-----Original Message-----
OK

So it seems the Access 2K driver gets a little confused.
Here is what I did

1. I did not create my Global variable before the
DataPump task
2. Source in Datapump task looks like

SELECT *
FROM tblReports
WHERE (ReportID >=
?)

3. I hit the parameters button.
4. I create a new Global Variable
5. assign to the ?
6. I now get your error on "Preview"

The datapump task still works as expected though.

--

----------------------------
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

"Julie" <julie.rodgers (AT) mapinfo (DOT) com> wrote in message
news:033501c35acf$30d2fe50$a501280a (AT) phx (DOT) gbl...
I have more information.

I tried to create an import that uses a SQL query from
an
Access database. I create the query and add a parameter
to
the where clause, i.e. "where date >= ?"
When I go into the properties for the task and try to
open
the Parameters control, I get the following error:

"Error Source: Microsoft OLE DB Provider for ODBC
Drivers
Error Description: Provider cannot derive parameter
information and SetParameterInfo has not been called"

I am using SQL Server 2000 and Access 2000.

I've read the previous reply, but I am afraind I do not
know where I would go about editing the SQL statement as
suggested. I thought I would try some more things with
Access, since it was suggested that Access should work.

Thanks Again
-----Original Message-----
I am trying to add a parameter to a SQL query for a
data
transform but am having no success. Basically, I would
like to limit the data I extract from a data source to
only the data entered into the source since the last
download (seems logical). To do this, I thought I would
use a date parameter in my SQL Query. somehting like:

Select col1, col2 col3
from source_tablename
where last_update_date = ?

This works when I run the cahnge/save and run the
transform against a SQL Server database but when I try
to
save the query to run against a different database,
using
an ODBC DSN I get an error that the "?" isn't
recognized.

Is there another way to extract a subset of data from a
data source other than using parameters.

I am using SQL Server 2000. The other data bases are
Access and TopSpeed (Don't ask)

TIA
.

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Using Global parameter to extract data from non SQL Server DBs

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Using Global parameter to extract data from non SQL Server DBs - 08-01-2003 , 03:37 PM

Re: Using Global parameter to extract data from non SQL Server DBs - 08-02-2003 , 05:39 AM

Additional Info: Using Global parameter to extract data from non SQL Server DBs - 08-04-2003 , 04:27 PM

Re: Additional Info: Using Global parameter to extract data from non SQL Server DBs - 08-05-2003 , 01:16 AM

Re: Additional Info: Using Global parameter to extract data from non SQL Server DBs - 08-06-2003 , 01:22 AM