DTS Oracle Select

Hi there,m

I have a DTS with an Oracle Connection. I transfers some data from oracle Db to SQL Server DB. I need convert my Oracle query into a parmerized query, i mean that the field [T1."DELV_SHIP_DATE_KEY"] can receive the dates like parameters.

I'd try to put:
T1."DELV_SHIP_DATE_KEY" between ? and ?

but it show me the next error when i try to set the parameter info:
Provider cannot deriver parameter information and
Setparameterinfo has not been called

Any idea??,
Maritzita

I use this select statement (for Oracle):
select T1."DELV_ID" "DELV_Delv Id",
T1."CUST_ID" "DELV_Cust Id",
T1."SHIP_CODE" "DELV_Ship Code",
T1."WHSE_ID" "DELV_Whse Id",
(T1."DELV_SHIP_TIME"*1.0e0)/100 "DELV_Delv Ship Time",
T1."DELV_SHIP_DATE_KEY" "DELV_Delv Ship Date Key",
T2."DELVI_ITEM_NO" "DELVI_Delvi Item No",
T2."ORD_ID" "DELVI_Ord Id",
T2."ORDI_ITEM_NO" "DELVI_Ordi Item No",
T2."DELVI_QTY" "DELVI_Delvi Qty",
T2."PROD_ID" "DELVI_Prod Id",
T2."DELVI_TOT_WGT" "DELVI_Delvi Tot Wgt",
T2."DELVI_TYPE_CODE" "DELVI_Delvi Type Code",
T3."ADR_CMPY_NAME1" "ADR_Adr Cmpy Name1",
T4."PROD_DESC_TEXT1" "PROD_Prod Desc Text1",
T4."PROD_ALT_CONV_FACTOR" "PROD_Prod Alt Conv Factor",
T5."ORDI_QTY" "ORDI_Ordi Qty",
T5."ORDI_ASM_GEN_ITEM_NO" "ORDI_Ordi Asm Gen Item No",
T5."ORDI_IS_UNIT_WGT" "ORDI_Ordi Is Unit Wgt",
T4."PROD_MKT_GROUP_CODE" "PROD_Prod Mkt Group Code"
from "VPEMGR"."DELV" T1,
"VPEMGR"."DELVI" T2,
"VPEMGR"."ADR" T3,
"VPEMGR"."ORDI" T5,
"VPEMGR"."PROD" T4
where T1."DELV_ID"=T2."DELV_ID" and
T1."ADR_ID"=T3."ADR_ID" and
T2."ORD_ID"=T5."ORD_ID" and
T2."ORDI_ITEM_NO"=T5."ORDI_ITEM_NO" and
T2."PROD_ID"=T4."PROD_ID" and
T2."DELVI_TYPE_CODE"='IQ' and
T1."DELV_SHIP_DATE_KEY" between '20030501' and '20031231' and
T1."DELV_SHIP_TIME"/100 between 0.00 and 24.00If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||I saw this solution on help from SQL, but i culdn't understand it so much. Can u tell me where i can find more information about it? and if i use this funcionality, how i have to do for pass this results of my query to my SQl database without using a data transformation task?

I have many quetions about it.

tks,
Maritzita

Originally posted by hmscott
If I understand the question correctly, I think that you will have better luck with OPENQUERY.

hmscott|||Look under OPENQUERY in SQL Books On Line. They give an example that specifically shows an Oracle Server. I think that the only thing you will have to do is build your Query String (with the substituted parameters) as a variable. I'm not positive, however, that this will work. Example:

DECLARE @.SQL Varchar(4000)

SELECT @.SQL =
'SELECT Columns
FROM MyTable
WHERE MyDate BETWEEN TO_DATE(' + '12/4/2003' + ') AND
TO_DATE(' + '12/5/2003' + ')'
GO

SELECT *
FROM OPENQUERY(OracleSvr, @.SQL)
GO