Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Colombia
    Posts
    6

    Question Unanswered: 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.00

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS Oracle Select

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

    hmscott

  3. #3
    Join Date
    Nov 2003
    Location
    Colombia
    Posts
    6

    Re: DTS Oracle Select

    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

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Re: DTS Oracle Select

    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)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •