SQL Server Performance

oracl query support

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by PatDeV, May 9, 2007.

  1. PatDeV New Member

    Hi i am trying to query oracle from SSRS and so far successful but when i try to do this:

    select testname, testid
    from test
    where testname like '%'+upper(?) +'%'

    then get error saying that invalid number i know i could do something like that in SQL but having trouble in oracle!

    i am doing that so in parameter user don't have to put % !!

    thanks


    Thanks!!
  2. Adriaan New Member

    You need to write a query that complies to Oracle's version of SQL.

    Not sure what UPPER() does in Oracle.

    Pretty sure the question mark will cause a problem - AFAIK this is acceptable only in a constant expression, not in 'bare' script.
  3. PatDeV New Member

    ? is act as prameter just like @parameter!!

    Thanks!!
  4. Adriaan New Member

    Are you calling sp_msforeachtable or sp_msforeachdb? That's the only application of the question mark as a parameter I'm aware of, and I doubt that you could call that against an Oracle server.
  5. PatDeV New Member

    no i am calling on regular query with odbc connection to Oracle database!

    Thanks!!
  6. Adriaan New Member

    I know this is not what you're doing, but anyway --- when I execute your statement against SQL Server, I get the following error:

    [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error


    quote:then get error saying that invalid number i know i could do something like that in SQL but having trouble in oracle!
    Can't quite follow what you're saying here.
  7. satya Moderator

    http://www.developer.com/db/article.php/3524781 fyi on the aspect of getting data from oracle to RS.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page