CODEWITHSUNDEEP
biztalk
TOMMY WANG
TOMMY WANG

Reputation: 1402

BizTalk WCF-SQL typed stored procedure response schema

Generating the response schema for a typed stored procedure, the stored procedure did some database updates prior to returning the final resultset. The response schema generated by Visual Studio has quite some garbage.

Is there a way to force it to generate a cleaner schema?

enter image description here

The StoredProcedureResultset4 is the only one that matters.

Upvotes: 1

Views: 1451

Answers (1)

DTRT
DTRT

Reputation: 11040

Here's my same answers from MSDN. Unfortunately, the marked Answer will not work for you since there is no way, or it's really, really hard, to capture and suppress result sets from a called Stored Procedure.

The cause is related to the Stored Procedure code.

The Wizard will only generate Schema types for elements that are returned in the response from SQL Server. Meaning, the Stored Procedure is emitting results for those updates so you're getting metadata for them.

The way to solve this is by modifying the SP code to not emit any result on any operation that shouldn't. Basically, if you see it in the result window in SQL Management Studio, you will get schema for it.

status and message are presumably the result of another SP so one way to suppress that is to assign the result to a temp table thus redirecting it form the output stream.

However, if StoredProcedureResultset4 is all that matters, that's all you have to use. There's nothing wrong with just ignoring all the other results provided they always appear in the same order.

Just to be clear, you still have to write the wrapper that suppresses the unwanted results, simply invoking the original SP from a new SP will not change the output, you'll still get the extra result sets.

In fact, a wrapper would be the harder implementation since you'd have to capture and examine all results sets which I don't think is possible.

The more correct way to do this in BizTalk would be a Port Map that strips the unwanted content.

Upvotes: 2

Related Questions