is it possible to recieve result of SQL stored procedure to web page?

I have web server with .aspx page from wich I call stored procedure on MSSQL server. In this procedure are lots of "select" statements and I need to show results of this statements in web page. Can I call this procedure in that manner that procedure output is writen in some file and this file then ir recieved by web server and included in web page.

for (int i=0; i<dataset.Tables.Count; i++) {
 Response.Write(dataset.Tables[i].TableName);
 Response.Write("\n");
 // loop rows
 for (int j=0; j<dataset.Tables[i].Rows.Count; j++) {
 // loop columns
 for (int k=0; k<dataset.Tables[i].Columns.Count; k++) {
 Response.Write(dataset.Tables[i].Rows[j][k].ToString());
 if (k < dataset.Tables[i].Columns.Count - 1) {
 Response.Write(", ");
 }
 }
 Response.Write("\n");
 }
 Response.Write("\n");
}

this isn't exactly what you want, but idea.|||If the procedure returns a fixed number of result sets, you can return the results a datareader and then bind each result set to something like a datagrid or a repeater using .nextresult between bindings. A datareader can hold more than one result set. It can be pretty handy.

is it possible to pass array to stored procedure

Dear All,

I am using sql2000, I want to know whether in stored procedure we can pass

array. Or is there any other solution to pass array of records

Please Guide Me

thanks

Moving SQL Server forum.

Thanks.

|||

No... it is not posible to pass arryas to sp. BTW what is u r requirement , there are workaround for everything.. so pse specify u r requirement..

Madhu

is it possible to have variant condition clause in procedure?

i want to use OLEDB to build a COM for my app

in the case, i want to execute a select statement which the where-clause is variant.

ex,

select * from db1 where code='abc'

select * from db1 where name='mike'

As it's very difficult to change sql-command in oledb, i want to build a procedure like this,

create procedure viewDB
@.filter CHAR(20)

as

select * from db1 where @.filter

go

but failed!

i tried EXEC(select), but i cant get the variants when building a oledb consumer

No, you cannot pass the whole WHERE clause as a parameter. You could pass only values. In your case if this is predefined set of the types of conditions, then you could create additional parameter in your SP and pass type of the condition there. then, inside of SP, first check type of the query using IF statement and based on it call specific SQL

IF @.MyTYPE='A'

select * from db1 where code=@.filter

ELSE

select * from db1 where name=@.filter

|||

oh, yeah! why i didnt come up with this smart idea, haha

but i found a better solution, bind the columns manually!

|||What is that? Is it constracting SQL statement dinamically inside of SP?|||

no, it's not that!

is easy!

build a SP like this:

create procedure test
@.p char(40)
as
exec( 'select * from testdb where ' + @.p )
go

when executing this sp in sql-server, we can get the columns correctly! but, when using the ole-wizard to build a oledb consumer , the columns disappear. the fact is, the columns lay there steadily, it's ole-wizard didn't bind the columns for us, haha

so let's DIY

|||

But this is worst way to do. It is a pure SQL injection. If I pass next string in your parameter then it will be executed with the different result

Assuming I am passing next value in a parameter

1=1; SHUTDOWN --

Then it will execute your SELECT and then it will shutdown server completely. I could execute DELETE statement or something else. This is how hakers could get control of your server

|||

oh, thanks for telling me that!

but, what if i remove the string after the semi-colon?

my plan is to create a procedure, and use a ATL oledb consumer to access it, if i dont expose the SP name, i think the hackers wont hack me this way

|||They do not need to know your SP name. All the nee to do is to pass value like that to the parameter from your application and job is done. For example, if your screen accepts input for the parameter from outside then screen will accept this value and code will be executed. Another drawback of the dynamic SQL is that it is slower. It means your SP will be recompiled each time when you call it and new execution plan will be prepared.|||

really thanks this piece of infomation!

so, 'select * from table where col=@.p' is safe right?

ok, i will try to re-code my SP

|||

i fond it is almost impossible to code my SP like you suggestted, because my condition clause is so complicate.

i figured out this new plan, and i want to get some advice from you, thx

create procedure proc
@.p1 varchar(10),
@.p2 varchar(10),
...
@.pn varchar(10)

as

if @.p1 is not null
begin
select * into retTable from table where col1= @.p1
--select * into tmpTable from table where col1= @.p1
end
if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2=@.p1
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

if @.p3 is not null
begin
if object_id('tmpTable') is not null
drop table tmpTable
select * into tmpTable from retTable where col3=@.p3
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

...
...

go

BUT, i wonder if it's efficiency !

could you give me some suggestion or a better solution, thx

|||

I see that code for the second and third IF statements is the same. Does it mean that it suppose to be something like below? It is not an actual code that could work, but shows an idea. If idea is correct then you could pass array of values as one parameter into stored procedure using XML string and then use it inside of the IN clause. If this is what you need, then I will post a code that shows how to pass arrfay of values into SP and how to use it there

if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2 IN (@.p1, @.p3)
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

|||

well, it should be 'col3' in the 3rd IF statement

so, u mean my idea wont work actually right?

what do u mean pass a parameter using XML string? how can i parse it in the SP?

|||

Here is my article about how touse XML to pass array of values into SP.

http://support.microsoft.com/kb/555266/en-us

I will try to think about ideas how to do this in your case and will let you know

|||

now i have a problem about injection attack!

could you tell me if the following code safe.

procedure sp_a
@.p CHAR(40)
as
select * from tab1 where col1=@.p
go

|||Yes, it is safe if you do not do anything else inside if this SP. Just small suggestion. If you can, select just the fields you need and avoid using *. It will impove performance

