Oracle Stored Procedure as datasource

I cannot get an Oracle stored procedure to work as my datasource to save my
life...
I purchased Professional SQL Server Reporting Services book from Wrox -
looking at p.115. Followed instructions exactly - but am obviously missing
something. It's not working.
In the Solution Explorer, I right-clicked "Reports" and chose "Add New
Item". Chose "Report" and named the report "Test_Parcel".
On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
Dataset dialogue appeared.
1) I named the new dataset "rebaproc" (reba is the name of the Oracle
server),
2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
I've been using for other reports - that are built on simply queries
accessing Oracle views),
3) chose "StoredProcedure" in the Command Type dropdown, and
4) typed the name of the stored procedure - sp_Test_Parcel,
5) clicked "OK"
As soon as I click "OK", I get the error:
"Could not generate a list of fields for the query. Check the query syntax
or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
The only two "IN" parameters are:
pnumUPCId IN NUMBER, /* UPC_ID to read
*/
pnumParcelId IN NUMBER, /* Parcel_Id to read
*/
When I click "Refresh Fields", RS pops up a dialog (the "Define Query
Parameters" dialog) which asks for parameter values for the pnumUPCId and
pnumParcelId. I enter values, and click OK, and get the same error
referenced above.
Not at all sure what I'm supposed to be doing... I even tried manually
adding "Report Parameters", but same issue...
Please help.
ThanksI had this same exact issue and finally got it to work. When you hit refresh
fields and it pops up with your two parameters try selecting...I think it is
the NULL at the top of the dropdown instead of actually typing in values.
Once you do that it will finally create your parameters in your report from
your stored procedure. I read the same book you did and followed
instructions on the internet just like the book and had the same results you
did - I too am using oracle. Once it created the parameters in the report it
got rid of the parameter error. If you can't get it to create them the first
time of doing this try it again...it seems to be a bit crazy and one time
took me 3 times of selecting the null to get it to work. Good Luck ;-)
Melissa
"Derek in Richmond" wrote:
> I cannot get an Oracle stored procedure to work as my datasource to save my
> life...
> I purchased Professional SQL Server Reporting Services book from Wrox -
> looking at p.115. Followed instructions exactly - but am obviously missing
> something. It's not working.
> In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> Item". Chose "Report" and named the report "Test_Parcel".
> On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> Dataset dialogue appeared.
> 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> server),
> 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> I've been using for other reports - that are built on simply queries
> accessing Oracle views),
> 3) chose "StoredProcedure" in the Command Type dropdown, and
> 4) typed the name of the stored procedure - sp_Test_Parcel,
> 5) clicked "OK"
> As soon as I click "OK", I get the error:
> "Could not generate a list of fields for the query. Check the query syntax
> or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> The only two "IN" parameters are:
> pnumUPCId IN NUMBER, /* UPC_ID to read
> */
> pnumParcelId IN NUMBER, /* Parcel_Id to read
> */
> When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> Parameters" dialog) which asks for parameter values for the pnumUPCId and
> pnumParcelId. I enter values, and click OK, and get the same error
> referenced above.
> Not at all sure what I'm supposed to be doing... I even tried manually
> adding "Report Parameters", but same issue...
> Please help.
> Thanks
>|||Thanks for responding Melissa. I tried what you suggested, but it still
doesn't seem to work.
---
"Melissa" wrote:
> I had this same exact issue and finally got it to work. When you hit refresh
> fields and it pops up with your two parameters try selecting...I think it is
> the NULL at the top of the dropdown instead of actually typing in values.
> Once you do that it will finally create your parameters in your report from
> your stored procedure. I read the same book you did and followed
> instructions on the internet just like the book and had the same results you
> did - I too am using oracle. Once it created the parameters in the report it
> got rid of the parameter error. If you can't get it to create them the first
> time of doing this try it again...it seems to be a bit crazy and one time
> took me 3 times of selecting the null to get it to work. Good Luck ;-)
> Melissa
> "Derek in Richmond" wrote:
> > I cannot get an Oracle stored procedure to work as my datasource to save my
> > life...
> >
> > I purchased Professional SQL Server Reporting Services book from Wrox -
> > looking at p.115. Followed instructions exactly - but am obviously missing
> > something. It's not working.
> >
> > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > Item". Chose "Report" and named the report "Test_Parcel".
> >
> > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > Dataset dialogue appeared.
> >
> > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > server),
> > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > I've been using for other reports - that are built on simply queries
> > accessing Oracle views),
> > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > 5) clicked "OK"
> >
> > As soon as I click "OK", I get the error:
> > "Could not generate a list of fields for the query. Check the query syntax
> > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> >
> > The only two "IN" parameters are:
> > pnumUPCId IN NUMBER, /* UPC_ID to read
> > */
> > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > */
> >
> > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > pnumParcelId. I enter values, and click OK, and get the same error
> > referenced above.
> >
> > Not at all sure what I'm supposed to be doing... I even tried manually
> > adding "Report Parameters", but same issue...
> >
> > Please help.
> >
> > Thanks
> >|||Sorry I wasn't any help. I am using the oracle OLE DB driver (not MS driver
for Oracle) so I guess that might have something to do with it...you
mentioned you are not. Here are my steps I went through which sound very
similar to yours:
1. Add existing datasource to project
2. Create New Report
3. New Dataset Name is test
4. Datasource hdbprod (same datasource I use in other reports)
5. Command Type StoredProcedure
6. Query String MAKE_ADDRESS_30DAY_TABLE
7. OK
8. OK on scary parameter error
9. Hit refresh fields button
10. Select NULL on two parameters (selecting empty won't work)
11. Report -> Report Parameters to verify two parameters were created
I'll look through my notes and see if there is anything else I can find.
Maybe someone else has some more experience with it.
Melissa
"Derek in Richmond" wrote:
> Thanks for responding Melissa. I tried what you suggested, but it still
> doesn't seem to work.
> ---
> "Melissa" wrote:
> > I had this same exact issue and finally got it to work. When you hit refresh
> > fields and it pops up with your two parameters try selecting...I think it is
> > the NULL at the top of the dropdown instead of actually typing in values.
> > Once you do that it will finally create your parameters in your report from
> > your stored procedure. I read the same book you did and followed
> > instructions on the internet just like the book and had the same results you
> > did - I too am using oracle. Once it created the parameters in the report it
> > got rid of the parameter error. If you can't get it to create them the first
> > time of doing this try it again...it seems to be a bit crazy and one time
> > took me 3 times of selecting the null to get it to work. Good Luck ;-)
> > Melissa
> >
> > "Derek in Richmond" wrote:
> >
> > > I cannot get an Oracle stored procedure to work as my datasource to save my
> > > life...
> > >
> > > I purchased Professional SQL Server Reporting Services book from Wrox -
> > > looking at p.115. Followed instructions exactly - but am obviously missing
> > > something. It's not working.
> > >
> > > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > > Item". Chose "Report" and named the report "Test_Parcel".
> > >
> > > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > > Dataset dialogue appeared.
> > >
> > > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > > server),
> > > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > > I've been using for other reports - that are built on simply queries
> > > accessing Oracle views),
> > > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > > 5) clicked "OK"
> > >
> > > As soon as I click "OK", I get the error:
> > > "Could not generate a list of fields for the query. Check the query syntax
> > > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> > >
> > > The only two "IN" parameters are:
> > > pnumUPCId IN NUMBER, /* UPC_ID to read
> > > */
> > > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > > */
> > >
> > > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > > pnumParcelId. I enter values, and click OK, and get the same error
> > > referenced above.
> > >
> > > Not at all sure what I'm supposed to be doing... I even tried manually
> > > adding "Report Parameters", but same issue...
> > >
> > > Please help.
> > >
> > > Thanks
> > >

