| Groups | Blog | Home |
sql server reporting services : Stored procedures in Oracle
subject (http://support.microsoft.com/default.aspx?scid=kb;en-us;834305 and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp ). But I can't get it to work. I've created the following stored procedure in oracle: ------------------------ create or replace package body Stored_Procedures is Procedure ACNamen(p_acnamen Out refcur) Is Begin Open p_acnamen For Select * From activiteitencentra; End; end Stored_Procedures; ---------------------------- I've made an "oracle"' data source When I type ACNamen in the SQL-pannel or Stored_Procedures.ACNamen I get errors: "Invalid SQL statement" and "MinimumCapacity must be non negative" Would appreciate some help.
When working with Oracle stored procedures you must use the text-based
generic query designer (2 panes). It is not clear from your description if you are using the graphical or the text-based query designer (you can toggle between these two by clicking an icon in the toolbar). -- Robert This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Antoon" <Antoon@discussions.microsoft.com> wrote in message news:B29EC325-E789-4910-B407-0A3BD07D7C03@microsoft.com... > I've followed the instructions in Robert Bruckners earlier mail about > this > subject (http://support.microsoft.com/default.aspx?scid=kb;en-us;834305 > and > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp > ). But I can't get it to work. > > I've created the following stored procedure in oracle: > > ------------------------ > create or replace package body Stored_Procedures is > > Procedure ACNamen(p_acnamen Out refcur) Is > Begin > Open p_acnamen For Select * From activiteitencentra; > End; > end Stored_Procedures; > ---------------------------- > > I've made an "oracle"' data source > When I type ACNamen in the SQL-pannel or Stored_Procedures.ACNamen I get > errors: > "Invalid SQL statement" and "MinimumCapacity must be non negative" > > Would appreciate some help. >
Yes, I did. The problem was with the parameter.
I 've made some progress (I think), but still have problems: If I put Stored_Procedures.ACNamen(:a) in the pannel I get an empty result set. I defined a custom assembly: Imports System Public Class Class1 Public a As System.Data.OracleClient.OracleDataReader End Class And made a textbox with: =ClassLibrary1.Class1.a.getname(1) I get the error: The value expression for the textbox ‘textbox1’ contains an error: Object reference not set to an instance of an object. If we get it solved I promise I'll write it all out and put it on the web. [quoted text, click to view] "Robert Bruckner [MSFT]" wrote:
> When working with Oracle stored procedures you must use the text-based > generic query designer (2 panes). It is not clear from your description if > you are using the graphical or the text-based query designer (you can toggle > between these two by clicking an icon in the toolbar). > > -- Robert > This posting is provided "AS IS" with no warranties, and confers no rights. > > "Antoon" <Antoon@discussions.microsoft.com> wrote in message > news:B29EC325-E789-4910-B407-0A3BD07D7C03@microsoft.com... > > I've followed the instructions in Robert Bruckners earlier mail about > > this > > subject (http://support.microsoft.com/default.aspx?scid=kb;en-us;834305 > > and > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp > > ). But I can't get it to work. > > > > I've created the following stored procedure in oracle: > > > > ------------------------ > > create or replace package body Stored_Procedures is > > > > Procedure ACNamen(p_acnamen Out refcur) Is > > Begin > > Open p_acnamen For Select * From activiteitencentra; > > End; > > end Stored_Procedures; > > ---------------------------- > > > > I've made an "oracle"' data source > > When I type ACNamen in the SQL-pannel or Stored_Procedures.ACNamen I get > > errors: > > "Invalid SQL statement" and "MinimumCapacity must be non negative" > > > > Would appreciate some help. > > > >
Just specify the name of the stored procedure _without_ any arguments or
parenthesis in the text-based query designer. The report designer will then automatically detect arguments. -- This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Antoon" <Antoon@discussions.microsoft.com> wrote in message news:FBCBEAF7-460A-4BF4-B83E-84D501CA71CF@microsoft.com... > Yes, I did. The problem was with the parameter. > I 've made some progress (I think), but still have problems: > > If I put Stored_Procedures.ACNamen(:a) in the pannel I get an empty result > set. > > I defined a custom assembly: > > Imports System > > Public Class Class1 > > Public a As System.Data.OracleClient.OracleDataReader > > End Class > > And made a textbox with: > > =ClassLibrary1.Class1.a.getname(1) > > I get the error: > The value expression for the textbox 'textbox1' contains an error: Object > reference not set to an instance of an object. > > If we get it solved I promise I'll write it all out and put it on the web. > > "Robert Bruckner [MSFT]" wrote: > >> When working with Oracle stored procedures you must use the text-based >> generic query designer (2 panes). It is not clear from your description >> if >> you are using the graphical or the text-based query designer (you can >> toggle >> between these two by clicking an icon in the toolbar). >> >> -- Robert >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> >> "Antoon" <Antoon@discussions.microsoft.com> wrote in message >> news:B29EC325-E789-4910-B407-0A3BD07D7C03@microsoft.com... >> > I've followed the instructions in Robert Bruckners earlier mail about >> > this >> > subject (http://support.microsoft.com/default.aspx?scid=kb;en-us;834305 >> > and >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp >> > ). But I can't get it to work. >> > >> > I've created the following stored procedure in oracle: >> > >> > ------------------------ >> > create or replace package body Stored_Procedures is >> > >> > Procedure ACNamen(p_acnamen Out refcur) Is >> > Begin >> > Open p_acnamen For Select * From activiteitencentra; >> > End; >> > end Stored_Procedures; >> > ---------------------------- >> > >> > I've made an "oracle"' data source >> > When I type ACNamen in the SQL-pannel or Stored_Procedures.ACNamen I >> > get >> > errors: >> > "Invalid SQL statement" and "MinimumCapacity must be non negative" >> > >> > Would appreciate some help. >> > >> >> >>
Well, if I do that I get an oracle error "invalid sql-statement"
So I tried putting it in a block: begin Stored_Procedures.ACNamen; end; This gives me a result in the result-pane (yeah!) but when I refresh the fields I get a new error: "Could not generate a list of fiels for the query. Check the query syntax..." ....?... [quoted text, click to view] "Robert Bruckner [MSFT]" wrote:
> Just specify the name of the stored procedure _without_ any arguments or > parenthesis in the text-based query designer. The report designer will then > automatically detect arguments. > > -- > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "Antoon" <Antoon@discussions.microsoft.com> wrote in message > news:FBCBEAF7-460A-4BF4-B83E-84D501CA71CF@microsoft.com... > > Yes, I did. The problem was with the parameter. > > I 've made some progress (I think), but still have problems: > > > > If I put Stored_Procedures.ACNamen(:a) in the pannel I get an empty result > > set. > > > > I defined a custom assembly: > > > > Imports System > > > > Public Class Class1 > > > > Public a As System.Data.OracleClient.OracleDataReader > > > > End Class > > > > And made a textbox with: > > > > =ClassLibrary1.Class1.a.getname(1) > > > > I get the error: > > The value expression for the textbox 'textbox1' contains an error: Object > > reference not set to an instance of an object. > > > > If we get it solved I promise I'll write it all out and put it on the web. > > > > "Robert Bruckner [MSFT]" wrote: > > > >> When working with Oracle stored procedures you must use the text-based > >> generic query designer (2 panes). It is not clear from your description > >> if > >> you are using the graphical or the text-based query designer (you can > >> toggle > >> between these two by clicking an icon in the toolbar). > >> > >> -- Robert > >> This posting is provided "AS IS" with no warranties, and confers no > >> rights. > >> > >> "Antoon" <Antoon@discussions.microsoft.com> wrote in message > >> news:B29EC325-E789-4910-B407-0A3BD07D7C03@microsoft.com... > >> > I've followed the instructions in Robert Bruckners earlier mail about > >> > this > >> > subject (http://support.microsoft.com/default.aspx?scid=kb;en-us;834305 > >> > and > >> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp > >> > ). But I can't get it to work. > >> > > >> > I've created the following stored procedure in oracle: > >> > > >> > ------------------------ > >> > create or replace package body Stored_Procedures is > >> > > >> > Procedure ACNamen(p_acnamen Out refcur) Is > >> > Begin > >> > Open p_acnamen For Select * From activiteitencentra; > >> > End; > >> > end Stored_Procedures; > >> > ---------------------------- > >> > > >> > I've made an "oracle"' data source > >> > When I type ACNamen in the SQL-pannel or Stored_Procedures.ACNamen I > >> > get > >> > errors: > >> > "Invalid SQL statement" and "MinimumCapacity must be non negative" > >> > > >> > Would appreciate some help. > >> > > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |