locked
Executing XML Stored procedure with schema name other than "DBO" RRS feed

  • Question

  • Hi All,

    I have to use request-response port to execute the xml stotred procedure with schema name other than "DBO".  Eg: [Schema1].[StoredProcedure]

    SQL adapter by default executes only the "DBO" schema objects. 

    So i gone for WCF-SQL adapter, but the WCF-SQL adapter doesn't provide XML output in response.

    Kindly help me,in this...

    • How to execute stored procedure with schema name other than "DBO" using SQL adapter?
    • How to get the desired XML output from WCF-SQL adapter response when executing XML stored procedure? 

    Thanks,

    Dani



    Monday, September 24, 2012 12:29 PM

Answers

  • Why you are using FOR XML Auto,

    try without Xml auto,

    if FOR XML Auto, is mandatory then use xml Polling in wcf-sql Adapter.

    look this Thread Here

    • Marked as answer by DanielTamil Tuesday, September 25, 2012 1:49 PM
    Monday, September 24, 2012 2:39 PM

All replies

  • Hi

    Please take a look at this blog.

    You will find how to get the Response back while executing Stored Procedure with WCF-SQL Adapter.

    Thanks,

    Deepthi

    Monday, September 24, 2012 1:00 PM
  • Change in schema will not impact on biztalk development . because it will generate xpath and namespace based on Schema

    follow this Link(http://www.codeproject.com/Articles/37808/How-to-invoke-Stored-Procedures-with-the-FOR-XML-c)

    Monday, September 24, 2012 1:18 PM
  • Hi ,

    I have followed the above link, step by step. But finally i ran into the below error.

    "Inner exception: Received unexpected message type 'http://schemas.microsoft.com/Sql/2008/05/Procedures/BIZ#GetPatientRegisterA04RMSToPLUSResponse' does not match expected type 'http://SQLRexc#Root'."

    Thanks,

    Dani

    Monday, September 24, 2012 1:42 PM
  • the message type you have attach with logical request-Respponse port is not write . attach  GetPatientRegisterA04RMSToPLUSResponse message type with port.
    Monday, September 24, 2012 1:55 PM
  • Hi,

    If i attach the GetPatientRegisterA04RMSToPLUSResponse mesage type with response port, i will not get the XML record set.. Instead, it returns dataset. if i get dataset how can i go for mapping

    Monday, September 24, 2012 2:06 PM
  • for the follwoing Sp

    Create  PROCEDURE bob.GetPatientRegisterA04RMSToPLUS @CustomerID nchar(5)
    AS
    select RESULTSID,RESULTText from bob.results

    two schema has been generated

    sqlBinding_bob.xsd

    sqlBinding_GetPatientRegisterA04RMSToPLUS.xsd

    biztalk wcf-sql adapter will always return response in form of GetPatientRegisterA04RMSToPLUSResponse which is resultset.

    Monday, September 24, 2012 2:18 PM
  • I think you are using Procedure Option ,

    try Strongly typed Procedure in category section

    Monday, September 24, 2012 2:26 PM
  • Hi ,

    My question is that..

    for the follwoing Sp

    Create  PROCEDURE bob.GetPatientRegisterA04RMSToPLUS @CustomerID nchar(5)
    AS
    select RESULTSID,RESULTText from bob.results FOR XML Auto

    two schema has been generated

    sqlBinding_bob.xsd

    sqlBinding_GetPatientRegisterA04RMSToPLUS.xsd

    biztalk wcf-sql adapter will always return response in form of GetPatientRegisterA04RMSToPLUSResponse which is resultset.

    How to get the XML result set is my question? I have to use the result for mapping

    Monday, September 24, 2012 2:32 PM
  • Why you are using FOR XML Auto,

    try without Xml auto,

    if FOR XML Auto, is mandatory then use xml Polling in wcf-sql Adapter.

    look this Thread Here

    • Marked as answer by DanielTamil Tuesday, September 25, 2012 1:49 PM
    Monday, September 24, 2012 2:39 PM