August 1, 2018 at 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...
GT
August 7, 2018 at 12:15 pm
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)
August 7, 2018 at 1:42 pm
gilbertojavier2 - Wednesday, August 1, 2018 11:23 AMHi, 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 DESCRIPTION1Error when I add parameter (int value)
SELECT *
FROM CMS.INVN_SBS
WHERE sbs_no = :Subsidiary
ORDER BY DESCRIPTION1Error on VS2017
ORA-00936: missing expression
ORA-00936: missing expressionError 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 errorsAny 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