Is It possible to have a stored procedure that returns 2 values?

Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.Do you mean two datatables in one dataset or what?|||output parameters?|||Hi, I mean 2 output values.
My Store Proc is a select query on 1 table in 1 database that I want to return 2 values from.
Thanks.|||If it is a single value, you may try ouput parameter.|||Let me repeat it again...
Hi, I mean (2 output values).
My Store Proc is a select query on 1 table in 1 database that I want to return 2 values from.
Does anyone have a exmple on how to do this..
I know how to do 1 output value but 2 I don't know how.
Thanks.|||the proc would look something like this and in your vb or c# code you would specify the parameters direction as Output or OutputInput then you can get the value out of the parameter value property I believe

CREATE PROCEDURE [OutputParamProc]
@.ItemID int,
@.OutputValue1 int output,
@.OutputValue2 int output
AS
SET NOCOUNT ON
Declare @.Rv int
SELECT @.OutputValue1 = Somecolumn1,
@.OutputValue2 = Somecolumn2
FROM SomeTable
WHERE SomeColumn = @.ItemID
SET @.Rv = @.@.Error
RETURN @.Rv

|||Thanks man that's what I needed.

Is it possible to get Multiple DataTables in a DataSet like VB.NET?

Hello

My stored procedure returns 4 recordsets. I require these recordsets as 4 different datatables in a dataset in Sql Server Reporting Services 2005, to use them on my rdl report.

I don't want to make the 4 diffrent datasets. Is it possible in SqlServer Reporting Services 2005? Please help me. Its urgent.

SSRS uses the first resultset returned from a stored procedure only.|||Thanx for the reply. But is there some way I can get all these recordsets. I have heard about Custom Data Extensions. Do these extensions help me in my problem. Or any other solution, other than data extension, if possible.|||

Well, I think nobody here is interested to answer this one. Do anyone suggest me any good forum for Reporting services queries.

Looking.

|||I won't suggest you write a custom data extension just for this. Instead, if possible, I will consider wrapping up the stored procedure inside another stored procedure which will return the requested dataset.sql

Is it possible to get Multiple DataTables in a DataSet like VB.NET?

Hello

My stored procedure returns 4 recordsets. I require these recordsets as 4 different datatables in a dataset in Sql Server Reporting Services 2005, to use them on my rdl report.

I don't want to make the 4 diffrent datasets. Is it possible in SqlServer Reporting Services 2005? Please help me. Its urgent.

SSRS uses the first resultset returned from a stored procedure only.|||Thanx for the reply. But is there some way I can get all these recordsets. I have heard about Custom Data Extensions. Do these extensions help me in my problem. Or any other solution, other than data extension, if possible.|||

Well, I think nobody here is interested to answer this one. Do anyone suggest me any good forum for Reporting services queries.

Looking.

|||I won't suggest you write a custom data extension just for this. Instead, if possible, I will consider wrapping up the stored procedure inside another stored procedure which will return the requested dataset.

is it possible to do this without cursors?

i have the following vb code that i want to turn into a stored procedure.
Can it be done without using cursors? thanks for any help!
what this code does is it says for each item, which other items reference it
in the column called source.
Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type], [ReferencedBy]
FROM [Catalog]")
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
objName = rst![Name]
objRefs = ""
Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
[Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE '*"
+ objName + "*';")
If Not findrst.EOF Then
findrst.MoveFirst
Do While Not findrst.EOF
objName = findrst![Name]
objType = findrst![Type]
objRefs = IIf(Len(objRefs) > 0, objRefs & ", " & objName
& " (" & objType & ")", objName & " (" & objType & ")")
findrst.MoveNext
Loop
End If
rst.Edit
rst![ReferencedBy] = objRefs
rst.Update
rst.MoveNext
Loop
End IfBen,
Please post the table DDL and sample data and desired results.
HTH
Jerry
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>i have the following vb code that i want to turn into a stored procedure.
> Can it be done without using cursors? thanks for any help!
> what this code does is it says for each item, which other items reference
> it
> in the column called source.
>
> Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type],
> [ReferencedBy]
> FROM [Catalog]")
> If Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> objName = rst![Name]
> objRefs = ""
> Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
> [Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE
> '*"
> + objName + "*';")
> If Not findrst.EOF Then
> findrst.MoveFirst
> Do While Not findrst.EOF
> objName = findrst![Name]
> objType = findrst![Type]
> objRefs = IIf(Len(objRefs) > 0, objRefs & ", " &
> objName
> & " (" & objType & ")", objName & " (" & objType & ")")
> findrst.MoveNext
> Loop
> End If
> rst.Edit
> rst![ReferencedBy] = objRefs
> rst.Update
> rst.MoveNext
> Loop
> End If|||create table catalog (name varchar(255), type varchar(50), source text,
referencedby text)
sample data before running the stored procedure
name type source referencedby
red hat mens red shirt
red shirt mens
after the stored procedure runs, i need the table to look like
name type source referencedby
red hat mens red shirt
red shirt mens red hat
the end result says that the red shirt is referenced in the source column by
the red hat.
thanks for any and all help!
"Jerry Spivey" wrote:

> Ben,
> Please post the table DDL and sample data and desired results.
> HTH
> Jerry
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>
>|||SELECT c.[name], c.[type], c.[source], r.[name] AS ReferencedBy
FROM [catalog] c
LEFT JOIN [catalog] r ON c.[name] = r.[source]
HTH,
John Scragg
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||A couple tips.
I assume you are not using the "text" data type for your source column. If
so, why? It is a FK column and should have the same data type as the related
column (in this case [name]). You can enforce referential integrity even
with self referenceing table relationships. I would suggest you do that.
Also, try not to use keywords for column or table names and try not to put
spaces in your column names.
Best of luck,
John
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||Thank you for the reply but unfortuanately that doesnt work (i dont think)
because the column source can have any number of items in it that it
references. i guess my sample wasnt clear enough, let me try again
the column name is a database object name
the column type is the type of database object (user table, stored procedure
)
the column source is the source code for the object (eg, the code/text of a
stored procedure)
the column referencedby contains a list of objects where the value in this
records name field can be found in all other records source column
i hope that is a little clearer. i know the vb code i posted earlier works
for this exact task, but i was hoping to have a stored procedure version as
well that didnt use cursors.
thanks for any help again.
ben

