none
Unable to send and retrieve data from SQL server 2008 R2 stored procedure using WCF - SQL Adapter

    Question

  • I have a simple Biztalk 2010 application which is trying to get the data from sql server using a stored procedure.

    I have used the wcf sql adapter to connect to sql server 2008 r2 using windows authentication. ( have left the credentials blank ).

    the wcf send port was automatically created from the imported binding file that the consume wcf adapter service generates. uses "wcf custom".

    the sample applications picks up the messages from the file folder on disk and uses the wcf sql adapter port to get the data from the stored procedure.

    But there seems to be a problem at this point. somehow i am not able to send and receive data back from sql stored procedure into the configured file location on disk. The input messages are getting dehydrated in the server. and the server fails after some retries with the Transmission failure error. i opened up the error tab of the dehydrated message but in vain. its empty with no errors.

    I am guessing this could be some kind of permission problem. how do i resolve this. what are the permissions that i need to enable on sql server to allow biztalk to interact. 

    i have enabled owner permissions to the current logged in windows user in sql server  and also given owner permission for the host instance to interact with the database in question.

    what else am i missing ???

    where could have i gone wrong ??

    below is my orchestration.. which fails to transmit input messages to sql using the wcf -sql adapter. its failing at the  send shape immediately after receive input shape. 



    • Edited by Aquarius05 Tuesday, March 05, 2013 6:25 AM
    Tuesday, March 05, 2013 6:25 AM

Answers

  • if you are not getting any error , you definately getting some warning please check .

    you can use sql and windows authentication both .

    for SQL Authentication specify username andpassword

    for windows auth click on do not use single signon

    Regards

    Mohit

    Tuesday, March 05, 2013 9:10 AM

All replies

  • if you are not getting any error , you definately getting some warning please check .

    you can use sql and windows authentication both .

    for SQL Authentication specify username andpassword

    for windows auth click on do not use single signon

    Regards

    Mohit

    Tuesday, March 05, 2013 9:10 AM
  • Hi,

    1.Create DatabaseRole  ex : BTSExecute

    2. Add your BizTalk send and Receive Host Group Service Account  for the  "BTSExecute"

    3. Add role "BTSExecute" for your Stored Procedure and give Execute permission.

    ___________________________________________________________
    If this is helpful or answers your question - please mark as answer and vote as helpful.


    Raj, http://rajwebjunky.blogspot.com

    Tuesday, March 05, 2013 9:11 AM