Connect to Oracle database.

Hi!

Recently I started to use Reporting Services for a project I am working on. I have a set of Oracle stored procedures that I have to use. All of them return the data I need in output variables.

Any idea about how to use this kind of procedures with Reporting Services.

Thanks in advance for your help!First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server.

Check the following 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 !) instead of 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 (but not Output parameters!). 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 the following newsgroup:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs
You may want to search for these in case you are running into issues.
-- Robert|||

Hello Robert,

I have the problem to connect Oracle data for my Report designer.

1st I used ODBC, it does not work, and my reference book "MS SQL server 2000 Reporting service step by step" tells me that I must select Oracle data provide instead of the generic ODBC.

I created a new data source and after I enter the userId/pswd and click "Test Connection" button, it is OK. But when I click "run" icon to try, it gives me the following error messages:

An error occurred while executing the query, ORA-00936:Missing expression

Additiona information:

ORA-00936:missing expression(system.Data.OracleClient)

My version of reporting service is 2005. The setting of "Connection Properties" as follows:

Data Source: Oracle (OracleClient) Server name: creonpra Log On to the database: UserId/Pswd= qmprodviewer/*********

I have very less knowledge about Oracle. I do not know Oracle Client software's coverage. I checked my program and only find Oracle 8i of "Net8 Assistant", "Net8 Config Assistant" and Oracle ODBC test" on my desktop PC.

Please help me to resolve the problem

|||

Have you tried executing the stored procedure from your report designer? It may be that you are able to connect, but erroring out on something specific with the stored procedure.

I'd execute it from the Data tab and check if it returns data first and rule out that the stored procedure works or not.

Confysed by the roles

DaI have an application that uses the Extended Stored Procedures sp_OA...
I thought that I read that members of the System Administrators Role could
access the Extended Stored Procedures in the Master Databasewithout
specifically granting execute permissions on each procedure. When the sorce
code executes an error stating that "EXECUTE permission denied on
'sp_OACreate', database 'master', owner 'dbo'
I then granted the user the Execute permissions and had the same error
message
I then Granted the Execute Permissions to Public and it worked
I then removed the permissions from the user and it still worked.
So in the end just adding the Public made this work BUT to reduce my
security risks I would prefer not to give Public the rights to execute the
Extended Stored Procedures.
Can this be done? if so how?Yes, it's possible. It sounds like an issue somewhere with
the login - maybe it's executing under a different login
than the one being granted permissions. You can run a
profiler trace and include login, user info to check this.
-Sue
On Tue, 16 Aug 2005 09:31:09 -0700, "Jim Abel"
<JimAbel@.discussions.microsoft.com> wrote:

>DaI have an application that uses the Extended Stored Procedures sp_OA...
>I thought that I read that members of the System Administrators Role could
>access the Extended Stored Procedures in the Master Databasewithout
>specifically granting execute permissions on each procedure. When the sorc
e
>code executes an error stating that "EXECUTE permission denied on
>'sp_OACreate', database 'master', owner 'dbo'
>I then granted the user the Execute permissions and had the same error
>message
>I then Granted the Execute Permissions to Public and it worked
>I then removed the permissions from the user and it still worked.
>So in the end just adding the Public made this work BUT to reduce my
>security risks I would prefer not to give Public the rights to execute the
>Extended Stored Procedures.
>Can this be done? if so how?

Conflict procedures gone weird...

I am using SQLServer7 with SP4 merge replicating to PocketPC devices. Everything works fine EXCEPT conflict resolution...

After publishing I noticed that the conflict_tablename tables and sp_cft_ storedprocedures didn't get created. So I picked up the .cft files created in the ReplData/unc/... directory and ran them myself through Query Analyser.

Now the conflict resolution procedure are called when a conflict occurs BUT they return the following errors....

Message: The process could not log conflict information
Source: Merge Replication Provider

followed by...

Message: {[call sp_cft_59....](?,?,?,?,0x00)}
Source: ServerMachineName

followed by...

Message: Too many parameter arguments were supplied for procedure sp_cft_59...
Source: ServerMachineName

Ok, it seems the addition of the 0x00 in the call is incorrect so can someone please help me out with this. Please note that I'm only Enterprise Manager proficient in SQLServer.

Thank you,
jhAwww please!! Someone out there must know something about this. Or can anybody point me at another resource that might be useful?

Thnx