Is it possible to create thread & start from CLR Stored Proc

My simple CLR Stored procedure is as below:

[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyParallelStoredProc(string name1, string name2)
{
Thread t = null;
Worker wth = null;
int parallel = 2;
Object[] obj = new object [parallel];
SqlPipe p;
p = SqlContext.Pipe;

for (int i = 0; i < parallel; i++)
{
if (i == 0)
wth = new Worker(name1);
else
wth = new Worker(name2);
t = new Thread(new System.Threading.ThreadStart(wth.WorkerProc));
t.Name = "Thread -" + i.ToString() + ":";
t.Start();
p.Send(t.Name + ":Started");
obj[ i] = t;
}
for (int i = 0; i < parallel; i++)
{
t = (System.Threading.Thread)obj[ i];
t.Join();
p.Send(t.Name + ":Finished");
}
return 0;
}

The worker class implementing Thread Proc:

public class Worker
{
private string Name;

public Worker(string name)
{
SqlPipe p;
p = SqlContext.Pipe;
Name = name;
p.Send("In Constructor:" + Name);
}

public void WorkerProc()
{
SqlPipe p;
p = SqlContext.Pipe;
for (int i = 0; i < 10; i++)
p.Send(i.ToString()+":"+Name);
}
}

The assembly is registered with UNSAFE permission set.

CREATE ASSEMBLY
ThreadTest
FROM
'C:\\ThreadTest\bin\Debug\ThreadTest.dll'
WITH
permission_set = unsafe;
GO

CREATE PROC ParallelStoredProc
@.Name1 NVARCHAR(1024),
@.Name2 NVARCHAR(1024)
AS
EXTERNAL NAME ThreadTest.[MyTest.ThreadTest].MyParallelStoredProc

When I invoke the the stored procedure from T-SQL script as below,

EXEC ParallelStoredProc @.Name1, @.Name2

the thread class constructor gets called; but the 'WorkerProc' does not execute ?

Whether an UNSAFE assembly is allowed to spawn threads

inside SQL Server ?

Your code works correctly to start and run threads under unsafe. The reason you think it doesn't work is because the SqlContext connection is not available on new threads, so you can't use it to Pipe.Send information back.

If you try/catch for exceptions in your WorkerProc, you should see an error like the following:

"The requested operation requires a Sql Server execution thread. The current thread was started by user code or other non-Sql Server engine code."

Steven

|||

Thanks steve. Your input was very useful.

If I use SqlConnection in WorkerProc, the thread gets aborted

and goes into "Stopped" state.

It means the main CLR Stored proc can only execute the T-SQL commands ?

WorkerProc's are restricted to computations.

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

Is it possible to create a function from within a stored procedure?

I would like to create an UDF from withing a stored procedure. Is it possible?
Thanks a lot.Originally posted by EMoscosoCam
I would like to create an UDF from withing a stored procedure. Is it possible?

Thanks a lot.
try something like this:

declare @.S varchar(1000)

select @.S='
CREATE FUNCTION dbo.fn_test
(
@.p1 int,
@.P2 int
)
RETURNS int
AS
BEGIN
DECLARE @.sum AS int
SELECT @.sum = @.p1 + @.P2
RETURN @.sum
END'
exec(@.S)
go
select dbo.fn_test(5,2)|||Thank you very much! it worked just fine.

Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>

is it possible to create a cube fact which is base on the result of a stored procedure

I want to create a cube fact which is base on the result of a stored procedure, is it possible?

In software there are few things that are not possible :)

The most common schenario for building cubes if first to build a data warehouse where you collect data in several tables. Then you design your cube and load data from datawarehouse.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Is it possible to capture an OUT type parameter from a PL/SQL stored procedure?

When a stored PL/SQL procedure in my Oracle database is called from ASP.NET, is it possible to retrieve the OUT parameter from the PL/SQL procedure? For example, if I have a simple procedure as below to insert a row into the database. Ideally I would like it to return back the parameter namedNewId to my ASP.NET server. I'd like to capture this in the VB.NET code.

1createorreplace procedure WriteName(FirstNamein varchar2, LastNamein varchar2,NewIdout pls_integer)is
2
3NameId pls_integer;
4
5begin
6
7 select name_seq.nextvalinto NameIdfrom dual;
8
9insert into all_names(id, first_name, last_name)
10values(NameId, FirstName, LastName);
11
12NewId := NameId;
13
14end WriteName;

1<asp:SqlDataSource
2 ID="SqlDataSaveName"
3 runat="server"
4 ConnectionString="<%$ ConnectionStrings:ConnectionString%>"
5 ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName%>"
6 SelectCommand="WRITENAME"
7 SelectCommandType="StoredProcedure">
8 <SelectParameters>
9 <asp:ControlParameter ControlID="TextBoxFirstName" Name="FIRSTNAME" PropertyName="Text" Type="String" />
10 <asp:ControlParameter ControlID="TextBoxLastName" Name="LASTNAME" PropertyName="text" Type="String" />
11 </SelectParameters>
12</asp:SqlDataSource>

This is then called in the VB.NET code as below. It is in this section that I would like to capture the PL/SQL OUT parameterNewId returned from Oracle. 

1 SqlDataSaveName.Select(DataSourceSelectArguments.Empty)

If anybody can help me with the code I need to add to the VB.NET section to capture and then use the returned OUT parameter then I'd be very grateful.

This Select looks an awfully lot like an Insert, doesn't it? :)

My most honest suggestion is that you skip the SqlDataSource altogether for this kind of task. The SqlDataSource has some merit when it comes to binding databound controls to a datasource, but in this case it's a just simple insert from two textboxes.

In other words, use a simple OracleCommand, with a defined output parameter.

