Sender JDBC support the stored procedure in SQL Server 2005

[Former Member]

Dear All,

My question here is , Sender is JDBC adpter support to call the Stored procedure in Microsoft SQL Server 2005?

I followed the below thread before posting this question

In the thread Suraj response has mentioned SAP note: 941317, I checked the note, Note says it supported only ORACLE DBMS versions.

Could you please clarify about this to possibility to call the stored procedure in Microsoft SQL server 2005?

Thank you in Advance

Sateesh

[maciej_jarecki · Accepted solution]

Hi

Please specify your question what problem do you have.

Meanwhile this will proviede you more information

Overview of JDBC adapter

http://help.sap.com/saphelp_nwpi711/helpdata/en/8f/0b674240449c60e10000000a1550b0/frameset.htm

Jdbc Adapter details

http://help.sap.com/saphelp_nwpi711/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Regards

Maciej

[Former Member · Accepted solution]

Hi Maciej,

My question here is

Sender JDBC adapter support to call the Stored procedure directly?

Generally I used the SELECT query in JDBC sender adapter "Query Statement" parameter,

According to our requirements,Insted of the SELECT query we are planning to call the Stored procedure in MICROSOFT SQL Server 2005.

So my question here is the " Query Statement" parameter supports to call the Stored Procedure

for ex:

Query Statement : CALL StoredProcedureName;

Please confirm me.

Thank you,

Sateesh

[maciej_jarecki · Accepted solution]

Hi

In query you enter SP name

in update query put

<TEST>

Remember that SP Massage has difrent format that insert message for example

Regards

Maciej

[Former Member · Accepted solution]

Hi Sateesh,

To answer your query, Yes it does. SP call works with SQL Server 2005 as well, the SP call should be "execute SPNAME <paramas>". In the place of update statement put any junk value as mentioned in the earlier comment.

But just one thing to be noted, the select or any other query returning the desired resultset must be the first statement in youre SP returning value to the calling app. Means if there are any update, deletion or intermediate select queries to be used with in your SP, those all should be placed after the Main Select query that returns the desired resultset. Variable declarions and assignments are allowed.

But if the seq of statements in your SP are like:

@var1 Varchar

Update <tablename>....

Select * from <tablename>

Then the output of the SP is :

1 row updated

<the resultset from select>

In such cases, PI can recognise the first value returned only, that is "1 row updated" and hence the returned resultset wouldn't be seen by PI or not be passed to integration engine. Hence the update should come after the main Select.

One more interesting piece of information, even if you copy and paste the entire SP code in place of the query string of Sender JDBC adapter, that too will work with all your variable declarations, multiple queries and updates and everything. Just have to follow the above rule.

Let us know what you find.

Regards,

Suddhasatta