Order Group in specified order

Hi all,
We have recently migrated from Crystal Reports to SSRS. We are converting
all our company reports from one format to another. Some of our reports have
groupings in a specified order. When you sort order in Crystal you can select
"Specified Order" and then pick the names of the groups and place them in a
specified order to appear on the report, as opposed to ascending alphabetical
order for example.
How can I specify "Specified Order" when creating a Reporting Services report?
Thanks
JHI have never used Crystal Reports. But I think I understand what you
are asking. I had a similar issue where I had to explicity specify the
order of matrix columns. Neither RS nor my SQL SELECT ORDER BY did the
trick because I could do only Ascending/Descending or ASC/DESC
respectively. I would like to believe RS has some way to control
explicit order, but I sure cannot find it.
I solved my problem by doing the following
1) returned a dummy INT column from my SELECT statement. Made sure rows
in the dataset returned appropriate value for this column based on the
column data.
2) Changed the Column group in my matrix to Sort Ascending on THIS INT
column instead of using a data field.
Kludgy perhaps, but at the moment I could care less.
HTH.
Best.
JH wrote:
> Hi all,
> We have recently migrated from Crystal Reports to SSRS. We are converting
> all our company reports from one format to another. Some of our reports have
> groupings in a specified order. When you sort order in Crystal you can select
> "Specified Order" and then pick the names of the groups and place them in a
> specified order to appear on the report, as opposed to ascending alphabetical
> order for example.
> How can I specify "Specified Order" when creating a Reporting Services report?
> Thanks
> JH

Oracle Stored Procedure for RDLC Dataset

Is it possible to use an Oracle Stored Procedure for an RDLC report. There are posts I've read that deal with RDL reports that use the data tab and command type of "Stored Procedure", but I don't have that installed. I just create a new dataset that the report uses. I can do reports just fine with SQL statements, but I want to be able to call a stored procedure...

Thanks

Another post that seeks to answer this question is at

http://www.codenewsgroups.net/group/microsoft.public.dotnet.framework.aspnet.webcontrols/topic6588.aspx

"Hi, i have used rdlc with the informix database. What is did was add a empty
dataset to the rdlc and i have a business layer (a vb class) which has a
function which returns a dataset. When you add a report to the page add an
objectdatasource and specify the type as the vb class and the select method
as the funtion. This works for me. "

Is this the only way to accomplish this? Does someone have the definitive answer of "No, an

Oracle Stored Procedure cannot be called by a setting in the dataset wizard for a client-side (RDLC) report."

Also, here is a post that deals with just the RDL report:

http://www.developer.com/db/article.php/3524781

Oracle stored proc with OUT parameter

Hi,

I am calling an Oracle stored proc which contains an IN and an OUT parameter also.

To the stored proc, I pass two reports parameteres. I get following error when I execute the report:

PLS-00306: wrong number or types of arguments in call to <Procedure name>

Where am I going wrong?

TIA,

Tanmaya

Out parameters for Oracle stored procedures are not supported. Only an OUT REF cursor is supported. Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.

-- Robert

Oracle reports using SQL Reporting Services

Hi! I am using SQL Reporting Services to create reports on data that is
stored on an Oracle server. I am unable to use query parameters when
accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
use in Reporting Services? Any help would be greatly appreciated.
Just to clarify, it's something similar to this (larger scale, of
course):
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = @.ID
Whenever I create a query parameter, Reporting Services tells me that I
am missing an expression...The managed Oracle provider (data source type: "Oracle") uses ":" instead of
"@." to identify parameters. Try this instead:
SELECT FirstName, LastName
FROM EmployeeInformation
WHERE EmployeeID = :ID
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nandan" <nandanrp@.gmail.com> wrote in message
news:1128455599.396906.96420@.g47g2000cwa.googlegroups.com...
> Hi! I am using SQL Reporting Services to create reports on data that is
> stored on an Oracle server. I am unable to use query parameters when
> accessing Oracle (i.e. @.ID). Is there an Oracle equivalent that I can
> use in Reporting Services? Any help would be greatly appreciated.
> Just to clarify, it's something similar to this (larger scale, of
> course):
> SELECT FirstName, LastName
> FROM EmployeeInformation
> WHERE EmployeeID = @.ID
> Whenever I create a query parameter, Reporting Services tells me that I
> am missing an expression...
>|||Yes that did it! 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 error witrh Report Builder

Hi
I am doing reports and UDMs from a DW in Oracle and it is great (some
doubt expressed in another post).
I tried Report Builder but the new model wizard comes back with the
error:
ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
COMMITTED }
The data source file (.ds) has been configured with most of the
connections to Oracle available (e.g. .NET Providers Oracleclient Data
provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
The strange thing is that I can create the .dsv with no problem, browse
the tables, refresh with no problems.
What I am doing wrong?
cheers
EnzoYou are not doing anything wrong. Oracle is not supported for the Report
Builder (note that it is supported for the Report Designer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Enzo M" <emartoglio@.gmail.com> wrote in message
news:1141724038.842766.151540@.j33g2000cwa.googlegroups.com...
> Hi
> I am doing reports and UDMs from a DW in Oracle and it is great (some
> doubt expressed in another post).
> I tried Report Builder but the new model wizard comes back with the
> error:
> ORA 02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ
> COMMITTED }
> The data source file (.ds) has been configured with most of the
> connections to Oracle available (e.g. .NET Providers Oracleclient Data
> provider; Microsoft OLE DB Provider for ORacle etc.) with same error.
> The strange thing is that I can create the .dsv with no problem, browse
> the tables, refresh with no problems.
> What I am doing wrong?
> cheers
> Enzo
>|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Oracle data source connection problem using Report Manager

I have an issue when using Report Manager for viewing reports that use a certain Oracle data source. When viewing the reports in Report Designer the reports display without problems. But when trying to display the reports with Report Manager, after deploying the reports to the Report Server, I now get the following error message:

blablabla....ORA-12705: invalid or unknown NLS parameter value specified

For testing purposes I am running Report Designer, Report Server and Report Manager locally on a PC with Windows XP. I am using Reporting Services 2005, IIS 6, Visual Studio 2005, an Oracle 9.x client and an Oracle 9.x (not 100% sure) database that is hosted on a remote server.

Earlier I got an error message that stated "ORA-12154: TNS: could not resolve the connect identifier specified". Access rights to some Oracle folders for the Network service account did the trick here but then I got the "NLS parameter" error message. What I have tried to do is removing the NLS_LANG register variable and also modified the value of the variable so that it matches the value of the NLS_LANG variable on the server that hosts the Oracle database, but none of them have worked. Any ideas?

Maybe this is an issue that is more appropriate in an Oracle forum but what the heck..the problem could reside in Reporting Services..

Thanks, Stefan

I found the solution to the problem. I was jerking around with the wrong NLS_LANG registry variable. Everywhere I have looked it says that the NLS_LANG variable is found in the Home0 directory in the Windows registry, so naturally I have tested settings with that NLS_LANG variable. Also, the first thing I did was to search the registry for the NLS_LANG variable and the only search result was the one found under the Home0 directory. Out of coincidence, I found another NLS_LANG variable located directly under the Oracle directory in the registry. This variable was set to "NA". I changed it to AMERICAN_AMERICA.WE8ISO8859P15, and boom shakalak!

/Stefan

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