If you really want to use the SqlDataSource, then you should first use its Insert method, and corresponding InsertCommand etc. Next, define

<asp:Parameter Name="NewId" Direction="ReturnValue" />

and subscribe to the Inserted event of the SqlDataSource, where you'll be able to retrieve the value from the SqlDataSourceStatusEventArgs.

|||

Hi, thanks for replying. Yes, the example is overly simplified and an INSERT would have been better - I just used it to highlight what I was trying to achieve and that was to accept a returned value from the PL/SQL proc back in ASP.NET. However I think that you have answered my questions to thank you for showing me the Direction="ReturnValue" which is the bit I was missing.

Is it possible to call an Oracle stored procedure and pass parameters to them from reporti

I've set up a shared datasource to oracle with the option of prompting for
credientals. The report works with just static SQL in the Query designer. I
have been trying for a number of days now to use a call to an oracle stored
procedure or function as my report data set without success.
The function called sp_ListEmp has one parameter of type number. To call the
function I have tried adding {call sp_ListEmp(?)} into the Query designer
and setting the command type to Text. I have added the parameter ? and made
that equal to a Report parameter EMPNO of Integer type that I have added to
the report. So I have in the parameter tab of the Dataset
?=Parameters!EMPNO.Value. I recieve the following error message 'An error
occurred while executing the query. ORA-01036: illegal variable
name/number.'
For the stored procedure curspkg_join.open_join_cursor1 I have not even been
able to call the procedure with recieving an error for the second parameter
which is of type refcursor. Does anyone know if it is possible to call
Oracle stored procedures and pass parameters to them from reporting
services?
I have added the code for the stored procedure and function and also the
tables for which I have been working on below and also the commands I use in
SQL Plus to prove that the actual function and stored procedure work. This
is causing me much woe.
Create DEPT table and insert some rows
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);
INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');
Create EMP table and insert some rows
CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);
INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
Create package for Function example
create or replace package packperson
as
type cursorType is ref cursor;
end;
/
Create Function for Function example
create or replace function sp_ListEmp (n_EMPNO NUMBER) return
packperson.cursortype
as
l_cursor packperson.cursorType;
begin
open l_cursor for select ename as NAME, empno as NUM from emp where
empno = n_EMPNO order by ename;
return l_cursor;
end;
From SQL Plus I call this by
SQL> variable c refcursor
SQL> exec :c := sp_ListEmp(123)
SQL> print c
Create package for sp example
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor);
END curspkg_join;
/
Create package body for sp example
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
/
From SQL Plus I call this by
SQL> variable c refcursor
SQL>exec curspkg_join.open_join_cursor1(123,:c)
SQL>print :cThe cursor in the stored procedure has to be an OUT REF cursor rather than a
IN OUT cursor.
Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
data source dialog) and not OleDB.
You might also want to check this previous posting for further information
and a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> I've set up a shared datasource to oracle with the option of prompting for
> credientals. The report works with just static SQL in the Query designer.
I
> have been trying for a number of days now to use a call to an oracle
stored
> procedure or function as my report data set without success.
> The function called sp_ListEmp has one parameter of type number. To call
the
> function I have tried adding {call sp_ListEmp(?)} into the Query designer
> and setting the command type to Text. I have added the parameter ? and
made
> that equal to a Report parameter EMPNO of Integer type that I have added
to
> the report. So I have in the parameter tab of the Dataset
> ?=Parameters!EMPNO.Value. I recieve the following error message 'An error
> occurred while executing the query. ORA-01036: illegal variable
> name/number.'
> For the stored procedure curspkg_join.open_join_cursor1 I have not even
been
> able to call the procedure with recieving an error for the second
parameter
> which is of type refcursor. Does anyone know if it is possible to call
> Oracle stored procedures and pass parameters to them from reporting
> services?
> I have added the code for the stored procedure and function and also the
> tables for which I have been working on below and also the commands I use
in
> SQL Plus to prove that the actual function and stored procedure work. This
> is causing me much woe.
>
> Create DEPT table and insert some rows
> CREATE TABLE DEPT
> (DEPTNO NUMBER(2,0) NOT NULL,
> DNAME VARCHAR2(14) NULL,
> LOC VARCHAR2(13) NULL,
> PRIMARY KEY (DEPTNO)
> );
> INSERT INTO Dept VALUES(11,'Sales','Texas');
> INSERT INTO Dept VALUES(22,'Accounting','Washington');
> INSERT INTO Dept VALUES(33,'Finance','Maine');
> Create EMP table and insert some rows
> CREATE TABLE EMP
> (EMPNO NUMBER(4,0) NOT NULL,
> ENAME VARCHAR2(10) NULL,
> JOB VARCHAR2(9) NULL,
> MGR NUMBER(4,0) NULL,
> SAL NUMBER(7,2) NULL,
> COMM NUMBER(7,2) NULL,
> DEPTNO NUMBER(2,0) NULL,
> FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> PRIMARY KEY (EMPNO)
> );
> INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> Create package for Function example
> create or replace package packperson
> as
> type cursorType is ref cursor;
> end;
> /
> Create Function for Function example
> create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> packperson.cursortype
> as
> l_cursor packperson.cursorType;
> begin
> open l_cursor for select ename as NAME, empno as NUM from emp where
> empno = n_EMPNO order by ename;
> return l_cursor;
> end;
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL> exec :c := sp_ListEmp(123)
> SQL> print c
>
> Create package for sp example
> CREATE OR REPLACE PACKAGE curspkg_join AS
> TYPE t_cursor IS REF CURSOR ;
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor);
> END curspkg_join;
> /
> Create package body for sp example
> CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> t_cursor)
> IS
> v_cursor t_cursor;
> BEGIN
> IF n_EMPNO <> 0
> THEN
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO
> AND EMP.EMPNO = n_EMPNO;
> ELSE
> OPEN v_cursor FOR
> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> FROM EMP, DEPT
> WHERE EMP.DEPTNO = DEPT.DEPTNO;
> END IF;
> io_cursor := v_cursor;
> END open_join_cursor1;
> END curspkg_join;
> /
> From SQL Plus I call this by
> SQL> variable c refcursor
> SQL>exec curspkg_join.open_join_cursor1(123,:c)
> SQL>print :c
>|||Thank you for your help,
I'm still unsure of the syntax that I should use in Reporting Services when
calling the sp. Where/how do I reference the out ref cursor? Example I call
the procedure using -
{ call test_package.get_customers(?, ) }
I get the following error 'The data extension Oracle does not support
unnamed parameters. Use named parameters instead.'
So my question is how do I reference the out ref cursor in Reporting
services?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> The cursor in the stored procedure has to be an OUT REF cursor rather than
a
> IN OUT cursor.
> Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> data source dialog) and not OleDB.
> You might also want to check this previous posting for further information
> and a sample:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > I've set up a shared datasource to oracle with the option of prompting
for
> > credientals. The report works with just static SQL in the Query
designer.
> I
> > have been trying for a number of days now to use a call to an oracle
> stored
> > procedure or function as my report data set without success.
> > The function called sp_ListEmp has one parameter of type number. To call
> the
> > function I have tried adding {call sp_ListEmp(?)} into the Query
designer
> > and setting the command type to Text. I have added the parameter ? and
> made
> > that equal to a Report parameter EMPNO of Integer type that I have added
> to
> > the report. So I have in the parameter tab of the Dataset
> > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
error
> > occurred while executing the query. ORA-01036: illegal variable
> > name/number.'
> >
> > For the stored procedure curspkg_join.open_join_cursor1 I have not even
> been
> > able to call the procedure with recieving an error for the second
> parameter
> > which is of type refcursor. Does anyone know if it is possible to call
> > Oracle stored procedures and pass parameters to them from reporting
> > services?
> >
> > I have added the code for the stored procedure and function and also the
> > tables for which I have been working on below and also the commands I
use
> in
> > SQL Plus to prove that the actual function and stored procedure work.
This
> > is causing me much woe.
> >
> >
> > Create DEPT table and insert some rows
> >
> > CREATE TABLE DEPT
> > (DEPTNO NUMBER(2,0) NOT NULL,
> > DNAME VARCHAR2(14) NULL,
> > LOC VARCHAR2(13) NULL,
> > PRIMARY KEY (DEPTNO)
> > );
> >
> > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > INSERT INTO Dept VALUES(33,'Finance','Maine');
> >
> > Create EMP table and insert some rows
> >
> > CREATE TABLE EMP
> > (EMPNO NUMBER(4,0) NOT NULL,
> > ENAME VARCHAR2(10) NULL,
> > JOB VARCHAR2(9) NULL,
> > MGR NUMBER(4,0) NULL,
> > SAL NUMBER(7,2) NULL,
> > COMM NUMBER(7,2) NULL,
> > DEPTNO NUMBER(2,0) NULL,
> > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > PRIMARY KEY (EMPNO)
> > );
> >
> > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> >
> > Create package for Function example
> >
> > create or replace package packperson
> > as
> > type cursorType is ref cursor;
> > end;
> > /
> >
> > Create Function for Function example
> >
> > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > packperson.cursortype
> > as
> > l_cursor packperson.cursorType;
> > begin
> > open l_cursor for select ename as NAME, empno as NUM from emp where
> > empno = n_EMPNO order by ename;
> >
> > return l_cursor;
> > end;
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL> exec :c := sp_ListEmp(123)
> > SQL> print c
> >
> >
> >
> > Create package for sp example
> >
> > CREATE OR REPLACE PACKAGE curspkg_join AS
> > TYPE t_cursor IS REF CURSOR ;
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor);
> > END curspkg_join;
> > /
> >
> > Create package body for sp example
> >
> > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > t_cursor)
> > IS
> > v_cursor t_cursor;
> > BEGIN
> > IF n_EMPNO <> 0
> > THEN
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > AND EMP.EMPNO = n_EMPNO;
> >
> > ELSE
> > OPEN v_cursor FOR
> > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > FROM EMP, DEPT
> > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> >
> > END IF;
> > io_cursor := v_cursor;
> > END open_join_cursor1;
> > END curspkg_join;
> > /
> >
> > From SQL Plus I call this by
> >
> > SQL> variable c refcursor
> > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > SQL>print :c
> >
> >
>|||Make sure you use the generic text-based query designer with 2 panes, rather
than the visual query designer with 4 panes.
Regarding the OUT cursor - don't reference it, just omit that parameter.
A better practice for stored procedures is to set the command type of the
query to StoredProcedure instead of Text. In that case, the query text is
just the name of the stored procedure "test_package.get_customers". The
parameters will automatically be determined by report designer on clicking
on the refresh fields icon.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe" <booksnore2@.netscape.net> wrote in message
news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> Thank you for your help,
> I'm still unsure of the syntax that I should use in Reporting Services
when
> calling the sp. Where/how do I reference the out ref cursor? Example I
call
> the procedure using -
> { call test_package.get_customers(?, ) }
> I get the following error 'The data extension Oracle does not support
> unnamed parameters. Use named parameters instead.'
> So my question is how do I reference the out ref cursor in Reporting
> services?
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > The cursor in the stored procedure has to be an OUT REF cursor rather
than
> a
> > IN OUT cursor.
> > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > data source dialog) and not OleDB.
> >
> > You might also want to check this previous posting for further
information
> > and a sample:
> >
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Joe" <booksnore2@.netscape.net> wrote in message
> > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > I've set up a shared datasource to oracle with the option of prompting
> for
> > > credientals. The report works with just static SQL in the Query
> designer.
> > I
> > > have been trying for a number of days now to use a call to an oracle
> > stored
> > > procedure or function as my report data set without success.
> > > The function called sp_ListEmp has one parameter of type number. To
call
> > the
> > > function I have tried adding {call sp_ListEmp(?)} into the Query
> designer
> > > and setting the command type to Text. I have added the parameter ? and
> > made
> > > that equal to a Report parameter EMPNO of Integer type that I have
added
> > to
> > > the report. So I have in the parameter tab of the Dataset
> > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> error
> > > occurred while executing the query. ORA-01036: illegal variable
> > > name/number.'
> > >
> > > For the stored procedure curspkg_join.open_join_cursor1 I have not
even
> > been
> > > able to call the procedure with recieving an error for the second
> > parameter
> > > which is of type refcursor. Does anyone know if it is possible to call
> > > Oracle stored procedures and pass parameters to them from reporting
> > > services?
> > >
> > > I have added the code for the stored procedure and function and also
the
> > > tables for which I have been working on below and also the commands I
> use
> > in
> > > SQL Plus to prove that the actual function and stored procedure work.
> This
> > > is causing me much woe.
> > >
> > >
> > > Create DEPT table and insert some rows
> > >
> > > CREATE TABLE DEPT
> > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > DNAME VARCHAR2(14) NULL,
> > > LOC VARCHAR2(13) NULL,
> > > PRIMARY KEY (DEPTNO)
> > > );
> > >
> > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > >
> > > Create EMP table and insert some rows
> > >
> > > CREATE TABLE EMP
> > > (EMPNO NUMBER(4,0) NOT NULL,
> > > ENAME VARCHAR2(10) NULL,
> > > JOB VARCHAR2(9) NULL,
> > > MGR NUMBER(4,0) NULL,
> > > SAL NUMBER(7,2) NULL,
> > > COMM NUMBER(7,2) NULL,
> > > DEPTNO NUMBER(2,0) NULL,
> > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > PRIMARY KEY (EMPNO)
> > > );
> > >
> > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > >
> > > Create package for Function example
> > >
> > > create or replace package packperson
> > > as
> > > type cursorType is ref cursor;
> > > end;
> > > /
> > >
> > > Create Function for Function example
> > >
> > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > packperson.cursortype
> > > as
> > > l_cursor packperson.cursorType;
> > > begin
> > > open l_cursor for select ename as NAME, empno as NUM from emp
where
> > > empno = n_EMPNO order by ename;
> > >
> > > return l_cursor;
> > > end;
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL> exec :c := sp_ListEmp(123)
> > > SQL> print c
> > >
> > >
> > >
> > > Create package for sp example
> > >
> > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > TYPE t_cursor IS REF CURSOR ;
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor);
> > > END curspkg_join;
> > > /
> > >
> > > Create package body for sp example
> > >
> > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > t_cursor)
> > > IS
> > > v_cursor t_cursor;
> > > BEGIN
> > > IF n_EMPNO <> 0
> > > THEN
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > AND EMP.EMPNO = n_EMPNO;
> > >
> > > ELSE
> > > OPEN v_cursor FOR
> > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > FROM EMP, DEPT
> > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > >
> > > END IF;
> > > io_cursor := v_cursor;
> > > END open_join_cursor1;
> > > END curspkg_join;
> > > /
> > >
> > > From SQL Plus I call this by
> > >
> > > SQL> variable c refcursor
> > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > SQL>print :c
> > >
> > >
> >
> >
>|||Thanks - got it working now, your help is much appreciated.
"Robert Bruckner [MSFT]" wrote:
> Make sure you use the generic text-based query designer with 2 panes, rather
> than the visual query designer with 4 panes.
> Regarding the OUT cursor - don't reference it, just omit that parameter.
> A better practice for stored procedures is to set the command type of the
> query to StoredProcedure instead of Text. In that case, the query text is
> just the name of the stored procedure "test_package.get_customers". The
> parameters will automatically be determined by report designer on clicking
> on the refresh fields icon.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Joe" <booksnore2@.netscape.net> wrote in message
> news:uZsHcq%23iEHA.384@.TK2MSFTNGP10.phx.gbl...
> > Thank you for your help,
> > I'm still unsure of the syntax that I should use in Reporting Services
> when
> > calling the sp. Where/how do I reference the out ref cursor? Example I
> call
> > the procedure using -
> >
> > { call test_package.get_customers(?, ) }
> >
> > I get the following error 'The data extension Oracle does not support
> > unnamed parameters. Use named parameters instead.'
> > So my question is how do I reference the out ref cursor in Reporting
> > services?
> >
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:e1$dlvyiEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > The cursor in the stored procedure has to be an OUT REF cursor rather
> than
> > a
> > > IN OUT cursor.
> > > Also make sure you use the managed Oracle provider (i.e. "Oracle" in the
> > > data source dialog) and not OleDB.
> > >
> > > You might also want to check this previous posting for further
> information
> > > and a sample:
> > >
> >
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=049fe955-cec8-4a79-a5e5-a9c02873e19d&sloc=en-us
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Joe" <booksnore2@.netscape.net> wrote in message
> > > news:%23dZ118wiEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > > > I've set up a shared datasource to oracle with the option of prompting
> > for
> > > > credientals. The report works with just static SQL in the Query
> > designer.
> > > I
> > > > have been trying for a number of days now to use a call to an oracle
> > > stored
> > > > procedure or function as my report data set without success.
> > > > The function called sp_ListEmp has one parameter of type number. To
> call
> > > the
> > > > function I have tried adding {call sp_ListEmp(?)} into the Query
> > designer
> > > > and setting the command type to Text. I have added the parameter ? and
> > > made
> > > > that equal to a Report parameter EMPNO of Integer type that I have
> added
> > > to
> > > > the report. So I have in the parameter tab of the Dataset
> > > > ?=Parameters!EMPNO.Value. I recieve the following error message 'An
> > error
> > > > occurred while executing the query. ORA-01036: illegal variable
> > > > name/number.'
> > > >
> > > > For the stored procedure curspkg_join.open_join_cursor1 I have not
> even
> > > been
> > > > able to call the procedure with recieving an error for the second
> > > parameter
> > > > which is of type refcursor. Does anyone know if it is possible to call
> > > > Oracle stored procedures and pass parameters to them from reporting
> > > > services?
> > > >
> > > > I have added the code for the stored procedure and function and also
> the
> > > > tables for which I have been working on below and also the commands I
> > use
> > > in
> > > > SQL Plus to prove that the actual function and stored procedure work.
> > This
> > > > is causing me much woe.
> > > >
> > > >
> > > > Create DEPT table and insert some rows
> > > >
> > > > CREATE TABLE DEPT
> > > > (DEPTNO NUMBER(2,0) NOT NULL,
> > > > DNAME VARCHAR2(14) NULL,
> > > > LOC VARCHAR2(13) NULL,
> > > > PRIMARY KEY (DEPTNO)
> > > > );
> > > >
> > > > INSERT INTO Dept VALUES(11,'Sales','Texas');
> > > > INSERT INTO Dept VALUES(22,'Accounting','Washington');
> > > > INSERT INTO Dept VALUES(33,'Finance','Maine');
> > > >
> > > > Create EMP table and insert some rows
> > > >
> > > > CREATE TABLE EMP
> > > > (EMPNO NUMBER(4,0) NOT NULL,
> > > > ENAME VARCHAR2(10) NULL,
> > > > JOB VARCHAR2(9) NULL,
> > > > MGR NUMBER(4,0) NULL,
> > > > SAL NUMBER(7,2) NULL,
> > > > COMM NUMBER(7,2) NULL,
> > > > DEPTNO NUMBER(2,0) NULL,
> > > > FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
> > > > PRIMARY KEY (EMPNO)
> > > > );
> > > >
> > > > INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
> > > > INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
> > > > INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);
> > > >
> > > > Create package for Function example
> > > >
> > > > create or replace package packperson
> > > > as
> > > > type cursorType is ref cursor;
> > > > end;
> > > > /
> > > >
> > > > Create Function for Function example
> > > >
> > > > create or replace function sp_ListEmp (n_EMPNO NUMBER) return
> > > > packperson.cursortype
> > > > as
> > > > l_cursor packperson.cursorType;
> > > > begin
> > > > open l_cursor for select ename as NAME, empno as NUM from emp
> where
> > > > empno = n_EMPNO order by ename;
> > > >
> > > > return l_cursor;
> > > > end;
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL> exec :c := sp_ListEmp(123)
> > > > SQL> print c
> > > >
> > > >
> > > >
> > > > Create package for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE curspkg_join AS
> > > > TYPE t_cursor IS REF CURSOR ;
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor);
> > > > END curspkg_join;
> > > > /
> > > >
> > > > Create package body for sp example
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY curspkg_join AS
> > > > Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT
> > > > t_cursor)
> > > > IS
> > > > v_cursor t_cursor;
> > > > BEGIN
> > > > IF n_EMPNO <> 0
> > > > THEN
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO
> > > > AND EMP.EMPNO = n_EMPNO;
> > > >
> > > > ELSE
> > > > OPEN v_cursor FOR
> > > > SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
> > > > FROM EMP, DEPT
> > > > WHERE EMP.DEPTNO = DEPT.DEPTNO;
> > > >
> > > > END IF;
> > > > io_cursor := v_cursor;
> > > > END open_join_cursor1;
> > > > END curspkg_join;
> > > > /
> > > >
> > > > From SQL Plus I call this by
> > > >
> > > > SQL> variable c refcursor
> > > > SQL>exec curspkg_join.open_join_cursor1(123,:c)
> > > > SQL>print :c
> > > >
> > > >
> > >
> > >
> >
> >
>
>

is it possible to call a stored procedure without a cursor?

is it possible to call a stored procedure without a cursor?Absolutely.|||hahaha, ok, well... If it is possible, would you please tel me how, or the name how to do it, or even a link to a site that shows me how. I've searched high and low and can't seem to find out how, help would be appreciated, thanks|||If the procedure is the first command in your code, you can simply call it by naming it:

MyCustomProcedure
select 'Procedure completed.'

Otherwise, you need to use the EXEC command:

select 'Getting ready to call the procedure...'
exec MyCustomProcedure
select 'Procedure completed.'

blindman

Is it possible to call a DTS package in a stored procedure

Hi,

I wanted to know if i can a DTS package using a stored procedure and if yes how should i do it.

Regards,

Karen

You should be able to use XP_cmdshell or DTS run for this, Look at this link

http://www.databasejournal.com/features/mssql/article.php/1459181

Thanks

Raj

Is it possible to access the @@RowCount from a Sproc in a report

Hi ,

I am trying to access the @.@.RowCount that is been returned by my stored procedure in a report. Can you please tell me how can i access it, This is my Stored procedure

USE [ICCStatements]GO/****** Object: StoredProcedure [dbo].[rpt_SelectInvestments] Script Date: 08/03/2007 11:54:47 ******/SET ANSI_NULLSOFFGOSET QUOTED_IDENTIFIEROFFGOALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanIdAS integer)ASBEGIN-- History-- 08/17/2004svanpatter/JSWCOinitial version created-- 08/30/2004svanpatter/JSWCOadd-- Select available fundsDeclare @.CountintSELECT [ClientPlan].PlanId, [ClientPlan].PlanName,-- Fund.[FundName] AS InvestmentName,CASEWHEN PlanFund.PlanFundDisplayNameISNULLTHEN Fund.ShortNameELSE PlanFund.PlanFundDisplayNameEND InvestmentName,'F'AS InvestmentType,--PlanFund.PlanId As InvestmentID PlanFund.IsPortfolioFundOnly, PlanFund.FundDisplayOrderAs InvestmentIDFROM [ClientPlan]--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdINNERJOIN PlanFundON [ClientPlan].PlanId = PlanFund.PlanIdAnd IsPortfolioFundOnly = "0"INNERJOIN FundON PlanFund.FundId = Fund.FundId--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperIDWHERE[ClientPlan].PlanId = @.PlanIdUNION-- Select PortfoliosSELECT [ClientPlan].PlanId, [ClientPlan].PlanName, PlanPortfolio.PortfolioNameAS InvestmentName,'P'AS InvestmentType,NULL,PlanPortfolio.PortfolioIdAs InvestmentIDFROM [ClientPlan]INNERJOIN PlanPortfolioON [ClientPlan].PlanId = PlanPortfolio.PlanId--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdWHERE[ClientPlan].PlanId = @.PlanIdORDER BYInvestmentType, InvestmentIDSet @.Count =@.@.RowCountReturn @.CountEND

