Frank, great, it works. Thank you for your assistance. Kind Regards Conny
________________________________ Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von Frank Caruso Gesendet: Donnerstag, 10. Dezember 2009 11:32 An: arslist@ARSLIST.ORG Betreff: Re: Get result from stored procedure ** I have created Functions in MS SQL that return values. You call them like this: select fn_test 'param1','param2' Specificy a return field for $1$. For a Stored Procedure, to obtain the return result, which I am assuming will tell you whether or not the procedure succeeded or failed, put a select statement directly before the " return 1" statement. Select the value you would like returned. Then you call the proc from like this: exec sp_testproc 'param1' It might not be the correct way but it seems to work On Thu, Dec 10, 2009 at 1:14 PM, David Morgan <dave.mor...@tiberone.com> wrote: Hi Conny/Joe Not sure if it helps but MS SQL doesn't require any helper table so where in Oracle you might use:- Select sysdate from dual ; In MS-SQL you could use select getsysdate Perhaps you can do something similar with your SP? Select sp Failing that - you could use a sledgehammer to crack the nut and use a Java JDBC program to launch the SP and write out the result (ie use Java the Java program to "glue" the two together?) ARS <-- Java program --> JDBC <-- Database (Oracle/SQL SERVER/SYBASE/INFORMIX etc) Regards Dave Morgan -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe D'Souza Sent: 10 December 2009 11:05 To: arslist@ARSLIST.ORG Subject: Re: Get result from stored procedure There is a small difference on how you call stored procedures while using either the direct SQL or the set field SQL feature from the ARS at least as far as Oracle is concerned.. When using the Oracle client SQL*Plus, Execute <procedure name> works, but from the ARS interface for SQL, EXECUTE doesn't work. This I believe is because the ARS does not use the extended set of the database API that SQL*Plus uses. So in Oracle instead of using EXECUTE you use BEGIN. However no error is returned if you use EXECUTE. It just ignores it and doesn't do anything. I'm guessing your problem with MS-SQL is something similar.. Only I'm not sure if Begin would be the right keyword to execute a SP if the underlying DB is MS-SQL.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Conny Martin Sent: Thursday, December 10, 2009 4:48 AM To: arslist@ARSLIST.ORG Subject: AW: Get result from stored procedure Joe, I'm calling it the same way as in Query Analyzer. declare @p_result int exec SP_TEST 'MDP000000072713', @p_result output select @p_result No Errormessage is returned but $1$ doesn't get the value from p_result With oracle there is no problem. One can just do select sp_test('MDP000000072713') from dual but I don't know the correct syntax for MS-SQL. Kind Regards Conny -----Ursprüngliche Nachricht----- Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von Joe D'Souza Gesendet: Donnerstag, 10. Dezember 2009 10:32 An: arslist@ARSLIST.ORG Betreff: Re: Get result from stored procedure Conny, Yes it is possible.. how are you calling/executing the stored procedure when you are using it in the set-field-SQL? Begin <procedure name>; works from the ARS with Oracle as the backend DB.. (I'm not 100% certain if you need the semi colon in the end) I'm not sure if that would work with MS-SQL as well but you could try that.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of Conny Martin Sent: Thursday, December 10, 2009 3:08 AM To: arslist@ARSLIST.ORG Subject: Get result from stored procedure All, Not sure if this is possible, but is there a way to get the results from a stored procedure via set-field-SQL into ars. This works in Query-Analyzer declare @p_result int exec SP_TEST 'MDP000000072713', @p_result output select @p_result But in a set-fields-SQL $1$ is always null. ARS 7.1p6 MSSQL 2005 W2K3 Enterprise Server Any help is really appreciated. Thanks Conny _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org <http://www.arslist.org/> Platinum Sponsor:rmisoluti...@verizon.net <mailto:sponsor%3armisoluti...@verizon.net> ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org <http://www.arslist.org/> Platinum Sponsor:rmisoluti...@verizon.net <mailto:sponsor%3armisoluti...@verizon.net> ARSlist: "Where the Answers Are" _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
