dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: anyone know how to pass a global variable in a SQL DTS to a ORACLE data source?

  1. #1
    Join Date
    Apr 2005
    Posts
    3

    anyone know how to pass a global variable in a SQL DTS to a ORACLE data source?

    Hi. I administer a SQL Database, and at times I need to connect to an Oracle DB.
    I am trying to load some data via a DTS using the microsoft OLE DB provider for Oracle with a query as the source, because I only need recent records and the table I am connecting to is huge. I haven't been able to figure out how to pass a parameter (that will change in value at run time, over time). I know how to set the global variable with a dynamic properties task in the DTS, but I get the the SQL+ query to recognize this variable. In sql I would just set the where clause to a question mark, but that doesn't work with SQL+ apparently... This is the query i need to run:

    SELECT
    PMT.BILLING_ID as BILLING_ID ,
    PMT.ACCOUNT_NO as ACCOUNT_NO ,
    PMT.CONTRACT_ID as CONTRACT_ID,
    PMT.ACCOUNT_SUMMARY_ID as ACCOUNT_SUMMARY_ID,
    PMT.AMOUNT as AMOUNT ,
    PMT.ENTRY_DATE as ENTRY_DATE,
    NULL as AIR_AMOUNT,
    NULL as TOLL_AMOUNT
    FROM
    BCM_CONT_PAYMENTS PMT
    WHERE PMT.BILLING_ID =

    What do I need to set the where clause to recognize my global variable? The variable is currently called "test" but WHERE PMT.BILLING_ID = test doesn't work.

    Does anyone have a suggestion? It would be greatly appreciated, and would save me tons of performance time and space! Thanks.
    Yvette

    p.s. btw -- I am using SQL 2000.

  2. #2
    Join Date
    Apr 2005
    Posts
    3

    I answered my own question, just wanted to share

    Hi. The problem with my DTS was with the source driver. This is what I found on on msdn
    (http://msdn.microsoft.com/library/de...usage_3ayb.asp):

    "Because the Microsoft OLE DB Provider for Oracle does not support ICommandWithParameters, it cannot be used as the destination of a Data Driven Query task. When using this provider in DTS Designer, the Parameters buttons on a Transform Data task, Data Driven Query task, and Execute SQL task will be disabled. "

    Apparently, it isn't supported for the source either, they just don't divulge it in this library...

    SO, I changed the source connection to an Microsoft ODBC connection for Oracle instead of a OLE DB provider for Oracle, and it worked like a charm!

    Let me know if you have any questions about this, I noticed lots of visits to my post but no replies, so I figured someone else might find this info useful.
    Thanks.
    Yvette

  3. #3
    Join Date
    Apr 2008
    Posts
    1
    WE are also facing similer issue and the alternative of using ODBC connection looks ok but there are datatype like CLOB in Oracle which is only supported by "Oracle provider for OLEDB"...

  4. #4
    Join Date
    Feb 2008
    Posts
    161
    Have you tried using OPENQUERY as opposed to DTS? I am doing something very similar and it is working for me.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by Slope
    Have you tried using OPENQUERY as opposed to DTS? I am doing something very similar and it is working for me.
    Very cool!

    If you are like me and are not sure what Slope meant by OPENQUERY, have a look at OPENQUERY

  6. #6
    Join Date
    Feb 2008
    Posts
    161
    One minor problem with openquery is that you can't pass any variables in the sql parameter.

    So you can do this:

    SELECT *
    FROM OPENQUERY(myOracleLinkedServer, 'SELECT * FROM my_oracle_table WHERE my_string_col = ''ABC''')


    But you can't do this:

    SELECT *
    FROM OPENQUERY(myOracleLinkedServer, 'SELECT * FROM my_oracle_table WHERE my_string_col = ''' + @myStringVal + '''')


    And you can't do this:
    SELECT *
    FROM OPENQUERY(myOracleLinkedServer, @sql)


    So it would seem that the query has to be hard coded, which is bad if you need to call it for different values. But you can get around this by using sp_executesql:

    Code:
    DECLARE @sql         NVARCHAR(500)
    DECLARE @myStringVal NVARCHAR(3)
    
    SET @myStringVal = N'ABC'
    
    SET @sql = 
      N'SELECT *
        FROM OPENQUERY(myOracleLinkedServer,
        ''SELECT * 
          FROM my_oracle_table
          WHERE my_string_col = ''''' + @myStringVal + ''''''')'
    
    --EXEC sp_executesql @sql
    
    PRINT @sql
    It looks like an aweful lot of quotation marks, but if you print the @sql variable, you can see what the string is evaluating to. Then copy the output into another SQL window and try to execute until you get it working.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center