Hello All,

I am having a stored Procedure with below syntax.

Signature of StoredProcedure:

CREATE PROCEDURE [aaa].[yyyyyyyyyyyyy] (
@Nf INT,
@XML_OUTPUT XML OUTPUT,
@TLR INT OUTPUT,
)

what dataType should I select in webMethods adapter service under Parameter tab under ParamJDBCTYpe column if I wanted to go with storedProcedure style. And also as what data type should I select Under Output type column in output section.

I don’t want to use storedProcedureWithSignature due to some reasons. Kindly help me to get the solution on this.

Thanks,
RP.

Hi All,
As I didn’t find any alternative, I converted XML to NVARCHAR in StoredProcedure, in SQL Server and specified it as OUPUT type. I am getting the results of StoredProcedure into wM. I am assigning the output of StoredProcedure to a variable of type String. When the storedProceudre is returning more than 43K characters, variable of String is unable to hold all the characters. Any suggestion or work around here.

Thanks all for your time & help.

Thanks,
RP.

What is the need for SP here?

Cant you handle the same in SELECT, INSERT, CUSTOM or DYNAMIC SQL template?

Thanks for your response Mahesh.

I have some business Logic which I have to execute at db side, there are lot of benifits of using SP in my requirement. Let me know the solution for my problem.

Thanks,
RP.

Business logic/requirement? I will try to help you if I can.

Business logic/requirement? I will try to help you if I can.

Based on the input passed ( No of records ) have to connect to DB, join around 20 tables, selecting around 70 columns. And all these data to webMethods.

Thanks,
RP

Hi RP,

You can achieve your requirement,If you follow the below steps.

Step 1:
A). Please try to change your output parameter from xml to NVARCHAR2(MAX) in your stored procedure.

 [b]ex: @xml_data  nvarchar(max) output[/b]

B). Cast the xml data into NVARCHAR(MAX) from your query.

Ex: SET @xml_data = (SELECT CAST(xml_data) as NVARCHAR(MAX) from <your_table>)

Step2:
As you mentioned in webMethods Adapter services,

In Parameters Tab,Please select the paramJDBCType as LONGNVARCHAR for output type.
In result Set Tab, Please choose JDBC Type as LONGNVARCHAR

You can your service after that and will get you an expected result.

Please let me know your response.

Yep this would do the trick with NVARCHAR unless both SP and AS to be changed.

Thanks Bhanu & RMG,

I followed the same which resolved my issue.

Thanks,
RP.