Microsoft SQL Connector error on stored procedure without input parameters

SQL Server

The error

Microsoft SQL Connector “Configured request can’t proceed. The input message provided with the request is null. Verify the input value of the request.”

The scenario

I have an Azure SQL database with some tables. I have a stored procedure which will poll for workload. It does not ret require any input parameters. I then create a logic app with an HTTP Listener and XML Validator. When the validation is true I want to execute the stored procedure and send a HTTP Response.

clip_image002

When I run the Logic App (I post a message to the HTTP Listener), I get the following error on the Microsoft SQL Connector

clip_image004

In the logging of the executed step I get the following:

So I tried to create a work-around. I provide the Input XML with the content of the received message, and therefore the input is not null

clip_image005

but then the next error appears:

The solution

The way to execute the stored procedure without even craeting a dummy input parameter.

Go to the API App for the Microsoft SQL Connector and download the stored procedure schemas from the SQL Connector

clip_image006

Import Schema’s in Visual Studio 2012 with BizTalk Services SDK and generate instance (could be any version, but for mappings you need this version to create the needed trfm files, and therefore I use this version in the example).

clip_image007

Copy the content of the generated instance (in this case <ns0:sp_PollForWorkload xmlns:ns0=”http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo” />) and paste it into the input field of the API App in the logic app

clip_image008

The Body.OutputXML returns the result of the stored procedure.

3 thoughts on “Microsoft SQL Connector error on stored procedure without input parameters

  1. Akshay Jain

    How did you then extract the relevant portions (tags) from your XML? If you used Biztalk Xpath Xtractor, can you please post the Xpath?

    Reply
    1. Emiel Kool Post author

      Hi Akshay,

      I return the complete Body.OutputXML to the caller via the statement @{body(‘microsoftsqlconnector’).OutputXml} in the Content field of the Send HTTP Response action.

      Reply
  2. Pingback: API App SQLConnector | Think * Share * Integrate

Leave a Reply

Your email address will not be published. Required fields are marked *