Can some one pls tell me how can i access @.Count from the report.

Any help will be appreciated..

Regards,

Karen

If you meant Crystal Reports then I don't understand why you need this as in CR you already have such function that returns number of rows returned by SP.

|||

I meant in SQl server Reporting Services.

|||

=Count(Field!somefieldname.Value)

|||

lavanya,

Is it possible to access the no. of rows returned by the stored procedure....

Regards

Karen

|||

You shouldn't need to return the RowCount from the stored procedure. In SQL Reporting Services there is a CountRows() function that can be used to return the number of rows in the datasource. To use it you would call it like so: =CountRows("DatasourceName"). The "DatasourceName" obviously being the name of the datasource that calls your stored procedure.

Also, the = sign is only needed if it is the only item, or first item, in a text field in the report.

Is it possible pass to procedure open cursor from other procedure ??

Is it possible pass to procedure open cursor from other procedure ?
Any suggestions will be appreciated
Message posted via http://www.webservertalk.comYes, you can declare a cursor as an output a parameter of a stored
procedure. But it is most likely not the most efficient way to share data
between procedures. The alternatives are discussed by SQL Server MVP Erland
Sommarskog in the following article:
http://www.sommarskog.se/share_data.html
Jacco Schalkwijk
SQL Server MVP
"JB via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:bd838d17bc034f26a19b475812a8eeb7@.SQ
webservertalk.com...
> Is it possible pass to procedure open cursor from other procedure ?
> Any suggestions will be appreciated
> --
> Message posted via http://www.webservertalk.com|||Yes, but it's probably not a good idea. Could you explain your
requirement. There's sure to be a better way.
David Portas
SQL Server MVP
--|||In my db i call to some procedure suppese MyProc from application and pass
some XML,
inside the MyProc i parse the xml, insert the data to temporary table and
now according to some information that i got from xml call to other
procedure suppose proc1 or
proc2 or ... and in one of these procedures i perform some operation on
data that exist in temporary table. Actually i solve this problem with
temporary table (#myTable) however it's not good idea to use it and i can't
to use it, i don't know what to do.
The problem is that according to data in XML i call to appropriate
procedure, i can parse the xml in Myproc sp and call to relevant nested
procedure
proc1 or proc2, ... and pass this XML again however i don't want to open
and
parse the XML twice
Message posted via http://www.webservertalk.com|||This doesn't make a lot of sense to me as a design for a process in SQL
Server. You should parse your XML once only in order to load it into
appropriate tables. TSQL provides a proc to do this:
sp_xml_removedocument. Then execute procs from the data in tables. That
way you should avoid lots of messy cursors, temp tables and procedural
code. XML is for data-interchange only - it's a lousy way to persist
data and move it around inside the database.
David Portas
SQL Server MVP
--|||CORRECTION: sp_xml_preparedocument is the name of the proc you want.
David Portas
SQL Server MVP
--|||my problem is that after i parse the xml i fill temporary table with it's
data and now i need to call to other procedure to which i want pass
temporary table, but it isn't good idea also pass an open cursor is not
good idea
Message posted via http://www.webservertalk.com|||So why create two separate SPs and why load the data into a temporary
table or a cursor? You seem to be looking for a solution to a problem
that wouldn't exist if you made a better design.
David Portas
SQL Server MVP
--|||because according to the data that i got from XML i call to appropriate
procedure
Message posted via http://www.webservertalk.com|||OK. So load the XML data into tables where it belongs. Then execute the
logic for BOTH procedures but add or modify the WHERE clauses in your
DML code such that the logic only executes as appropriate. Example
pseudo-code:
Instead of this:
IF x=1
EXEC usp_proc1
IF x=2
EXEC usp_proc2
Do this:
..
WHERE x = 1
..
WHERE x = 2
In other words, adopt the declarative, set-based approach rather than a
procedural approach.
David Portas
SQL Server MVP
--

Is it possible pass to procedure open cursor from other procedure

JB, you need to take a break and get an education. Your postings
consistently show that you missed the whole idea of a declarative,
set-oriented language. I put in a chapter on "Thinking in Sets" in my
new book because of this problem. It gets a little Zen, but it is
important. See the forest and not the trees. Use the right words so
your mind will have the right concepts (rows are not records, etc.).
Example: I have a bag of pennies and I want to know the value of the
bag
Procedural solution: count the pennies one at a time (i.e. cursor).
This sucks when you have $1 million dollars
SQL solution: Weigh the bag as a whole unit in itself, divide by the
weight of a single penny. Scales to large amoutns of data quickly.
It takes about a year or more of programming in SQL to make the shift.
Do not XXXXX aobut that; it takes six years to beocme a Union
Journeyman Carpenter in New York State and their incompetence cannot
kill near or miam as many people as a bad database.
Right now, you are writing code that is 1,2 or 3 orders of magnitude
slower than it should be and you probably have little or no data
integrity in your schemas. In short, you are very dangerous right now.Thanks, where can i got your book ?
Message posted via http://www.webservertalk.com|||u r very helpfull
Message posted via http://www.webservertalk.com