DEVELOPERS FORUM - Uregent Help on ARAS SQLs Item Type : executing stored procedure

divya_p02 - Monday, June 6, 2011 7:14 AM:

Hi Folks,

I have written a stored procedure in SQL's Item type, but when i click "Execute SQL" from the Action Menu, i getting the below error

<SOAP-ENV:Envelope xmlns:SOAP-ENV="schemas.xmlsoap.org/.../">
	<SOAP-ENV:Body>
		<SOAP-ENV:Fault xmlns:af="www.aras.com/InnovatorFault">
			<faultcode>999</faultcode>
			<faultstring>must have id or name of sql item in request</faultstring>
			<detail>
				<af:legacy_detail>must have id or name of sql item in request</af:legacy_detail>
				<af:legacy_faultstring>must have id or name of sql item in request</af:legacy_faultstring>
			</detail>
		</SOAP-ENV:Fault>
	</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Stored Procedure written  is having only select statement something like ("Select * from [innovator].[ACTIVITY]) but not able to execute the stored procedure from ARAS but i am able to run the same procedure from SQL server directly with out any errors.

Kindly reply if any solution for this.

Regards,

Divya

Brian - Monday, June 6, 2011 8:46 AM:

Hi Divya,

It would help if you posted all of the text of the SQL stored procedure as it is saved in the Aras SQL object.

Also for a simple SQL have a look at "get_activity2" and see if there is anything different about your procedure to this one.

Cheers,

Brian.

divya_p02 - Wednesday, June 8, 2011 12:01 AM:

Hi Brain,

Thanks for your reply.  I have verified the stored procedure. Please find te script of the stored procedure and method in which stored procedure is being called. I am calling server side(C#) method on button click event. When button click event happens, i am getting the following error

---------------------------------------------------------------------------------------------------------------------

Error : Event handler failed with the message : Object Expected

Stack Trace : Client Side Error

 ---------------------------------------------------------------------------------------------------------------------

Stored Procedure Written

CREATE PROCEDURE innovator.lean_sp_GetActivityDetails (@id varchar(32))

AS
BEGIN

select process_name as process_name from innovator.CHECK_PROCESS
WHERE CHECK_PROCESS_ID= @id

END

------------------------------------------------------------------------------------------------------------------------------------

Method(server Side - C#) where stored procedure is being called

 Item proc = this.newItem("SQL", "SQL PROCESS");
proc.setProperty("name", "lean_sp_GetActivityDetails");
proc.setProperty("PROCESS", "CALL");
proc.setProperty("ARG1", "ABC_153");
Item result = proc.apply();
Item itm=this.newItem("CHECK_PROCESS","get");
string newresult1 = result.getProperty("process_name");
string sql =@"update innovator.CHECK_PROCESS SET result='{0}' where CHECK_PROCESS_ID='ABC_157'";
sql = String.Format(sql, newresult1);
Item result1 = this.getInnovator().applySQL(sql);
return result1;

Kindly help me in this regard. Any suggestions or modification needs to be done in the stored procedure or method.  Is there a  way i can debug the server side method??. Let me know if there is a way

NOTE: i am able to execute the method when i run the method using (RUN SERVER METHOD) in actions menu of Methods and its giving me proper results
 

divya_p02 - Wednesday, June 8, 2011 1:57 AM:

Hi Brain,

Thanks a lot for your quick reply and this really helped us to modify our approach.

Regards,

Divya

Brian - Wednesday, June 8, 2011 12:33 AM:

Hi Divya,

The problem is that you are trying to run a server side method from a client side event.

The button click happens on a form on the client side. The method attached to this should be a javascript method not a c# method.

Your method will convert easily to Javascript from c# or you can call the c# method from the Javascript by treating it as a Generic Function.

Have a look in the Programmers Guide for information about generic functions.

Note that you will have to change "this" to "document.thisItem" when you convert the method to javascript. See the Programmers guide page 29 for information on the context item.

If you have a full copy of Visual Studio you can set it up to debug server side methods. Turn on "Just in time" debugging and put "System.Diagnostics.Debugger.Break();" into your server side code to trigger the debugger.

Cheers,

Brian.