1

I'm trying to call a Stored proc from BizTalk. I have seen many tutorials doing this. Only issue is, once I Consume Adapter -> connect to DB-> select a SP to run, the resulting schema doesn't have any the return values from the Stored proc (I assume this is what is meant to happen). Therefore I can't map the resulting schema to the one I want.

Please correct me if I wrong. This is my understanding:

  1. Consume an Adapter, call a SP from the database in which there are some select statements.
  2. This should result in a schema being generated which should have all the returned columns from the SP. (Do I have to add the output dataset which the SP will return to the schema manually? )
  3. Map this to the desired schema
  4. Output the result.
Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Mike Smith
  • 139
  • 2
  • 2
  • 12
  • 1
    Those steps are correct. Are you not getting the data or not getting the resultset schema (columns)? – Johns-305 Jun 30 '14 at 13:13
  • 1
    Can you post your stored procedure. The above steps are correct. Are you doing 'Select * from table' in the stored procedure? – Bitmask Jun 30 '14 at 13:40
  • Hi. I'm just doing a smiple select all, for test dummy. Here is the stored proc: `ALTER PROCEDURE [dbo].[GetRolesAdmin] AS BEGIN SELECT * from Roles END` I'm not getting the resulting schema(columns from select statemnts), I'm getting the 'request' and 'response' elements in the xsd, but it contains no data at all. There are two attributes, both listed as : '' – Mike Smith Jun 30 '14 at 14:19
  • 1
    Please follow this link with the example.. This was help me. http://lajak.wordpress.com/2011/07/17/biztalk-configure-wcf-sql-adapter-for-outbound-operation-using-typed-stored-procedure/ – GihanLiyanage Jul 01 '14 at 06:55
  • Thanks for the link. That helped me out a alot, I didn't configure it correctly it seems. – Mike Smith Jul 20 '14 at 09:32

1 Answers1

0

Number 2 is not correct;

2.This should result in a schema being generated which should have all the returned columns from the SP. (Do I have to add the output dataset which the SP will return to the schema manually? )

this happens only when you are defining output parameters in your SP, if your stored procedure is returning a record set from select statement the columns will be generated but in the run time, you can get the values with xpath.

in schema design you will only find a generated element of type any xml.

Yasser El-Ekhnawy
  • 44
  • 4