Oracle Connection Problems

Hello,
I'm currently in the process of deploying reports transfered from
Crystal Reports to a Reporting Services server. All of the reports
have been deployed with the exception of about a third of the reports
that make use of Oracle. I originally designed the shared data source
to use the Oracle provider rather than the OLE DB provider. However,
when I recreate the shared data source on the Reporting Services
server (with Oracle 8.1.7 installed), attempting to view any of the
Oracle reports gives me the following error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'internal'.
(rsErrorOpeningConnection)
ORA-12640: Authentication adapter initialization failed
Clicking on the online help links generated by the error message gives
me the following information: "Cannot create a connection to data
source". I loaded up SQL Plus on the Reporting Services server to
ensure that a connection could be made to the Oracle server. Sure
enough, I had no problem retrieving data from the Oracle server.
After trying a few of the more simple troubleshooting suggestions
available on the web without success, I switched my provider from
Oracle to OLE DB using the following connection string:
Provider=MSDAORA;Data Source=NameOfTheServer
For two thirds of the reports, this switch works perfectly fine. But
for the other third, the following error is generated when attempting
to view report data:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'DataSet1'.
(rsErrorExecutingCommand)
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The number of parameters in the report matches the number of
parameters in the stored procedure. Likewise, the data types used in
the report (mainly String) match the data types used in the stored
procedure (mainly VARCHAR). I've tried recreating the shared data
source on my machine and viewing the same reports that are plagued by
those errors: the same error is generated on my machine. However, if I
go to the data tab of the report on my machine, select the dataset
that causes the problem, hit run, and fill in the same data as I did
for the parameters in the report, the data is returned without a
single problem.
I've searched this newsgroup and the web for a solution without
finding one and, at this point, I'm a bit lost. Does anyone have any
suggestions?
Regards,
BryanRegarding ORA-12640:
* Did you enable tracing and perform some further investigations on the
Oracle side to determine the exact error within the Oracle Authentication
Adapter?
* Did the ORA-12640 error happen for all users trying to execute reports
(i.e. users which are members of the Administrator group on the report
server machine vs. non-admins)? If it only happens for non-admin users, then
it is a file system security permission issue. Just as a test, you might
want to try giving explicit Read&Execute permissions to all files and
subdirectories in the \oracle\ora81 installation directory.
Note: SQL*Plus uses a different mode to connect to the Oracle server and
therefore would most likely not run into the same authentication problem.
Regarding OleDB error when using shared datasources:
* you might want to use two shared datasources in the report project, one is
based on the managed Oracle provider, and one that is based on the OLE DB
provider
* design the report with the shared data source for the managed Oracle
provider
* when everything works, go to the "Data" view, open the data source dialog
and just switch from the Oracle shared data source to the OleDB shared data
source; click OK
* After this step make sure that you do _not_ execute the stored procedure
from the "Data" view or hit the green "Refresh Fields" icon (this will cause
a change in the RDL structure which will later cause the OleDB problem).
* You can still change the layout, run the report in preview and publish it
on the server
Regarding OleDB error when using embedded datasources:
You could do similar steps as above, or if the report is already published
using the managed Oracle provider you would need these steps:
* EDIT the report properties in report manager for the report:
- Go to "Data sources" and replace "Oracle" with "OLE DB"
- Replace the connection string with a valid OLE DB connection string
(e.g. Provider=MSDAORA; Data Source=NameOfTheServer).
* make sure to hit the "APPLY" button on the data source page
You could also manually edit the RDL file and just change the contents of
the <ConnectionProperties> element.
Always make sure the report works fine based on the managed Oracle provider
in report designer, before switching to OleDB. Let me know if the steps
above work for you. We plan to address the underlying issue for the OleDB
error in RS SP2.
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407121311.710f2d65@.posting.google.com...
> Hello,
> I'm currently in the process of deploying reports transfered from
> Crystal Reports to a Reporting Services server. All of the reports
> have been deployed with the exception of about a third of the reports
> that make use of Oracle. I originally designed the shared data source
> to use the Oracle provider rather than the OLE DB provider. However,
> when I recreate the shared data source on the Reporting Services
> server (with Oracle 8.1.7 installed), attempting to view any of the
> Oracle reports gives me the following error:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'internal'.
> (rsErrorOpeningConnection)
> ORA-12640: Authentication adapter initialization failed
> Clicking on the online help links generated by the error message gives
> me the following information: "Cannot create a connection to data
> source". I loaded up SQL Plus on the Reporting Services server to
> ensure that a connection could be made to the Oracle server. Sure
> enough, I had no problem retrieving data from the Oracle server.
> After trying a few of the more simple troubleshooting suggestions
> available on the web without success, I switched my provider from
> Oracle to OLE DB using the following connection string:
> Provider=MSDAORA;Data Source=NameOfTheServer
> For two thirds of the reports, this switch works perfectly fine. But
> for the other third, the following error is generated when attempting
> to view report data:
> An error has occurred during report processing. (rsProcessingAborted)
> Query execution failed for data set 'DataSet1'.
> (rsErrorExecutingCommand)
> ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> The number of parameters in the report matches the number of
> parameters in the stored procedure. Likewise, the data types used in
> the report (mainly String) match the data types used in the stored
> procedure (mainly VARCHAR). I've tried recreating the shared data
> source on my machine and viewing the same reports that are plagued by
> those errors: the same error is generated on my machine. However, if I
> go to the data tab of the report on my machine, select the dataset
> that causes the problem, hit run, and fill in the same data as I did
> for the parameters in the report, the data is returned without a
> single problem.
> I've searched this newsgroup and the web for a solution without
> finding one and, at this point, I'm a bit lost. Does anyone have any
> suggestions?
> Regards,
> Bryan|||Hi,
Thanks for the response. I'm just going to reply to each individual
point.
> * Did you enable tracing and perform some further investigations on the
> Oracle side to determine the exact error within the Oracle Authentication
> Adapter?
Not yet. That's something I'd have to pass along to the Oracle person
here - if it comes to that, I'll post the results.
> * Did the ORA-12640 error happen for all users trying to execute reports
> (i.e. users which are members of the Administrator group on the report
> server machine vs. non-admins)? If it only happens for non-admin users, then
> it is a file system security permission issue. Just as a test, you might
> want to try giving explicit Read&Execute permissions to all files and
> subdirectories in the \oracle\ora81 installation directory.
> Note: SQL*Plus uses a different mode to connect to the Oracle server and
> therefore would most likely not run into the same authentication problem.
It happens for all users. Just to be sure, I tried giving everyone
read & execute permissions to the Oracle directory - no luck.
> Regarding OleDB error when using shared datasources:
> * you might want to use two shared datasources in the report project, one is
> based on the managed Oracle provider, and one that is based on the OLE DB
> provider
> * design the report with the shared data source for the managed Oracle
> provider
> * when everything works, go to the "Data" view, open the data source dialog
> and just switch from the Oracle shared data source to the OleDB shared data
> source; click OK
> * After this step make sure that you do _not_ execute the stored procedure
> from the "Data" view or hit the green "Refresh Fields" icon (this will cause
> a change in the RDL structure which will later cause the OleDB problem).
> * You can still change the layout, run the report in preview and publish it
> on the server
I'm not having any luck with that either.
> Always make sure the report works fine based on the managed Oracle provider
> in report designer, before switching to OleDB. Let me know if the steps
> above work for you. We plan to address the underlying issue for the OleDB
> error in RS SP2.
The reports were all designed on my own machine using the Oracle
provider. When I couldn't get the Oracle provider working on the
Reporting Services server, I changed the properties of the shared data
source to use OLE DB.
What's confusing me is that most of the reports work just fine with
OLE DB, parameterized and all. I managed to get one of the Oracle
reports that I was having a problem with (PLS-00306) working. I
changed the two datetime parameters in the report to strings, then I
type cast the parameter values to datetime and then back to string. I
changed the stored procedure parameters to accept varchar2 values and
then type cast the values back to date later in the procedure.
Strangely enough, the report works as intended now. I'll be attempting
the same with the remaining reports when I have time - the stored
procedures used are much more complicated than the one I fixed, so it
could take a while.
Regards,
Bryan|||Thanks for the response. I did reboot the machine after installing the
Oracle client, but I didn't grant Everyone read & execute permissions
on the Oracle folder. I tried it but unfortunately had no luck.
Regards,
Bryan
> 2 things. After you installed the Oracle client on the report server did you reboot? Also, you need to make sure that are permissions on the oracle folder. Make sure that "everyone" has read and execute permission.
> Mark
> "Bryan" wrote:
> > Hello,
> >
> > I'm currently in the process of deploying reports transfered from
> > Crystal Reports to a Reporting Services server. All of the reports
> > have been deployed with the exception of about a third of the reports
> > that make use of Oracle. I originally designed the shared data source
> > to use the Oracle provider rather than the OLE DB provider. However,
> > when I recreate the shared data source on the Reporting Services
> > server (with Oracle 8.1.7 installed), attempting to view any of the
> > Oracle reports gives me the following error:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Cannot create a connection to data source 'internal'.
> > (rsErrorOpeningConnection)
> > ORA-12640: Authentication adapter initialization failed
> >
> > Clicking on the online help links generated by the error message gives
> > me the following information: "Cannot create a connection to data
> > source". I loaded up SQL Plus on the Reporting Services server to
> > ensure that a connection could be made to the Oracle server. Sure
> > enough, I had no problem retrieving data from the Oracle server.
> >
> > After trying a few of the more simple troubleshooting suggestions
> > available on the web without success, I switched my provider from
> > Oracle to OLE DB using the following connection string:
> > Provider=MSDAORA;Data Source=NameOfTheServer
> > For two thirds of the reports, this switch works perfectly fine. But
> > for the other third, the following error is generated when attempting
> > to view report data:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
> > Query execution failed for data set 'DataSet1'.
> > (rsErrorExecutingCommand)
> > ORA-06550: line 1, column 7: PLS-00306: wrong number or types of
> > arguments in call to 'SP_CDIPRODBYPIT' ORA-06550: line 1, column 7:
> > PL/SQL: Statement ignored
> >
> > The number of parameters in the report matches the number of
> > parameters in the stored procedure. Likewise, the data types used in
> > the report (mainly String) match the data types used in the stored
> > procedure (mainly VARCHAR). I've tried recreating the shared data
> > source on my machine and viewing the same reports that are plagued by
> > those errors: the same error is generated on my machine. However, if I
> > go to the data tab of the report on my machine, select the dataset
> > that causes the problem, hit run, and fill in the same data as I did
> > for the parameters in the report, the data is returned without a
> > single problem.
> >
> > I've searched this newsgroup and the web for a solution without
> > finding one and, at this point, I'm a bit lost. Does anyone have any
> > suggestions?
> >
> > Regards,
> >
> > Bryan
> >|||SP1 contains a fix the PLS-00306 issue when using the managed Oracle
provider in report designer. However, it can still happen with the OleDB
provider, when you execute the SP from Data view or refresh fields.
Do you have SP1 installed?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bryan" <bscriven@.inland.lehighcement.com> wrote in message
news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> Hi,
> Thanks for the response. I'm just going to reply to each individual
> point.
> > * Did you enable tracing and perform some further investigations on the
> > Oracle side to determine the exact error within the Oracle
Authentication
> > Adapter?
> Not yet. That's something I'd have to pass along to the Oracle person
> here - if it comes to that, I'll post the results.
> > * Did the ORA-12640 error happen for all users trying to execute reports
> > (i.e. users which are members of the Administrator group on the report
> > server machine vs. non-admins)? If it only happens for non-admin users,
then
> > it is a file system security permission issue. Just as a test, you might
> > want to try giving explicit Read&Execute permissions to all files and
> > subdirectories in the \oracle\ora81 installation directory.
> > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > therefore would most likely not run into the same authentication
problem.
> It happens for all users. Just to be sure, I tried giving everyone
> read & execute permissions to the Oracle directory - no luck.
> > Regarding OleDB error when using shared datasources:
> > * you might want to use two shared datasources in the report project,
one is
> > based on the managed Oracle provider, and one that is based on the OLE
DB
> > provider
> > * design the report with the shared data source for the managed Oracle
> > provider
> > * when everything works, go to the "Data" view, open the data source
dialog
> > and just switch from the Oracle shared data source to the OleDB shared
data
> > source; click OK
> > * After this step make sure that you do _not_ execute the stored
procedure
> > from the "Data" view or hit the green "Refresh Fields" icon (this will
cause
> > a change in the RDL structure which will later cause the OleDB problem).
> > * You can still change the layout, run the report in preview and publish
it
> > on the server
> I'm not having any luck with that either.
> > Always make sure the report works fine based on the managed Oracle
provider
> > in report designer, before switching to OleDB. Let me know if the steps
> > above work for you. We plan to address the underlying issue for the
OleDB
> > error in RS SP2.
> The reports were all designed on my own machine using the Oracle
> provider. When I couldn't get the Oracle provider working on the
> Reporting Services server, I changed the properties of the shared data
> source to use OLE DB.
> What's confusing me is that most of the reports work just fine with
> OLE DB, parameterized and all. I managed to get one of the Oracle
> reports that I was having a problem with (PLS-00306) working. I
> changed the two datetime parameters in the report to strings, then I
> type cast the parameter values to datetime and then back to string. I
> changed the stored procedure parameters to accept varchar2 values and
> then type cast the values back to date later in the procedure.
> Strangely enough, the report works as intended now. I'll be attempting
> the same with the remaining reports when I have time - the stored
> procedures used are much more complicated than the one I fixed, so it
> could take a while.
> Regards,
> Bryan|||SP1 is installed.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> provider in report designer. However, it can still happen with the OleDB
> provider, when you execute the SP from Data view or refresh fields.
> Do you have SP1 installed?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > Hi,
> >
> > Thanks for the response. I'm just going to reply to each individual
> > point.
> >
> > > * Did you enable tracing and perform some further investigations on the
> > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > Adapter?
> >
> > Not yet. That's something I'd have to pass along to the Oracle person
> > here - if it comes to that, I'll post the results.
> >
> > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > (i.e. users which are members of the Administrator group on the report
> > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > it is a file system security permission issue. Just as a test, you might
> > > want to try giving explicit Read&Execute permissions to all files and
> > > subdirectories in the \oracle\ora81 installation directory.
> > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > therefore would most likely not run into the same authentication
> problem.
> >
> > It happens for all users. Just to be sure, I tried giving everyone
> > read & execute permissions to the Oracle directory - no luck.
> >
> > > Regarding OleDB error when using shared datasources:
> > > * you might want to use two shared datasources in the report project,
> one is
> > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > provider
> > > * design the report with the shared data source for the managed Oracle
> > > provider
> > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > source; click OK
> > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > a change in the RDL structure which will later cause the OleDB problem).
> > > * You can still change the layout, run the report in preview and publish
> it
> > > on the server
> >
> > I'm not having any luck with that either.
> >
> > > Always make sure the report works fine based on the managed Oracle
> provider
> > > in report designer, before switching to OleDB. Let me know if the steps
> > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > error in RS SP2.
> >
> > The reports were all designed on my own machine using the Oracle
> > provider. When I couldn't get the Oracle provider working on the
> > Reporting Services server, I changed the properties of the shared data
> > source to use OLE DB.
> >
> > What's confusing me is that most of the reports work just fine with
> > OLE DB, parameterized and all. I managed to get one of the Oracle
> > reports that I was having a problem with (PLS-00306) working. I
> > changed the two datetime parameters in the report to strings, then I
> > type cast the parameter values to datetime and then back to string. I
> > changed the stored procedure parameters to accept varchar2 values and
> > then type cast the values back to date later in the procedure.
> > Strangely enough, the report works as intended now. I'll be attempting
> > the same with the remaining reports when I have time - the stored
> > procedures used are much more complicated than the one I fixed, so it
> > could take a while.
> >
> > Regards,
> >
> > Bryan|||I just wanted to leave an update.
I managed to get my remaining Oracle reports working. In all cases,
the PLS-00306 error was caused by the date parameters. I used the
following steps to get the reports working (some of which may not be
necessary - I haven't tested the procedure thoroughly)
1) Some of the stored procedures were old and were not quite written
to Microsoft specs. Being sure to declare which parameters were going
in and which were going out, as well as moving the cursor to the last
in the list, seemed to help.
2) Some of the "date" values were being brought in as varchar and were
being converted to date in the stored procedure. I changed the
datatype on the relevant parameters to date and got rid of the to_date
calls.
3) I left the report parameters as string datatypes.
4) I changed the provider from Oracle to OLE DB and refreshed the
dataset.
5) Since refreshing the dataset erased the parameter list, I entered
all parameters manually. Under the value fields I used the CDate
function to type cast the string start date and end date values to
date values.
Additionally, the Reporting Services server I'm using seems to become
confused and switches mm/dd/yyyy dates to dd/mm/yyyy for ALL of my
Oracle reports. Type casting dates from string to date also seems to
solve that problem.
bscriven@.inland.lehighcement.com (Bryan) wrote in message news:<7b5f5ea2.0407150623.7d7dc0fa@.posting.google.com>...
> SP1 is installed.
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uUiw0TcaEHA.3664@.TK2MSFTNGP12.phx.gbl>...
> > SP1 contains a fix the PLS-00306 issue when using the managed Oracle
> > provider in report designer. However, it can still happen with the OleDB
> > provider, when you execute the SP from Data view or refresh fields.
> > Do you have SP1 installed?
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "Bryan" <bscriven@.inland.lehighcement.com> wrote in message
> > news:7b5f5ea2.0407140632.745a8151@.posting.google.com...
> > > Hi,
> > >
> > > Thanks for the response. I'm just going to reply to each individual
> > > point.
> > >
> > > > * Did you enable tracing and perform some further investigations on the
> > > > Oracle side to determine the exact error within the Oracle
> Authentication
> > > > Adapter?
> > >
> > > Not yet. That's something I'd have to pass along to the Oracle person
> > > here - if it comes to that, I'll post the results.
> > >
> > > > * Did the ORA-12640 error happen for all users trying to execute reports
> > > > (i.e. users which are members of the Administrator group on the report
> > > > server machine vs. non-admins)? If it only happens for non-admin users,
> then
> > > > it is a file system security permission issue. Just as a test, you might
> > > > want to try giving explicit Read&Execute permissions to all files and
> > > > subdirectories in the \oracle\ora81 installation directory.
> > > > Note: SQL*Plus uses a different mode to connect to the Oracle server and
> > > > therefore would most likely not run into the same authentication
> problem.
> > >
> > > It happens for all users. Just to be sure, I tried giving everyone
> > > read & execute permissions to the Oracle directory - no luck.
> > >
> > > > Regarding OleDB error when using shared datasources:
> > > > * you might want to use two shared datasources in the report project,
> one is
> > > > based on the managed Oracle provider, and one that is based on the OLE
> DB
> > > > provider
> > > > * design the report with the shared data source for the managed Oracle
> > > > provider
> > > > * when everything works, go to the "Data" view, open the data source
> dialog
> > > > and just switch from the Oracle shared data source to the OleDB shared
> data
> > > > source; click OK
> > > > * After this step make sure that you do _not_ execute the stored
> procedure
> > > > from the "Data" view or hit the green "Refresh Fields" icon (this will
> cause
> > > > a change in the RDL structure which will later cause the OleDB problem).
> > > > * You can still change the layout, run the report in preview and publish
> it
> > > > on the server
> > >
> > > I'm not having any luck with that either.
> > >
> > > > Always make sure the report works fine based on the managed Oracle
> provider
> > > > in report designer, before switching to OleDB. Let me know if the steps
> > > > above work for you. We plan to address the underlying issue for the
> OleDB
> > > > error in RS SP2.
> > >
> > > The reports were all designed on my own machine using the Oracle
> > > provider. When I couldn't get the Oracle provider working on the
> > > Reporting Services server, I changed the properties of the shared data
> > > source to use OLE DB.
> > >
> > > What's confusing me is that most of the reports work just fine with
> > > OLE DB, parameterized and all. I managed to get one of the Oracle
> > > reports that I was having a problem with (PLS-00306) working. I
> > > changed the two datetime parameters in the report to strings, then I
> > > type cast the parameter values to datetime and then back to string. I
> > > changed the stored procedure parameters to accept varchar2 values and
> > > then type cast the values back to date later in the procedure.
> > > Strangely enough, the report works as intended now. I'll be attempting
> > > the same with the remaining reports when I have time - the stored
> > > procedures used are much more complicated than the one I fixed, so it
> > > could take a while.
> > >
> > > Regards,
> > >
> > > Bryan