Stored Procedure in SQL Server 2005

[Former Member]

Hi Friends,

when i execute the stored procedure , i can get below error.

************************

Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KANLOGDATA_KLOGDATA' (structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

*************************

My XML Message below format:

<Statment>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0001</EMPNO>

---<EMPNAME isInput= "true type="char">Sateesh</EMPNAME>

---<ADDRESS isInput="true" type="char">Delhi</ADDRESS>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0002</EMPNO>

---<EMPNAME isInput= "true type="char">Sateesh1</EMPNAME>

---<ADDRESS isInput="true" type="char">Bangalore</ADDRESS>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0003</EMPNO>

---<EMPNAME isInput= "true type="char">Sateesh3</EMPNAME>

---<ADDRESS isInput="true" type="char">Hyderabad</ADDRESS>

</Statement>

[former_member187339 · Accepted solution]

Hi Sateesh,

The structure should be

<Statment>
--<storedProcedureName action="EXECUTE">
---<table>storedprocedurename</table>
---<EMPNO isInput="true" type="char">0001</EMPNO>
---<EMPNAME isInput= "true type="char">Sateesh</EMPNAME>
---<ADDRESS isInput="true" type="char">Delhi</ADDRESS>
</storedProcedureName > 
--<storedProcedureName action="EXECUTE">
---<table>storedprocedurename</table>
---<EMPNO isInput="true" type="char">0002</EMPNO>
---<EMPNAME isInput= "true type="char">Sateesh1</EMPNAME>
---<ADDRESS isInput="true" type="char">Bangalore</ADDRESS>
</storedProcedureName > 
--<storedProcedureName action="EXECUTE">
---<table>storedprocedurename</table>
---<EMPNO isInput="true" type="char">0003</EMPNO>
---<EMPNAME isInput= "true type="char">Sateesh3</EMPNAME>
---<ADDRESS isInput="true" type="char">Hyderabad</ADDRESS>
</storedProcedureName > 
</Statement>

Regards

Suraj

[Former Member · Accepted solution]

Hi,

Sorry for inconvience,

I make changes as per as your suggestion,Till i am getting this error.

"Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_DATA(structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)"

I didn't get any idea how i will approach,

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_DB_SELECT_Response xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">

- <Statement>

- <XI_SP_DATA action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">901064</EMPNO>

<EMPNAME isInput="true" type="CHAR">Sateesh</EMPNAME>

<ADDRESS isInput="true" type="CHAR">Hyderabad</ADDRESS>

</XI_SP_DATA>

- <XI_SP_DATA action="EXECUTE">

<table>XI_SP_DATA </table>

<EMPNO isInput="true" type="CHAR ">901000</EMPNO>

<EMPNAME isInput="true" type="CHAR">Ashish</EMPNAME>

<ADDRESS isInput="true" type="CHAR">Delhi</ADDRESS>

</XI_SP_DATA>

</Statement>

</ns0:MT_DB_SELECT_Response>

is their any wrong from Stored procedure side.

Kindly suggest me,

thank you

sateesh.

[former_member200962 · Accepted solution]

MT_DB_SELECT_Response

can you tell me what are you actually trying to achieve?

I have _response attached to my DT name when there is a SYNC JDBC receiver.....and the above format indicates the response message structure (response MT)

Just try to have the structure as suggested by Suraj or as it is mentioned in the blog.....and also confirm that your SP actually has the names/ data types that you are sending!

[former_member187339 · Accepted solution]

Hi Sateesh,

Try like this:

<?xml version="1.0" encoding="UTF-8" ?>
- <ns0:MT_DB_SELECT_Response xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">
- <Statement>
- <XI_SP_DATA action="EXECUTE">
<table>XI_SP_DATA</table>
<EMPNO isInput="true" type="CHAR">901064</EMPNO>
<EMPNAME isInput="true" type="CHAR">Sateesh</EMPNAME>
<ADDRESS isInput="true" type="CHAR">Hyderabad</ADDRESS>
</XI_SP_DATA>
- </Statement>
- <Statement>
- <XI_SP_DATA action="EXECUTE">
<table>XI_SP_DATA </table>
<EMPNO isInput="true" type="CHAR ">901000</EMPNO>
<EMPNAME isInput="true" type="CHAR">Ashish</EMPNAME>
<ADDRESS isInput="true" type="CHAR">Delhi</ADDRESS>
</XI_SP_DATA>
</Statement>
</ns0:MT_DB_SELECT_Response>

Also why you have _Response in MT_DB_SELECT_Response? Response is generally for getting response from DB. Keep you MT with response (and also by closing statement after every execute) and see whether it gives any success

Regards

Suraj

[Former Member · Accepted solution]

Hi abishek,

My scenario is basically JDBC(SQL Server)XIJDBC(ORACLE)(Synchronous)

XI pick the date field from JDBC Sender and goes to ORACLE Database and selects records based upon the condition, and response back and put this data into JDBC(SQL Server),

My process successfully upto receive the response from ORACLE system and after response mapping also successful.after that response message is insert into SQL Server.

In SQL server we have two tables,one DATA table and LOG table, If insert the data into DATA table successfully LOG table updated with DATE and STATUS field not successfully insert LOG table update with DATE and Error status field.

So I impleted "stored procedure" in SQL side.

*********************

Any way i solved that error.

I am using below XML structure with out table field,i followed below blog.

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9618. [original link is broken] [original link is broken] [original link is broken]

My stored procedure name is: XI_SP_KDATA_KLOGDATA

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_DB_SELECT_REQUEST_response xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">

- <Statement_response>

- <XI_SP_KDATA_KLOGDATA action="EXECUTE">

<EMPNO type="CHAR">901064</EMPNO>

<EMPNAME type="CHAR">Sateesh</EMPNAME>

<ADDRESS type="CHAR">Hyderabad</ADDRESS>

</XI_SP_KDATA_KLOGDATA>

- <XI_SP_KDATA_KLOGDATA action="EXECUTE">

<EMPNO type="CHAR">901000</EMPNO>

<EMPNAME type="CHAR">Ashish</EMPNAME>

<ADDRESS type="CHAR">Bangalore</ADDRESS>

</XI_SP_KDATA_KLOGDATA>

</Statement_response>

</ns0:MT_DB_SELECT_REQUEST_response>

But again i faced one error in Communication channel

****************

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KDATA_KLOGDATA' (structure 'Statement_response'): com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function XI_SP_KANLOGDATA_KLOGDATA has too many arguments specified.

********************

My doubt is above error is Stored procedure error or XML Structure error?

Don't hesitate to clarify me.

Thank you.

sateesh

Edited by: sateesh kumar .N on Mar 4, 2010 11:58 AM

Edited by: sateesh kumar .N on Mar 4, 2010 11:59 AM

Edited by: sateesh kumar .N on Mar 4, 2010 12:00 PM

[former_member181985 · Accepted solution]

I hope my blog will help you:

[Best Practices with Receiver JDBC Adapter Document Formats|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/12970] [original link is broken] [original link is broken] [original link is broken];

[former_member200962 · Accepted solution]

Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KDATA_KLOGDATA' 
(structure 'Statement_response'

Wrong approach!

You seem to be using a stored procedure message structure in the response message.....you should be using it in the Request message.....the one which goes into the JDBC.....and the one for which the JDBC sends the response

See whenever you append _response to the MT in a JDBC scenario it is considered (as a JDBC standard) to be a response message that the JDBC is supposed to send back.....however you want to first send the data to the SP and then proceed further.

I would suggest that you look at the blog which I gave, which you are trying to implement and the above logic.

[Former Member]

Hi Kumar,

It seems for empname the attribute type is wrong. You dont have a double quote. Check once again.

Regards,

---Satish

[Former Member]

Hi Satish,

Thanks for your reply,

unfortunately i miss it.But in my XML format the double quote is avilable.

Please help me.

Thank you very much.

Sateesh

[Former Member]

Hi Friends,

I need some more inputs

when i execute the stored procedure , i can get below error.

************************

Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KANLOGDATA_KLOGDATA' (structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

*************************

My XML Message below format:

<Statment>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0001</EMPNO>

---<EMPNAME isInput= "true" type="char">Sateesh</EMPNAME>

---<ADDRESS isInput="true" type="char">Delhi</ADDRESS>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0002</EMPNO>

---<EMPNAME isInput= "true" type="char">Sateesh1</EMPNAME>

---<ADDRESS isInput="true" type="char">Bangalore</ADDRESS>

--<dbStmt action="EXECUTE">

---<table>storedprocedurename</table>

---<EMPNO isInput="true" type="char">0003</EMPNO>

---<EMPNAME isInput= "true" type="char">Sateesh3</EMPNAME>

---<ADDRESS isInput="true" type="char">Hyderabad</ADDRESS>

</Statement>

My table filelds are EMPNO,EMPNAME,ADDRESS all the datatype is "char" i defined.

How i can acheive it?

Regards,

Sateesh

[former_member200962]

Try CHAR instead of char ....i think it is due to the case.....even help section mentiones the datatypes in uppercase

[Former Member]

Hi Abishek,

I used that one also, I am getting same error.

My Stored procedure looking like below :

ALTER PROCEDURE [dbo].[XI_SP_KANLOGDATA_KLOGDATA]

-- Add the parameters for the stored procedure here

--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

(

@EMPNO [CHAR](20),

@EMPNAME [CHAR](20),

@COMPANY [CHAR](20))

AS

SET NOCOUNT ON;

BEGIN

INSERT INTO dbo.[XI_tmp_DATA]

([EMPNO],

[EMPNAME],

[COMPANY])

VALUES(@EMPNO,

@EMPNAME,

@COMPANY)

END

/INSERT FOR XI_tmp_LOG/

BEGIN

INSERT INTO dbo.[XI_tmp_LOG]

([DATE],

[STATUS])

VALUES('SYSDATE',

'null')

END

Please guide me how i can achieve it.

[former_member200962]

--<dbStmt action="EXECUTE">

Are you sure the above can be used.....i think it should be like:

<storedProcedureName action="EXECUTE">

Just check the note given after Fig2 in this blog: /people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

Even help mentions it in the given format (storedProcedureName).....i would suggest that you maintain the same names and cases.

[former_member208856]

Take Steps below :

1. Create One New Statement.

2. Create One node (Sub-Element) for Stored Procedure Name (Node name is same name of Stored Procedure at SQL) in This Statement.

3. Create One Attribute in this Sub-element name ACTION, pass constant EXECUTE in this attribute.

4. Create One Sub-element with attribute at same level, pass constant Stored Procedure Table name.

5. Create One Sub-Element at same level with Field of table & pass the value from payload.

Now create 2 attributes below Field name Sub-element

1. isInput, pass value constant "true".

2. type, pass value constant "CHAR".

it will run the stored procedure at SQL system.

The payload example will be as below :

<StatementName>

<storedProcedureName action=u201D EXECUTEu201D>

<param1 isInput=u201Dtrueu201D type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName>

check the below link also :

http://wiki.sdn.sap.com/wiki/display/Java/receiverJDBCstrucutres+creation

[Former Member]

Hi,

Thank you for your valubles

i changed my mapping structure, after my XML format is below.

But i am getting same error in Communication Channel.

Error

*******

Error processing request in sax parser: Error when executing statement for table/stored proc. 'XI_SP_KANLOGDATA_KLOGDATA' (structure 'Statement'): java.sql.SQLException: ERROR: Invalid XML document format for stored procedure: 'type="<SQL-type>"' attribute is missing for element 'table' (Setting a SQL-type (e.g. INTEGER, CHAR, DATE etc.) is mandatory !)

**********

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_DB_SELECT_Response xmlns:ns0="urn:pg-siemens-com:POCA0013:sample1">

- <Statement>

- <storedProcedureName action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">900001</EMPNO>

<EMPNAME isInput="true" type="CHAR">Sateesh</EMPNAME>

<ADDRESS isInput="true" type="CHAR">HYDERABAD</ADDRESS>

</storedProcedureName>

- <storedProcedureName action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">900002</EMPNO>

<EMPNAME isInput="true" type="CHAR">Ashish</EMPNAME>

<ADDRESS isInput="true" type="CHAR">DELHI</ADDRESS>

</storedProcedureName>

- <storedProcedureName action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">900003</EMPNO>

<EMPNAME isInput="true" type="CHAR">Gregory</EMPNAME>

<ADDRESS isInput="true" type="CHAR">Bangalore</ADDRESS>

</storedProcedureName>

- <storedProcedureName action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">900004</EMPNO>

<EMPNAME isInput="true" type="CHAR">Hemanth</EMPNAME>

<ADDRESS isInput="true" type="CHAR">Mumbai</ADDRESS>

</storedProcedureName>

</Statement>

</ns0:MT_DB_SELECT_Response>

Sateesh

[former_member208856]

This error is due to parsing the XML for Stored Procedure.

your structure is like :

- <Statement>

- <storedProcedureName action="EXECUTE">

<table>XI_SP_DATA</table>

<EMPNO isInput="true" type="CHAR">900001</EMPNO>

<EMPNAME isInput="true" type="CHAR">Sateesh</EMPNAME>

<ADDRESS isInput="true" type="CHAR">HYDERABAD</ADDRESS>

</storedProcedureName>

Change the : - <storedProcedureName action="EXECUTE">

There should be Element with having name of Stored procedure, in place of storedProcedureName, create node with the name of Stored procedure exists in SQL.

like if Stored procedure is sp_test, so create that node with

<sp_test action="EXECUTE">

Also create structure like :

Statement

Sp_test (Sub-element of Statement)

ACTION (attribute)

TABLE (sub-element of Sp_test)

EMPNO (sub-Element of Sp_test)

isInput (sub-element of EMPNO)

Type (sub-element of EMPNO)

EMPNAME (sub-Element of Sp_test)

isInput (sub-element of EMPNAME)

Type (sub-element of EMPNAME)

ADDRESS (sub-Element of Sp_test)

isInput (sub-element of ADDRESS)

Type (sub-element of ADDRESS)

Edited by: Sandeep Kaushik on Mar 4, 2010 12:04 PM

[Former Member]

Thanks Sandeep,

But in this case what is the meaning for Table field?

Generally we pass "Tablename/Storedprocedure name" to Table field.

Please correct me my structure again'

Sateesh

[former_member208856]

The stored procedure will work as below :

first it will execute the Stored procedure using this node

<sp_test action="EXECUTE">

Pass the value of stored procedure in table field also

<table>sp_test</table>

First stored procedure will start & update the same.