VS2017 and SSRS2016 – Problem parameter and Oracle database 11g

  • Hi, I have 1 server with Oracle 11g (11.1), and other server with SQL 2016 / SRSS 2016, when I try to create Dataset
    on my report, I have this error.
    I try to install ODAC 32b and 64b, but same error, maybe error with old OraOLEDB provider.
    I change my parameter with @ by :, on my old SRSS 2008 working fine. But on new not.

    VS20017 dataset this working fine

    SELECT   *
    FROM    CMS.INVN_SBS
    WHERE   sbs_no = 1
    ORDER BY DESCRIPTION1

    Error when I add parameter (int value)


    SELECT   *
    FROM    CMS.INVN_SBS
    WHERE   sbs_no = :Subsidiary
    ORDER BY DESCRIPTION1

    Error on VS2017
    ORA-00936: missing expression
    ORA-00936: missing expression

    Error On localhost/Reports/
    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset 'DSItems'. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Any Advice...

    GT

  • Parameters in SSRS are NOT quite the same way you might use them in PL/SQL.   You would be far better served by having the oracle database have a Stored Procedure that accepts parameters and then at worst, a text query that uses EXECUTE and puts the parameter in to the query in a PL/SQL way, but using an expression for the query to properly build the query string using expression elements that will add the parameter value in the correct place in the string.   Be sure to observe quoting any string valued parameters as part of that query.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • gilbertojavier2 - Wednesday, August 1, 2018 11:23 AM

    Hi, I have 1 server with Oracle 11g (11.1), and other server with SQL 2016 / SRSS 2016, when I try to create Dataset
    on my report, I have this error.
    I try to install ODAC 32b and 64b, but same error, maybe error with old OraOLEDB provider.
    I change my parameter with @ by :, on my old SRSS 2008 working fine. But on new not.

    VS20017 dataset this working fine

    SELECT   *
    FROM    CMS.INVN_SBS
    WHERE   sbs_no = 1
    ORDER BY DESCRIPTION1

    Error when I add parameter (int value)


    SELECT   *
    FROM    CMS.INVN_SBS
    WHERE   sbs_no = :Subsidiary
    ORDER BY DESCRIPTION1

    Error on VS2017
    ORA-00936: missing expression
    ORA-00936: missing expression

    Error On localhost/Reports/
    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset 'DSItems'. (rsErrorExecutingCommand)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    Any Advice...

    That's the correct format and what they show you in the tutorials - just replace the @ with :
    The missing expressions error does happen with some versions of ODAC but I don't follow it enough to know which ones you should use.
    I think it was 12c release 4 that was one of the versions that worked. I had read about the issues up on the Oracle forums so you may want to check for people hitting that same error with parameters up on those forums. It wasn't limited to just SSRS but the issue was reported for SSRS as well as others. Try searching on that ora error and ODAC.

    Sue

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply