Stored Procedure in SQL Server 2005

[Former Member]

Hi Friends,

JDBC(oracle)-XI-JDBC(SQL Server2005) scenario.

XI pick the data from JDBC(oracle) and stores the data into target Database(SQL Server).This is generally normal scenario.

But my requirement is Once XI pick the data from source database and it can insert target database table.

once XI insert the data into target database log table is create with DATE and STATUS coloumn.

How i can implement the stored procedure in SQL Server 2005?

can you provide some examples, it would be very helpful me.

Thank you very much.

Sateesh

[former_member208856]

Stored procedure will be created in SQL & we can execute this from PI mapping.

For Executing Stored Procedure at SQL from PI Interface.

Use one more statement in Message Mapping for Stored Procedure.

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_member207622]

Hi

Please follow the links , I am not sure about this but yu might get some help out of it

http://msdn.microsoft.com/en-us/library/ms190782(SQL.90).aspx

http://www.c-sharpcorner.com/UploadFile/raj1979/SQLViewsStoredProcedures10032008152021PM/SQLViewsSto...

http://www.devasp.net/net/articles/display/710.html

Regards

Ninad