Oracle problem

Has someone encountered this issue when reports are run from ORACLE datasource
I can view the reports fine in VS.net designer but when I try to view it
from RS Manager I get this erro
----
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OracleDvel'.
(rsErrorOpeningConnection) Get Online Help
System.Data.OracleClient requires Oracle client software version 8.1.7 or
greater.
----
Thanks
System.Data.OracleClient requires Oracle client software version 8.1.7 or
greater.Make sure to install the Oracle client software on the report server machine
(not only on the report designer machine).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"shahab" <shahab@.discussions.microsoft.com> wrote in message
news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
> Has someone encountered this issue when reports are run from ORACLE
> datasource
> I can view the reports fine in VS.net designer but when I try to view it
> from RS Manager I get this error
> ----
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OracleDvel'.
> (rsErrorOpeningConnection) Get Online Help
> System.Data.OracleClient requires Oracle client software version 8.1.7 or
> greater.
> ----
> Thanks
> System.Data.OracleClient requires Oracle client software version 8.1.7 or
> greater.|||Thanks Rob
I am doing the both i.e the designer as well as reportserver on the same
machine. Although I have installed 9i client but still it would not let me
continue.
Any assistance would be most appreciated.
"Robert Bruckner [MSFT]" wrote:
> Make sure to install the Oracle client software on the report server machine
> (not only on the report designer machine).
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
> > Has someone encountered this issue when reports are run from ORACLE
> > datasource
> > I can view the reports fine in VS.net designer but when I try to view it
> > from RS Manager I get this error
> > ----
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'OracleDvel'.
> > (rsErrorOpeningConnection) Get Online Help
> > System.Data.OracleClient requires Oracle client software version 8.1.7 or
> > greater.
> >
> > ----
> > Thanks
> >
> > System.Data.OracleClient requires Oracle client software version 8.1.7 or
> > greater.
>
>|||1. Use ORACLE OLE DB PROVIDER
2. Server, Login, Password
3. Open over RS Project and use this:
- use OLE DB Generic
- Manual Modify Data Source: "Provider=MSDAORA.1;data source=XXXXXXX|||Did you reboot the machine since you installed the Oracle 9i client. There
is a known issue that ASP.NET application (like the report server) don't
"see" the Oracle 9i client right after installation without reboot.
Also make sure that the Oracle client installation directory can be accessed
(add read permissions explicitly) by the account the RS windows service is
running under.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"shahab" <shahab@.discussions.microsoft.com> wrote in message
news:1119CDBB-8C67-438E-9FA7-C585E41BB67E@.microsoft.com...
> Thanks Rob
> I am doing the both i.e the designer as well as reportserver on the same
> machine. Although I have installed 9i client but still it would not let me
> continue.
> Any assistance would be most appreciated.
>
> "Robert Bruckner [MSFT]" wrote:
>> Make sure to install the Oracle client software on the report server
>> machine
>> (not only on the report designer machine).
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "shahab" <shahab@.discussions.microsoft.com> wrote in message
>> news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
>> > Has someone encountered this issue when reports are run from ORACLE
>> > datasource
>> > I can view the reports fine in VS.net designer but when I try to view
>> > it
>> > from RS Manager I get this error
>> > ----
>> > An error has occurred during report processing. (rsProcessingAborted)
>> > Get
>> > Online Help
>> > Cannot create a connection to data source 'OracleDvel'.
>> > (rsErrorOpeningConnection) Get Online Help
>> > System.Data.OracleClient requires Oracle client software version 8.1.7
>> > or
>> > greater.
>> >
>> > ----
>> > Thanks
>> >
>> > System.Data.OracleClient requires Oracle client software version 8.1.7
>> > or
>> > greater.
>>|||still no luck. I rebooted the machine and gave full control to the Oracle
client directory ...The RS is running under my own credentials but still
comes up with the same error.
"Robert Bruckner [MSFT]" wrote:
> Did you reboot the machine since you installed the Oracle 9i client. There
> is a known issue that ASP.NET application (like the report server) don't
> "see" the Oracle 9i client right after installation without reboot.
> Also make sure that the Oracle client installation directory can be accessed
> (add read permissions explicitly) by the account the RS windows service is
> running under.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> news:1119CDBB-8C67-438E-9FA7-C585E41BB67E@.microsoft.com...
> > Thanks Rob
> > I am doing the both i.e the designer as well as reportserver on the same
> > machine. Although I have installed 9i client but still it would not let me
> > continue.
> > Any assistance would be most appreciated.
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> Make sure to install the Oracle client software on the report server
> >> machine
> >> (not only on the report designer machine).
> >>
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> >> news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
> >> > Has someone encountered this issue when reports are run from ORACLE
> >> > datasource
> >> > I can view the reports fine in VS.net designer but when I try to view
> >> > it
> >> > from RS Manager I get this error
> >> > ----
> >> > An error has occurred during report processing. (rsProcessingAborted)
> >> > Get
> >> > Online Help
> >> > Cannot create a connection to data source 'OracleDvel'.
> >> > (rsErrorOpeningConnection) Get Online Help
> >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> >> > or
> >> > greater.
> >> >
> >> > ----
> >> > Thanks
> >> >
> >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> >> > or
> >> > greater.
> >>
> >>
> >>
>
>|||I am having the same problem. RS is running a new server. I've tried the
recommendations above and still get this error. Did you resolve it?
Thanks,
Neil
"shahab" wrote:
> still no luck. I rebooted the machine and gave full control to the Oracle
> client directory ...The RS is running under my own credentials but still
> comes up with the same error.
> "Robert Bruckner [MSFT]" wrote:
> > Did you reboot the machine since you installed the Oracle 9i client. There
> > is a known issue that ASP.NET application (like the report server) don't
> > "see" the Oracle 9i client right after installation without reboot.
> > Also make sure that the Oracle client installation directory can be accessed
> > (add read permissions explicitly) by the account the RS windows service is
> > running under.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "shahab" <shahab@.discussions.microsoft.com> wrote in message
> > news:1119CDBB-8C67-438E-9FA7-C585E41BB67E@.microsoft.com...
> > > Thanks Rob
> > > I am doing the both i.e the designer as well as reportserver on the same
> > > machine. Although I have installed 9i client but still it would not let me
> > > continue.
> > > Any assistance would be most appreciated.
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > >> Make sure to install the Oracle client software on the report server
> > >> machine
> > >> (not only on the report designer machine).
> > >>
> > >>
> > >> -- Robert
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >>
> > >>
> > >> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> > >> news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
> > >> > Has someone encountered this issue when reports are run from ORACLE
> > >> > datasource
> > >> > I can view the reports fine in VS.net designer but when I try to view
> > >> > it
> > >> > from RS Manager I get this error
> > >> > ----
> > >> > An error has occurred during report processing. (rsProcessingAborted)
> > >> > Get
> > >> > Online Help
> > >> > Cannot create a connection to data source 'OracleDvel'.
> > >> > (rsErrorOpeningConnection) Get Online Help
> > >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> > >> > or
> > >> > greater.
> > >> >
> > >> > ----
> > >> > Thanks
> > >> >
> > >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> > >> > or
> > >> > greater.
> > >>
> > >>
> > >>
> >
> >
> >|||I unistalled the RS and then installed it again and it worked. My situation
was that I installed Oracle clients after I installed the RS, and maybe the
RS does not recognises it.
Hope it helps
"Neil Gould" wrote:
> I am having the same problem. RS is running a new server. I've tried the
> recommendations above and still get this error. Did you resolve it?
> Thanks,
> Neil
> "shahab" wrote:
> > still no luck. I rebooted the machine and gave full control to the Oracle
> > client directory ...The RS is running under my own credentials but still
> > comes up with the same error.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Did you reboot the machine since you installed the Oracle 9i client. There
> > > is a known issue that ASP.NET application (like the report server) don't
> > > "see" the Oracle 9i client right after installation without reboot.
> > > Also make sure that the Oracle client installation directory can be accessed
> > > (add read permissions explicitly) by the account the RS windows service is
> > > running under.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > >
> > > "shahab" <shahab@.discussions.microsoft.com> wrote in message
> > > news:1119CDBB-8C67-438E-9FA7-C585E41BB67E@.microsoft.com...
> > > > Thanks Rob
> > > > I am doing the both i.e the designer as well as reportserver on the same
> > > > machine. Although I have installed 9i client but still it would not let me
> > > > continue.
> > > > Any assistance would be most appreciated.
> > > >
> > > >
> > > > "Robert Bruckner [MSFT]" wrote:
> > > >
> > > >> Make sure to install the Oracle client software on the report server
> > > >> machine
> > > >> (not only on the report designer machine).
> > > >>
> > > >>
> > > >> -- Robert
> > > >> This posting is provided "AS IS" with no warranties, and confers no
> > > >> rights.
> > > >>
> > > >>
> > > >> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> > > >> news:E59DF319-A42E-455D-AE68-10D71BE65DC8@.microsoft.com...
> > > >> > Has someone encountered this issue when reports are run from ORACLE
> > > >> > datasource
> > > >> > I can view the reports fine in VS.net designer but when I try to view
> > > >> > it
> > > >> > from RS Manager I get this error
> > > >> > ----
> > > >> > An error has occurred during report processing. (rsProcessingAborted)
> > > >> > Get
> > > >> > Online Help
> > > >> > Cannot create a connection to data source 'OracleDvel'.
> > > >> > (rsErrorOpeningConnection) Get Online Help
> > > >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> > > >> > or
> > > >> > greater.
> > > >> >
> > > >> > ----
> > > >> > Thanks
> > > >> >
> > > >> > System.Data.OracleClient requires Oracle client software version 8.1.7
> > > >> > or
> > > >> > greater.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >

Oracle Datasource with sql express version

Hi,
do you know if it's possible to connect Reporting Services, installed
with the sql express version, to an oracle datasource.
The oracle server is on the same machine.
This work fine in dev environnement but the deployment failed with this
message: "provider is not registered on the local machine".
Thank'sI don't think so. I think the express version is for reporting against
express data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<amfred@.gmail.com> wrote in message
news:1156321685.686065.70410@.m73g2000cwd.googlegroups.com...
> Hi,
> do you know if it's possible to connect Reporting Services, installed
> with the sql express version, to an oracle datasource.
> The oracle server is on the same machine.
> This work fine in dev environnement but the deployment failed with this
> message: "provider is not registered on the local machine".
> Thank's
>