Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>
Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts
Friday, March 30, 2012
Monday, March 19, 2012
Oracle with Reporting Services. "illegal variable name/number"
I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.
This is the code that I'm using for the dataset query that my report is to be based on:
="SELECT * FROM IRMFE_*************_V WHERE " &
IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")
The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:
"Query execution failed for dataset 'FREIGHT'.
ORA-01036: illegal variable name/number'
There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')
The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.
Any resolution or suggestion on this would be much appreciated guys.
Kev.After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:
IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"
instead of
IIf(Parameters!Invoice_No.Value(0)<> "ALL"
I found Chris Hays solution and will just use that though.
Oracle with Reporting Services
I am trying to connect to an Oracle database using Reporting Services.
I create a new Shared Data Source, give it a name, then hit the edit
button. Set the provider to Microsoft OLE DB Provider for Oracle, on
connection tab I enter
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID = ServiceName)))
I enter the username and password, hit the test and everything tests
out ok. I ok out, go to create a new report and when I go to use the
DataSource I get an error of
A connection cannot be made to the database.
Set and test the connection string.
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater.
Any help would be appreciated. Thanks.OK, are you using RS 2000. Here is how it works in RS 2000. When creating
the query it uses OLE DB if you are using the graphical editor (4 panes). If
you use the generic it uses the dotnet provider. The generic is 2 panes
(there is a button to switch to generic to the right of the ...). When
running the report it uses the dotnet provider. The reason for this was that
VS 2003 query designer did not have support for the dotnet provider so they
did this workaround. The dotnet provider requires 8.1.7 client to be
installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdcamp" <camsam50@.hotmail.com> wrote in message
news:1145550738.077059.160290@.t31g2000cwb.googlegroups.com...
>I am trying to connect to an Oracle database using Reporting Services.
> I create a new Shared Data Source, give it a name, then hit the edit
> button. Set the provider to Microsoft OLE DB Provider for Oracle, on
> connection tab I enter
> (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST => server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID => ServiceName)))
> I enter the username and password, hit the test and everything tests
> out ok. I ok out, go to create a new report and when I go to use the
> DataSource I get an error of
> A connection cannot be made to the database.
> Set and test the connection string.
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater.
> Any help would be appreciated. Thanks.
>|||I ran into this exact same problem yesterday...you need to install the
Oracle Client Tools on the report server. w/o the client tools your
windows box doesn't natively understand how to connect to the oracle
db.
After you install the Oracle Client on the report server you will need
to update your tnsnames.ora and sqlnet.ora files with the correct host
settings. Your Oracle dba's should have these but here's some examples
to get you going in case they dont...
If you use the default install of the client tools you will find these
files in the following location (C:\oracle\ora90\network\ADMIN)
tnsnames.ora example
================================<Server Alias> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.somewhere.com)(PORT =9999))
)
(CONNECT_DATA = (SERVICE_NAME = <ServiceName>)
)
)
sqlnet.ora example
================================SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
Hope this helps!
Cheers
--
Ben Sullins
I create a new Shared Data Source, give it a name, then hit the edit
button. Set the provider to Microsoft OLE DB Provider for Oracle, on
connection tab I enter
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID = ServiceName)))
I enter the username and password, hit the test and everything tests
out ok. I ok out, go to create a new report and when I go to use the
DataSource I get an error of
A connection cannot be made to the database.
Set and test the connection string.
System.Data.OracleClient requires Oracle client software version 8.1.7
or greater.
Any help would be appreciated. Thanks.OK, are you using RS 2000. Here is how it works in RS 2000. When creating
the query it uses OLE DB if you are using the graphical editor (4 panes). If
you use the generic it uses the dotnet provider. The generic is 2 panes
(there is a button to switch to generic to the right of the ...). When
running the report it uses the dotnet provider. The reason for this was that
VS 2003 query designer did not have support for the dotnet provider so they
did this workaround. The dotnet provider requires 8.1.7 client to be
installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jdcamp" <camsam50@.hotmail.com> wrote in message
news:1145550738.077059.160290@.t31g2000cwb.googlegroups.com...
>I am trying to connect to an Oracle database using Reporting Services.
> I create a new Shared Data Source, give it a name, then hit the edit
> button. Set the provider to Microsoft OLE DB Provider for Oracle, on
> connection tab I enter
> (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST => server.somewhere.com) (PORT=99999)) (CONNECT_DATA = (SID => ServiceName)))
> I enter the username and password, hit the test and everything tests
> out ok. I ok out, go to create a new report and when I go to use the
> DataSource I get an error of
> A connection cannot be made to the database.
> Set and test the connection string.
> System.Data.OracleClient requires Oracle client software version 8.1.7
> or greater.
> Any help would be appreciated. Thanks.
>|||I ran into this exact same problem yesterday...you need to install the
Oracle Client Tools on the report server. w/o the client tools your
windows box doesn't natively understand how to connect to the oracle
db.
After you install the Oracle Client on the report server you will need
to update your tnsnames.ora and sqlnet.ora files with the correct host
settings. Your Oracle dba's should have these but here's some examples
to get you going in case they dont...
If you use the default install of the client tools you will find these
files in the following location (C:\oracle\ora90\network\ADMIN)
tnsnames.ora example
================================<Server Alias> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server.somewhere.com)(PORT =9999))
)
(CONNECT_DATA = (SERVICE_NAME = <ServiceName>)
)
)
sqlnet.ora example
================================SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
Hope this helps!
Cheers
--
Ben Sullins
Monday, March 12, 2012
Oracle Support
I'm hoping someone at MS is monitoring this and can give us some hope.
Another thread in this forum suggests that Reporting Services Oracle support
is not all there yet. Is this true? If so, what's the plan?
My specific need is to execute Oracle [stored] procedures that take
parameters to build my reports. Can I use RS or not?
Oracle's supposed to be supported, and executing a stored procedure to get
data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
it. I'm still trying, but so far I haven't read that it can't be done.
PeterIt can definitely be done. Oracle is not supported for Report Models but if
you are using regular reports then yes, you can use Oracle stored
procedures.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this article for general information about how to connect to Oracle
> from RS:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> Choose "Oracle" as data source type which will give you the managed
> provider
> for Oracle. Also make sure that you use the text-based generic query
> designer (2 panes) and not the visual query designer (4 panes) - you can
> switch between them through an icon on the toolbar in the data view of
> report designer.
>
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
>
> Finally, in the generic query designer, just specify the name of the
> stored
> procedure without arguments and the parameters should get detected
> automatically.
>
> There also have been several discussion threads about Oracle stored
> procedures on this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Here's a post from a MS employee. Note that it is for RS 2003, still, even
with 2005 it should help you;
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this article for general information about how to connect to Oracle
> from RS:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> Choose "Oracle" as data source type which will give you the managed
> provider
> for Oracle. Also make sure that you use the text-based generic query
> designer (2 panes) and not the visual query designer (4 panes) - you can
> switch between them through an icon on the toolbar in the data view of
> report designer.
>
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
>
> Finally, in the generic query designer, just specify the name of the
> stored
> procedure without arguments and the parameters should get detected
> automatically.
>
> There also have been several discussion threads about Oracle stored
> procedures on this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Thanks, Bruce. The links you provided clarified a few things, but I must
still be missing something.
In Oracle I have written the following fairly simple procedure called
SP_HFM_EPRO_NEG_NUM_REPORT:
AS
BEGIN
DECLARE
CURSOR report_cursor IS
SELECT business_unit
FROM sysadm.ps_req_hdr;
REPORT_RECORD report_cursor%rowtype;
BEGIN
OPEN report_cursor;
LOOP
FETCH report_cursor INTO report_record;
EXIT WHEN report_cursor%notfound;
END LOOP;
END;
END;
I've created a datasource that correctly points to the Oracle instance and a
dataset the contains only the name of the procedure. When I run it I get no
errors, but I get no results, either. My next step would be to populate a
table with data, but with nothing being returned I don't know how.
Another thread in this forum suggests that Reporting Services Oracle support
is not all there yet. Is this true? If so, what's the plan?
My specific need is to execute Oracle [stored] procedures that take
parameters to build my reports. Can I use RS or not?
Oracle's supposed to be supported, and executing a stored procedure to get
data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
it. I'm still trying, but so far I haven't read that it can't be done.
PeterIt can definitely be done. Oracle is not supported for Report Models but if
you are using regular reports then yes, you can use Oracle stored
procedures.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this article for general information about how to connect to Oracle
> from RS:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> Choose "Oracle" as data source type which will give you the managed
> provider
> for Oracle. Also make sure that you use the text-based generic query
> designer (2 panes) and not the visual query designer (4 panes) - you can
> switch between them through an icon on the toolbar in the data view of
> report designer.
>
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
>
> Finally, in the generic query designer, just specify the name of the
> stored
> procedure without arguments and the parameters should get detected
> automatically.
>
> There also have been several discussion threads about Oracle stored
> procedures on this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Here's a post from a MS employee. Note that it is for RS 2003, still, even
with 2005 it should help you;
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this article for general information about how to connect to Oracle
> from RS:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
>
> Choose "Oracle" as data source type which will give you the managed
> provider
> for Oracle. Also make sure that you use the text-based generic query
> designer (2 panes) and not the visual query designer (4 panes) - you can
> switch between them through an icon on the toolbar in the data view of
> report designer.
>
> In addition, how do you return the data from your stored procedure? Note:
> only an out ref cursor is supported. Please follow the guidelines in the
> following article on MSDN (scroll down to the section where it talks about
> "Oracle REF CURSORs") on how to design the Oracle stored procedure:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
> To use a stored procedure with regular out parameters, you should either
> remove the parameter (if it is possible) or write a little wrapper around
> the original stored procedure which checks the result of the out parameter
> and just returns the out ref cursor but no out parameter.
>
> Finally, in the generic query designer, just specify the name of the
> stored
> procedure without arguments and the parameters should get detected
> automatically.
>
> There also have been several discussion threads about Oracle stored
> procedures on this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Thanks, Bruce. The links you provided clarified a few things, but I must
still be missing something.
In Oracle I have written the following fairly simple procedure called
SP_HFM_EPRO_NEG_NUM_REPORT:
AS
BEGIN
DECLARE
CURSOR report_cursor IS
SELECT business_unit
FROM sysadm.ps_req_hdr;
REPORT_RECORD report_cursor%rowtype;
BEGIN
OPEN report_cursor;
LOOP
FETCH report_cursor INTO report_record;
EXIT WHEN report_cursor%notfound;
END LOOP;
END;
END;
I've created a datasource that correctly points to the Oracle instance and a
dataset the contains only the name of the procedure. When I run it I get no
errors, but I get no results, either. My next step would be to populate a
table with data, but with nothing being returned I don't know how.
Oracle Stored Procedure flakiness...
I'm on a deadline so any help would be much appreciated.
I am building a Reporting Services report that takes a number of
parameters with which to call a stored procedure. I am using the
Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
provider that I have manually installed does not show up as a choice,
but that's for another day.)
Ultimately, this report will be rendered from an ASP.NET front-end Web
application. But in order to do the formatting etc. I had it working in
MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
for a while. A mixture of dates, floats, varchar2s and numbers.
Now, I'm not sure exactly what happened, but to the best of my
recollection:
When calling the report programmtically, it was trying to do a query
for the first parameter and was failing. I didn't need it to do the
query any longer (the front end was handling that). I modified the
report parameters such that it no longer used a Query.
Now I get "PLS-00306 Wrong number or types of arguments..." when I
attempt to render the report either in MSRS HTML Viewer or through my
ASP.NET application. I've tried narrowing it down and it seems when I
add a third parameter, it fails.
I'm sorry for the long post and lack of details, but has anyone else
experienced flakiness with Report Parameters, Stored Procedures and/or
Oracle?
Regards,
PeterMy guess here is that there is a mismatch between your query parameters and
report parameters. They are two different things but it isn't obvious since
RS creates the report parameters for you. Make sure the report work from
Report Manager.
Another possibility is that the report parameter is a string and your stored
procedure is an integer or something like that. When you add a parameter it
defaults to string. Another area to check.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thanks for the quick reply Bruce.
I wish that were the case :-). I've dumbed it down so I'm passing a
VARCHAR2 and two NUMBERS.
Is there any way to see the command generated by MSRS to be sent to
Oracle? That would really help.
I am in class today, more details later. Gotta run.|||I have had some issues with SP going to Sybase. What helped me was to use
the generic query (there is a button to switch to that). Think of this as
passthrough. Put it in the format that you would if you were using a query
tool from Oracle. For instance I put in this for Sybase:
pr_test ?,?,?
Be sure to map it to the report parameters by clicking on the ... and going
to the parameter tab. I sometimes have to put in the ? on the left column
and then put in expression and then select the report parameter.
I'm not sure if ? is the placeholder for the parameter when doing this with
Oracle, it might not be. When you are in the generic query tab you are using
the managed provider for Oracle.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107372894.354513.125000@.o13g2000cwo.googlegroups.com...
> Thanks for the quick reply Bruce.
> I wish that were the case :-). I've dumbed it down so I'm passing a
> VARCHAR2 and two NUMBERS.
> Is there any way to see the command generated by MSRS to be sent to
> Oracle? That would really help.
> I am in class today, more details later. Gotta run.
>|||It won't work through the OleDB provider at design time. Choose "Oracle" as
data source type which will give you the managed provider for Oracle. Also
make sure that you use the text-based generic query designer (2 panes) and
not the visual query designer (4 panes) - you can switch between them
through an icon on the toolbar in the data view of report designer.
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
Finally, in the generic query designer, just specify the name of the stored
procedure without arguments and the parameters should get detected
automatically.
BTW: it is possible to switch from the managed Oracle to the OleDB provider
once the entire report is designed and done. E.g. after you published the
report to the production environment you can switch to the OleDB provider
using the steps discussed in detail in this related newsgroup thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/782c9647e34cb1f5/8b45a452801e7b77?q=Oracle+REF+CURSORS&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fgroup%3Dmicrosoft.public.sqlserver.reportingsvcs%26q%3DOracle+REF+CURSORS%26qt_g%3D1%26searchnow%3DSearch+this+group%26&_doneTitle=Back+to+Search&&d#8b45a452801e7b77
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thank you for the response Robert.
My stored procedure is being called when I run the Dataset that
references it from the Data pane in the Designer. It's when the stored
procedure is called from the preview pane that I get an error.
Since posting this issue, I've worked around it (somewhat) by passing
my stored procedure only one parameter: an Xml Document containing the
actual 20 or so parameters. I was getting tired of rebuilding the
parameter list (which occasionally fixed the problem).
However, please see my more recent post concerning HTML output caching.
I'm wondering if my single ~1500 character parameter is causing issues
with MSRS determining when to return cached versions of the HTML output
(even though the parameter string differes).
Once again thank you for your help. My company is evaluating moving
from an expensive and really buggy high-end reporting system to MSRS. I
am doing the proof-of-concept work and the HTML issue is causing some
concern about adopting MSRS. I'd really love to whole-heartedly endorse
it! :-)
peter|||Here is an idea. Have a parameter table that you fill with the 20 parameters
and then pass that primary key as the single parameter to RS. The parameter
will definitely be different and your caching problem will go away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107784198.997079.73660@.c13g2000cwb.googlegroups.com...
> Thank you for the response Robert.
> My stored procedure is being called when I run the Dataset that
> references it from the Data pane in the Designer. It's when the stored
> procedure is called from the preview pane that I get an error.
> Since posting this issue, I've worked around it (somewhat) by passing
> my stored procedure only one parameter: an Xml Document containing the
> actual 20 or so parameters. I was getting tired of rebuilding the
> parameter list (which occasionally fixed the problem).
> However, please see my more recent post concerning HTML output caching.
> I'm wondering if my single ~1500 character parameter is causing issues
> with MSRS determining when to return cached versions of the HTML output
> (even though the parameter string differes).
> Once again thank you for your help. My company is evaluating moving
> from an expensive and really buggy high-end reporting system to MSRS. I
> am doing the proof-of-concept work and the HTML issue is causing some
> concern about adopting MSRS. I'd really love to whole-heartedly endorse
> it! :-)
> peter
>|||Thank you Bruce, I might just give that a try. I am still hoping for a
MSFT solution though.|||My feeling is that this will improve in the next version. I just got an
email from Oracle about additional Oracle design tools for VS beta 2. So it
looks like the next version of VS will have better support for Oracle from
Oracle. Plus MS had to work around some tools limitations for this release.
The design tools were designed around oledb, not dotnet. I expect this to
improve dramatically with the next version (hopefully late summer).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107800057.304345.89930@.z14g2000cwz.googlegroups.com...
> Thank you Bruce, I might just give that a try. I am still hoping for a
> MSFT solution though.
>
I am building a Reporting Services report that takes a number of
parameters with which to call a stored procedure. I am using the
Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
provider that I have manually installed does not show up as a choice,
but that's for another day.)
Ultimately, this report will be rendered from an ASP.NET front-end Web
application. But in order to do the formatting etc. I had it working in
MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
for a while. A mixture of dates, floats, varchar2s and numbers.
Now, I'm not sure exactly what happened, but to the best of my
recollection:
When calling the report programmtically, it was trying to do a query
for the first parameter and was failing. I didn't need it to do the
query any longer (the front end was handling that). I modified the
report parameters such that it no longer used a Query.
Now I get "PLS-00306 Wrong number or types of arguments..." when I
attempt to render the report either in MSRS HTML Viewer or through my
ASP.NET application. I've tried narrowing it down and it seems when I
add a third parameter, it fails.
I'm sorry for the long post and lack of details, but has anyone else
experienced flakiness with Report Parameters, Stored Procedures and/or
Oracle?
Regards,
PeterMy guess here is that there is a mismatch between your query parameters and
report parameters. They are two different things but it isn't obvious since
RS creates the report parameters for you. Make sure the report work from
Report Manager.
Another possibility is that the report parameter is a string and your stored
procedure is an integer or something like that. When you add a parameter it
defaults to string. Another area to check.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thanks for the quick reply Bruce.
I wish that were the case :-). I've dumbed it down so I'm passing a
VARCHAR2 and two NUMBERS.
Is there any way to see the command generated by MSRS to be sent to
Oracle? That would really help.
I am in class today, more details later. Gotta run.|||I have had some issues with SP going to Sybase. What helped me was to use
the generic query (there is a button to switch to that). Think of this as
passthrough. Put it in the format that you would if you were using a query
tool from Oracle. For instance I put in this for Sybase:
pr_test ?,?,?
Be sure to map it to the report parameters by clicking on the ... and going
to the parameter tab. I sometimes have to put in the ? on the left column
and then put in expression and then select the report parameter.
I'm not sure if ? is the placeholder for the parameter when doing this with
Oracle, it might not be. When you are in the generic query tab you are using
the managed provider for Oracle.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107372894.354513.125000@.o13g2000cwo.googlegroups.com...
> Thanks for the quick reply Bruce.
> I wish that were the case :-). I've dumbed it down so I'm passing a
> VARCHAR2 and two NUMBERS.
> Is there any way to see the command generated by MSRS to be sent to
> Oracle? That would really help.
> I am in class today, more details later. Gotta run.
>|||It won't work through the OleDB provider at design time. Choose "Oracle" as
data source type which will give you the managed provider for Oracle. Also
make sure that you use the text-based generic query designer (2 panes) and
not the visual query designer (4 panes) - you can switch between them
through an icon on the toolbar in the data view of report designer.
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter.
Finally, in the generic query designer, just specify the name of the stored
procedure without arguments and the parameters should get detected
automatically.
BTW: it is possible to switch from the managed Oracle to the OleDB provider
once the entire report is designed and done. E.g. after you published the
report to the production environment you can switch to the OleDB provider
using the steps discussed in detail in this related newsgroup thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/782c9647e34cb1f5/8b45a452801e7b77?q=Oracle+REF+CURSORS&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fgroup%3Dmicrosoft.public.sqlserver.reportingsvcs%26q%3DOracle+REF+CURSORS%26qt_g%3D1%26searchnow%3DSearch+this+group%26&_doneTitle=Back+to+Search&&d#8b45a452801e7b77
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thank you for the response Robert.
My stored procedure is being called when I run the Dataset that
references it from the Data pane in the Designer. It's when the stored
procedure is called from the preview pane that I get an error.
Since posting this issue, I've worked around it (somewhat) by passing
my stored procedure only one parameter: an Xml Document containing the
actual 20 or so parameters. I was getting tired of rebuilding the
parameter list (which occasionally fixed the problem).
However, please see my more recent post concerning HTML output caching.
I'm wondering if my single ~1500 character parameter is causing issues
with MSRS determining when to return cached versions of the HTML output
(even though the parameter string differes).
Once again thank you for your help. My company is evaluating moving
from an expensive and really buggy high-end reporting system to MSRS. I
am doing the proof-of-concept work and the HTML issue is causing some
concern about adopting MSRS. I'd really love to whole-heartedly endorse
it! :-)
peter|||Here is an idea. Have a parameter table that you fill with the 20 parameters
and then pass that primary key as the single parameter to RS. The parameter
will definitely be different and your caching problem will go away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107784198.997079.73660@.c13g2000cwb.googlegroups.com...
> Thank you for the response Robert.
> My stored procedure is being called when I run the Dataset that
> references it from the Data pane in the Designer. It's when the stored
> procedure is called from the preview pane that I get an error.
> Since posting this issue, I've worked around it (somewhat) by passing
> my stored procedure only one parameter: an Xml Document containing the
> actual 20 or so parameters. I was getting tired of rebuilding the
> parameter list (which occasionally fixed the problem).
> However, please see my more recent post concerning HTML output caching.
> I'm wondering if my single ~1500 character parameter is causing issues
> with MSRS determining when to return cached versions of the HTML output
> (even though the parameter string differes).
> Once again thank you for your help. My company is evaluating moving
> from an expensive and really buggy high-end reporting system to MSRS. I
> am doing the proof-of-concept work and the HTML issue is causing some
> concern about adopting MSRS. I'd really love to whole-heartedly endorse
> it! :-)
> peter
>|||Thank you Bruce, I might just give that a try. I am still hoping for a
MSFT solution though.|||My feeling is that this will improve in the next version. I just got an
email from Oracle about additional Oracle design tools for VS beta 2. So it
looks like the next version of VS will have better support for Oracle from
Oracle. Plus MS had to work around some tools limitations for this release.
The design tools were designed around oledb, not dotnet. I expect this to
improve dramatically with the next version (hopefully late summer).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107800057.304345.89930@.z14g2000cwz.googlegroups.com...
> Thank you Bruce, I might just give that a try. I am still hoping for a
> MSFT solution though.
>
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
> > >
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 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!!!
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!!!
Friday, March 9, 2012
Oracle Query
Good day
I am having trouble with getting an Oracle query right for SQL
reporting service.
I wrote a front end to execute the query and it works fine. Now the
next step is to allow users to get a report on any number of clients.
The way I do this for normal SQL Server, is to build SQL with "AND"s
and "OR"s in code, and then send it to reporting services as a
parameter and append it.
This is the text
="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
And the value of my parameter is:
ID = 'ALE' OR ID = 'ALO'
I keep getting errors like "SQL command not properly ended".
Have anybody done something similar? Am I missing something?
Any help will be appreciated.
Kind regards,
KarlKarl,
You need to use a (:) colon instead of the @. sign.
For example,
Select *
From tblYours
Where ID=:ID
Hope this helps,
rwiethron
"karlbasson@.gmail.com" wrote:
> Good day
> I am having trouble with getting an Oracle query right for SQL
> reporting service.
> I wrote a front end to execute the query and it works fine. Now the
> next step is to allow users to get a report on any number of clients.
> The way I do this for normal SQL Server, is to build SQL with "AND"s
> and "OR"s in code, and then send it to reporting services as a
> parameter and append it.
> This is the text
> ="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
> And the value of my parameter is:
> ID = 'ALE' OR ID = 'ALO'
> I keep getting errors like "SQL command not properly ended".
> Have anybody done something similar? Am I missing something?
> Any help will be appreciated.
> Kind regards,
> Karl
>
I am having trouble with getting an Oracle query right for SQL
reporting service.
I wrote a front end to execute the query and it works fine. Now the
next step is to allow users to get a report on any number of clients.
The way I do this for normal SQL Server, is to build SQL with "AND"s
and "OR"s in code, and then send it to reporting services as a
parameter and append it.
This is the text
="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
And the value of my parameter is:
ID = 'ALE' OR ID = 'ALO'
I keep getting errors like "SQL command not properly ended".
Have anybody done something similar? Am I missing something?
Any help will be appreciated.
Kind regards,
KarlKarl,
You need to use a (:) colon instead of the @. sign.
For example,
Select *
From tblYours
Where ID=:ID
Hope this helps,
rwiethron
"karlbasson@.gmail.com" wrote:
> Good day
> I am having trouble with getting an Oracle query right for SQL
> reporting service.
> I wrote a front end to execute the query and it works fine. Now the
> next step is to allow users to get a report on any number of clients.
> The way I do this for normal SQL Server, is to build SQL with "AND"s
> and "OR"s in code, and then send it to reporting services as a
> parameter and append it.
> This is the text
> ="SELECT * FROM TABLE WHERE "+ Parameters!Id.Value
> And the value of my parameter is:
> ID = 'ALE' OR ID = 'ALO'
> I keep getting errors like "SQL command not properly ended".
> Have anybody done something similar? Am I missing something?
> Any help will be appreciated.
> Kind regards,
> Karl
>
Saturday, February 25, 2012
oracle driver
Where can I download the Oracle driver for SQL Server Reporting Server? The
Oracle Client is 500MB, wasn't sure if there's something smaller for just the
driver. Thanks!I don't think there is a smaller download for the Oracle client. Make sure
you are installing version 8.1.7 or later of the Oracle client.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> Where can I download the Oracle driver for SQL Server Reporting Server?
> The
> Oracle Client is 500MB, wasn't sure if there's something smaller for just
> the
> driver. Thanks!|||Hi Robert,
Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
places. I'm running into a more critical issue with executing Oracle reports.
I kept getting the "rsErrorOpeningConnection with Oracle" when running from
the server and I know I can make the db connection from the server. And I
also double check the permission in the Oracle folder per one of your
posting. Could you think of anything else I can try?
Thank you very much!
"Robert Bruckner [MSFT]" wrote:
> I don't think there is a smaller download for the Oracle client. Make sure
> you are installing version 8.1.7 or later of the Oracle client.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> > Where can I download the Oracle driver for SQL Server Reporting Server?
> > The
> > Oracle Client is 500MB, wasn't sure if there's something smaller for just
> > the
> > driver. Thanks!
>
>|||My first suggestion is to make sure that you have rebooted your server once
after the Oracle client installation happened (I have seen some machines
where applications running inside ASP.NET, such as report server, cannot
access the Oracle client without a reboot after the client installation).
Then, if this is RS 2005, you have to apply the following steps:
* make sure the WebServiceIdentity is configured through the RS
Configuration tool; often it is set to the Network Service user. You can
also check it by looking up the <WebServiceAccount> element in the
RSReportServer.config file.
* For the specified <WebServiceAccount> apply these steps on the Oracle
client installation directory (Oracle\Oracle version):
Right-click the directory, and then Properties. On the security tab, add the
<WebServiceAccount> and grant read&execute permission. Important: click on
the advanced button and select "Allow inheritable permissions from the
parent ..." before you click OK!
Note: it is also necessary to grant the account read&execute permissions
that is used to execute the RS Windows Service. For RS 2005, both the RS
Windows Service account and the configured RS Web Service account must have
permissions to access the files of the Oracle client installation in the
file system! On RS 2000, this was generally only needed for the RS Windows
Service account.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
PS: I guess I should know a few things about RS since I'm
designing/implementing some areas of the product :)
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:6BF6E4C3-B612-454D-99A7-DDFF431A04D2@.microsoft.com...
> Hi Robert,
> Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
> places. I'm running into a more critical issue with executing Oracle
> reports.
> I kept getting the "rsErrorOpeningConnection with Oracle" when running
> from
> the server and I know I can make the db connection from the server. And I
> also double check the permission in the Oracle folder per one of your
> posting. Could you think of anything else I can try?
> Thank you very much!
> "Robert Bruckner [MSFT]" wrote:
>> I don't think there is a smaller download for the Oracle client. Make
>> sure
>> you are installing version 8.1.7 or later of the Oracle client.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
>> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
>> > Where can I download the Oracle driver for SQL Server Reporting Server?
>> > The
>> > Oracle Client is 500MB, wasn't sure if there's something smaller for
>> > just
>> > the
>> > driver. Thanks!
>>
Oracle Client is 500MB, wasn't sure if there's something smaller for just the
driver. Thanks!I don't think there is a smaller download for the Oracle client. Make sure
you are installing version 8.1.7 or later of the Oracle client.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> Where can I download the Oracle driver for SQL Server Reporting Server?
> The
> Oracle Client is 500MB, wasn't sure if there's something smaller for just
> the
> driver. Thanks!|||Hi Robert,
Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
places. I'm running into a more critical issue with executing Oracle reports.
I kept getting the "rsErrorOpeningConnection with Oracle" when running from
the server and I know I can make the db connection from the server. And I
also double check the permission in the Oracle folder per one of your
posting. Could you think of anything else I can try?
Thank you very much!
"Robert Bruckner [MSFT]" wrote:
> I don't think there is a smaller download for the Oracle client. Make sure
> you are installing version 8.1.7 or later of the Oracle client.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
> > Where can I download the Oracle driver for SQL Server Reporting Server?
> > The
> > Oracle Client is 500MB, wasn't sure if there's something smaller for just
> > the
> > driver. Thanks!
>
>|||My first suggestion is to make sure that you have rebooted your server once
after the Oracle client installation happened (I have seen some machines
where applications running inside ASP.NET, such as report server, cannot
access the Oracle client without a reboot after the client installation).
Then, if this is RS 2005, you have to apply the following steps:
* make sure the WebServiceIdentity is configured through the RS
Configuration tool; often it is set to the Network Service user. You can
also check it by looking up the <WebServiceAccount> element in the
RSReportServer.config file.
* For the specified <WebServiceAccount> apply these steps on the Oracle
client installation directory (Oracle\Oracle version):
Right-click the directory, and then Properties. On the security tab, add the
<WebServiceAccount> and grant read&execute permission. Important: click on
the advanced button and select "Allow inheritable permissions from the
parent ..." before you click OK!
Note: it is also necessary to grant the account read&execute permissions
that is used to execute the RS Windows Service. For RS 2005, both the RS
Windows Service account and the configured RS Web Service account must have
permissions to access the files of the Oracle client installation in the
file system! On RS 2000, this was generally only needed for the RS Windows
Service account.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
PS: I guess I should know a few things about RS since I'm
designing/implementing some areas of the product :)
"Teresa" <Teresa@.discussions.microsoft.com> wrote in message
news:6BF6E4C3-B612-454D-99A7-DDFF431A04D2@.microsoft.com...
> Hi Robert,
> Thanks! You seem very knowledgable with Rpt Svcs. I saw your posts in many
> places. I'm running into a more critical issue with executing Oracle
> reports.
> I kept getting the "rsErrorOpeningConnection with Oracle" when running
> from
> the server and I know I can make the db connection from the server. And I
> also double check the permission in the Oracle folder per one of your
> posting. Could you think of anything else I can try?
> Thank you very much!
> "Robert Bruckner [MSFT]" wrote:
>> I don't think there is a smaller download for the Oracle client. Make
>> sure
>> you are installing version 8.1.7 or later of the Oracle client.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Teresa" <Teresa@.discussions.microsoft.com> wrote in message
>> news:FB197F40-D82C-4093-8F5F-73291E74C0C9@.microsoft.com...
>> > Where can I download the Oracle driver for SQL Server Reporting Server?
>> > The
>> > Oracle Client is 500MB, wasn't sure if there's something smaller for
>> > just
>> > the
>> > driver. Thanks!
>>
Oracle DB Link in Reporting Services Query String
I am trying to select for a report some data from an Oracle table through a
DB Link, using a select statement:
SELECT a.filed1, a.field2, dbl.field3, dbl.field4
FROM table_name1 a, table_name2@.db_link_name dbl
WHERE a.filed1=dbl.filed5
However, the SQL Report Wizard returns an error â'Invalid characterâ'
(selected data source is ORACLE type). Apparently, it doesnâ't like â'@.â'
character in the string. To find a way around I have tried to create an
Oracle stored procedure which returns Oracle REF Cursor created using the
same select statement. The procedure was compiled and I changed data type in
the dataset to â'Stored Procedureâ' and query string to the procedure name.
But, again, a failure was received since it canâ't refresh the list of
parameters and fields.
Excluding reference to the DB Link from both the Select statement and the
stored procedure solves the problem, but I need the data from it.
Is there another way to retrieve the data for a report through an Oracle DB
Link?Two things to try. First off, did you do this via the generic query
designer. If not try that. Next, what happens if you put double quotes
around it. I.e.
SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
"table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
Just a wild guess whether the double quote would work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> I am trying to select for a report some data from an Oracle table through
a
> DB Link, using a select statement:
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> FROM table_name1 a, table_name2@.db_link_name dbl
> WHERE a.filed1=dbl.filed5
> However, the SQL Report Wizard returns an error "Invalid character"
> (selected data source is ORACLE type). Apparently, it doesn't like "@."
> character in the string. To find a way around I have tried to create an
> Oracle stored procedure which returns Oracle REF Cursor created using the
> same select statement. The procedure was compiled and I changed data type
in
> the dataset to "Stored Procedure" and query string to the procedure name.
> But, again, a failure was received since it can't refresh the list of
> parameters and fields.
> Excluding reference to the DB Link from both the Select statement and the
> stored procedure solves the problem, but I need the data from it.
> Is there another way to retrieve the data for a report through an Oracle
DB
> Link?
>|||Thanks Bruce,
I am afraid, I canâ't use the generic query designer, since it canâ't access a
table though a db link. I can only add a table or a view in the graphical
schema representation.
After I tried to edit the Select statement and put a double quote the syntax
problem was resolved, however I canâ't even save the query string, because of
an error message:
â'Couldnâ't generate a list of fields for the query.â' A similar message was
received when I was trying to use a stored procedure with ref cursor as OUT
parameter. It looks like because the db link table column list canâ't be
generated a critical run time error is raised and it stops execution of the
procedure.
Is there any way to solve this problem, for example to try to enter the list
of parameters and fields manually?
"Bruce L-C [MVP]" wrote:
> Two things to try. First off, did you do this via the generic query
> designer. If not try that. Next, what happens if you put double quotes
> around it. I.e.
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> Just a wild guess whether the double quote would work.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > I am trying to select for a report some data from an Oracle table through
> a
> > DB Link, using a select statement:
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > FROM table_name1 a, table_name2@.db_link_name dbl
> > WHERE a.filed1=dbl.filed5
> >
> > However, the SQL Report Wizard returns an error "Invalid character"
> > (selected data source is ORACLE type). Apparently, it doesn't like "@."
> > character in the string. To find a way around I have tried to create an
> > Oracle stored procedure which returns Oracle REF Cursor created using the
> > same select statement. The procedure was compiled and I changed data type
> in
> > the dataset to "Stored Procedure" and query string to the procedure name.
> > But, again, a failure was received since it can't refresh the list of
> > parameters and fields.
> >
> > Excluding reference to the DB Link from both the Select statement and the
> > stored procedure solves the problem, but I need the data from it.
> >
> > Is there another way to retrieve the data for a report through an Oracle
> DB
> > Link?
> >
> >
>
>|||There are some unusual things going on with Oracle. When you pick the Oracle
provider it is using Oledb provider when you are in the graphical query
designer. If you are in the generic query designer it is using the dotnet
provider. Plus, when deployed it will be using the dotnet provider. The
reason for all of this is that the graphical query designer component is a
shared component that knows nothing about dotnet. Hopefully with Widbey this
will no longer be true but for now, what I said is try.
Now, the generic query designer should act like a passthrough query. Have
you tried it since you started putting in the double quotes?
You can definitely add the fields manually (right click on the fields list).
The graphical designer does a lot with your query. That is why I suggested
the generic. I would spend some more time in the generic trying to get it to
work (generic plus adding the fields manually is the most likely combination
to work). You can always view the rdl and put the query string in that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:F03A20D6-FE5E-479A-93B0-BBCD0B9DAA3C@.microsoft.com...
> Thanks Bruce,
> I am afraid, I can't use the generic query designer, since it can't access
a
> table though a db link. I can only add a table or a view in the graphical
> schema representation.
> After I tried to edit the Select statement and put a double quote the
syntax
> problem was resolved, however I can't even save the query string, because
of
> an error message:
> "Couldn't generate a list of fields for the query." A similar message was
> received when I was trying to use a stored procedure with ref cursor as
OUT
> parameter. It looks like because the db link table column list can't be
> generated a critical run time error is raised and it stops execution of
the
> procedure.
> Is there any way to solve this problem, for example to try to enter the
list
> of parameters and fields manually?
>
> "Bruce L-C [MVP]" wrote:
> > Two things to try. First off, did you do this via the generic query
> > designer. If not try that. Next, what happens if you put double quotes
> > around it. I.e.
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> > "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> >
> > Just a wild guess whether the double quote would work.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> > news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > > I am trying to select for a report some data from an Oracle table
through
> > a
> > > DB Link, using a select statement:
> > >
> > > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > > FROM table_name1 a, table_name2@.db_link_name dbl
> > > WHERE a.filed1=dbl.filed5
> > >
> > > However, the SQL Report Wizard returns an error "Invalid character"
> > > (selected data source is ORACLE type). Apparently, it doesn't like
"@."
> > > character in the string. To find a way around I have tried to create
an
> > > Oracle stored procedure which returns Oracle REF Cursor created using
the
> > > same select statement. The procedure was compiled and I changed data
type
> > in
> > > the dataset to "Stored Procedure" and query string to the procedure
name.
> > > But, again, a failure was received since it can't refresh the list of
> > > parameters and fields.
> > >
> > > Excluding reference to the DB Link from both the Select statement and
the
> > > stored procedure solves the problem, but I need the data from it.
> > >
> > > Is there another way to retrieve the data for a report through an
Oracle
> > DB
> > > Link?
> > >
> > >
> >
> >
> >
DB Link, using a select statement:
SELECT a.filed1, a.field2, dbl.field3, dbl.field4
FROM table_name1 a, table_name2@.db_link_name dbl
WHERE a.filed1=dbl.filed5
However, the SQL Report Wizard returns an error â'Invalid characterâ'
(selected data source is ORACLE type). Apparently, it doesnâ't like â'@.â'
character in the string. To find a way around I have tried to create an
Oracle stored procedure which returns Oracle REF Cursor created using the
same select statement. The procedure was compiled and I changed data type in
the dataset to â'Stored Procedureâ' and query string to the procedure name.
But, again, a failure was received since it canâ't refresh the list of
parameters and fields.
Excluding reference to the DB Link from both the Select statement and the
stored procedure solves the problem, but I need the data from it.
Is there another way to retrieve the data for a report through an Oracle DB
Link?Two things to try. First off, did you do this via the generic query
designer. If not try that. Next, what happens if you put double quotes
around it. I.e.
SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
"table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
Just a wild guess whether the double quote would work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> I am trying to select for a report some data from an Oracle table through
a
> DB Link, using a select statement:
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> FROM table_name1 a, table_name2@.db_link_name dbl
> WHERE a.filed1=dbl.filed5
> However, the SQL Report Wizard returns an error "Invalid character"
> (selected data source is ORACLE type). Apparently, it doesn't like "@."
> character in the string. To find a way around I have tried to create an
> Oracle stored procedure which returns Oracle REF Cursor created using the
> same select statement. The procedure was compiled and I changed data type
in
> the dataset to "Stored Procedure" and query string to the procedure name.
> But, again, a failure was received since it can't refresh the list of
> parameters and fields.
> Excluding reference to the DB Link from both the Select statement and the
> stored procedure solves the problem, but I need the data from it.
> Is there another way to retrieve the data for a report through an Oracle
DB
> Link?
>|||Thanks Bruce,
I am afraid, I canâ't use the generic query designer, since it canâ't access a
table though a db link. I can only add a table or a view in the graphical
schema representation.
After I tried to edit the Select statement and put a double quote the syntax
problem was resolved, however I canâ't even save the query string, because of
an error message:
â'Couldnâ't generate a list of fields for the query.â' A similar message was
received when I was trying to use a stored procedure with ref cursor as OUT
parameter. It looks like because the db link table column list canâ't be
generated a critical run time error is raised and it stops execution of the
procedure.
Is there any way to solve this problem, for example to try to enter the list
of parameters and fields manually?
"Bruce L-C [MVP]" wrote:
> Two things to try. First off, did you do this via the generic query
> designer. If not try that. Next, what happens if you put double quotes
> around it. I.e.
> SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> Just a wild guess whether the double quote would work.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > I am trying to select for a report some data from an Oracle table through
> a
> > DB Link, using a select statement:
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > FROM table_name1 a, table_name2@.db_link_name dbl
> > WHERE a.filed1=dbl.filed5
> >
> > However, the SQL Report Wizard returns an error "Invalid character"
> > (selected data source is ORACLE type). Apparently, it doesn't like "@."
> > character in the string. To find a way around I have tried to create an
> > Oracle stored procedure which returns Oracle REF Cursor created using the
> > same select statement. The procedure was compiled and I changed data type
> in
> > the dataset to "Stored Procedure" and query string to the procedure name.
> > But, again, a failure was received since it can't refresh the list of
> > parameters and fields.
> >
> > Excluding reference to the DB Link from both the Select statement and the
> > stored procedure solves the problem, but I need the data from it.
> >
> > Is there another way to retrieve the data for a report through an Oracle
> DB
> > Link?
> >
> >
>
>|||There are some unusual things going on with Oracle. When you pick the Oracle
provider it is using Oledb provider when you are in the graphical query
designer. If you are in the generic query designer it is using the dotnet
provider. Plus, when deployed it will be using the dotnet provider. The
reason for all of this is that the graphical query designer component is a
shared component that knows nothing about dotnet. Hopefully with Widbey this
will no longer be true but for now, what I said is try.
Now, the generic query designer should act like a passthrough query. Have
you tried it since you started putting in the double quotes?
You can definitely add the fields manually (right click on the fields list).
The graphical designer does a lot with your query. That is why I suggested
the generic. I would spend some more time in the generic trying to get it to
work (generic plus adding the fields manually is the most likely combination
to work). You can always view the rdl and put the query string in that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
news:F03A20D6-FE5E-479A-93B0-BBCD0B9DAA3C@.microsoft.com...
> Thanks Bruce,
> I am afraid, I can't use the generic query designer, since it can't access
a
> table though a db link. I can only add a table or a view in the graphical
> schema representation.
> After I tried to edit the Select statement and put a double quote the
syntax
> problem was resolved, however I can't even save the query string, because
of
> an error message:
> "Couldn't generate a list of fields for the query." A similar message was
> received when I was trying to use a stored procedure with ref cursor as
OUT
> parameter. It looks like because the db link table column list can't be
> generated a critical run time error is raised and it stops execution of
the
> procedure.
> Is there any way to solve this problem, for example to try to enter the
list
> of parameters and fields manually?
>
> "Bruce L-C [MVP]" wrote:
> > Two things to try. First off, did you do this via the generic query
> > designer. If not try that. Next, what happens if you put double quotes
> > around it. I.e.
> >
> > SELECT a.filed1, a.field2, dbl.field3, dbl.field4 FROM table_name1 a,
> > "table_name2@.db_link_name" dbl WHERE a.filed1=dbl.filed5
> >
> > Just a wild guess whether the double quote would work.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Anatoliy" <Anatoliy@.discussions.microsoft.com> wrote in message
> > news:19E95C96-1078-4E89-87D6-95420A8D4184@.microsoft.com...
> > > I am trying to select for a report some data from an Oracle table
through
> > a
> > > DB Link, using a select statement:
> > >
> > > SELECT a.filed1, a.field2, dbl.field3, dbl.field4
> > > FROM table_name1 a, table_name2@.db_link_name dbl
> > > WHERE a.filed1=dbl.filed5
> > >
> > > However, the SQL Report Wizard returns an error "Invalid character"
> > > (selected data source is ORACLE type). Apparently, it doesn't like
"@."
> > > character in the string. To find a way around I have tried to create
an
> > > Oracle stored procedure which returns Oracle REF Cursor created using
the
> > > same select statement. The procedure was compiled and I changed data
type
> > in
> > > the dataset to "Stored Procedure" and query string to the procedure
name.
> > > But, again, a failure was received since it can't refresh the list of
> > > parameters and fields.
> > >
> > > Excluding reference to the DB Link from both the Select statement and
the
> > > stored procedure solves the problem, but I need the data from it.
> > >
> > > Is there another way to retrieve the data for a report through an
Oracle
> > DB
> > > Link?
> > >
> > >
> >
> >
> >
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
>
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
>
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
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
Monday, February 20, 2012
Oracle client?
I'm using the Oracle data connector within Reporting Services because it uses
the Oracle client, which should be the most efficient.
There is very little description/documentation I can find in Reporting
Services help about this connector and it is difficult to figure out what it
is doing and what connection parameters it supports. Is it documented
anywhere?
For example, I tried to set the connection pool information in the
connection string (using the same properties as described in the ODP.NET
documentation), but for the oracle server log, it doesn't seem to be using
them correctly.
At this point I'm am considering writing a DPE for ODP.NET, just to make
sure I have that control.
Brendan.ODP.Net is not being used. Microsoft managed provider is being used. The
provider requires 8.1.7 or higher client to be installed I believe. It gets
even more confusing though. If you use the graphical query designer (4 pane)
it uses OLEDB. If you use generic query designer (2 pane) it uses the
managed provider. When you are at runtime it uses the MS managed provider
for Oracle. My suggestion is to always use the generic query designer so you
are developing with the same provider that you will be running under.
Hopefully this will get more clearcut with the next release (November).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brendan Whelan" <BrendanWhelan@.discussions.microsoft.com> wrote in message
news:94800733-02F7-4D4D-9782-72CADEC7FE6E@.microsoft.com...
> I'm using the Oracle data connector within Reporting Services because it
> uses
> the Oracle client, which should be the most efficient.
> There is very little description/documentation I can find in Reporting
> Services help about this connector and it is difficult to figure out what
> it
> is doing and what connection parameters it supports. Is it documented
> anywhere?
> For example, I tried to set the connection pool information in the
> connection string (using the same properties as described in the ODP.NET
> documentation), but for the oracle server log, it doesn't seem to be using
> them correctly.
> At this point I'm am considering writing a DPE for ODP.NET, just to make
> sure I have that control.
> Brendan.
>|||I use oracle and SRS now with no problems. you just have to install the
oracle client on the SRS server and configure a service using net manager.
once you can get to oracle using Sql plus then you can use the client to
connect to oracle. This is the fastest way using the native Oracle client.
Bruce L-C [MVP] wrote:
>ODP.Net is not being used. Microsoft managed provider is being used. The
>provider requires 8.1.7 or higher client to be installed I believe. It gets
>even more confusing though. If you use the graphical query designer (4 pane)
>it uses OLEDB. If you use generic query designer (2 pane) it uses the
>managed provider. When you are at runtime it uses the MS managed provider
>for Oracle. My suggestion is to always use the generic query designer so you
>are developing with the same provider that you will be running under.
>Hopefully this will get more clearcut with the next release (November).
>> I'm using the Oracle data connector within Reporting Services because it
>> uses
>[quoted text clipped - 15 lines]
>> Brendan.
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
the Oracle client, which should be the most efficient.
There is very little description/documentation I can find in Reporting
Services help about this connector and it is difficult to figure out what it
is doing and what connection parameters it supports. Is it documented
anywhere?
For example, I tried to set the connection pool information in the
connection string (using the same properties as described in the ODP.NET
documentation), but for the oracle server log, it doesn't seem to be using
them correctly.
At this point I'm am considering writing a DPE for ODP.NET, just to make
sure I have that control.
Brendan.ODP.Net is not being used. Microsoft managed provider is being used. The
provider requires 8.1.7 or higher client to be installed I believe. It gets
even more confusing though. If you use the graphical query designer (4 pane)
it uses OLEDB. If you use generic query designer (2 pane) it uses the
managed provider. When you are at runtime it uses the MS managed provider
for Oracle. My suggestion is to always use the generic query designer so you
are developing with the same provider that you will be running under.
Hopefully this will get more clearcut with the next release (November).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brendan Whelan" <BrendanWhelan@.discussions.microsoft.com> wrote in message
news:94800733-02F7-4D4D-9782-72CADEC7FE6E@.microsoft.com...
> I'm using the Oracle data connector within Reporting Services because it
> uses
> the Oracle client, which should be the most efficient.
> There is very little description/documentation I can find in Reporting
> Services help about this connector and it is difficult to figure out what
> it
> is doing and what connection parameters it supports. Is it documented
> anywhere?
> For example, I tried to set the connection pool information in the
> connection string (using the same properties as described in the ODP.NET
> documentation), but for the oracle server log, it doesn't seem to be using
> them correctly.
> At this point I'm am considering writing a DPE for ODP.NET, just to make
> sure I have that control.
> Brendan.
>|||I use oracle and SRS now with no problems. you just have to install the
oracle client on the SRS server and configure a service using net manager.
once you can get to oracle using Sql plus then you can use the client to
connect to oracle. This is the fastest way using the native Oracle client.
Bruce L-C [MVP] wrote:
>ODP.Net is not being used. Microsoft managed provider is being used. The
>provider requires 8.1.7 or higher client to be installed I believe. It gets
>even more confusing though. If you use the graphical query designer (4 pane)
>it uses OLEDB. If you use generic query designer (2 pane) it uses the
>managed provider. When you are at runtime it uses the MS managed provider
>for Oracle. My suggestion is to always use the generic query designer so you
>are developing with the same provider that you will be running under.
>Hopefully this will get more clearcut with the next release (November).
>> I'm using the Oracle data connector within Reporting Services because it
>> uses
>[quoted text clipped - 15 lines]
>> Brendan.
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
Subscribe to:
Posts (Atom)