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:[email protected]]on Behalf Of Conny Martin Sent: Thursday, December 10, 2009 4:48 AM To: [email protected] 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:[email protected]] Im Auftrag von Joe D'Souza Gesendet: Donnerstag, 10. Dezember 2009 10:32 An: [email protected] 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:[email protected]]on Behalf Of Conny Martin Sent: Thursday, December 10, 2009 3:08 AM To: [email protected] 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 Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"

