Thinking outside the box (or not): How to properly call a SQL stored procedure without any input parameters?

|  Posted: November 15, 2016  |  Categories: BizTalk Server

First of all, let me say thanks to Saravana for inviting me to be a guest blogger in BizTalk360 blog. I hope this will be the first of many… and why not start creating a series call “Thinking outside the box (or not)” where I will address some common scenarios, requirements, operations, features and so on, and I will provide some tips or best approaches that you can implement.

In this first post, we will address the following topic: How to properly call a SQL stored procedure without any inputs parameters?

In the last few months, I find myself migrating several projects from previous BizTalk versions to BizTalk Server 2013 R2 and revising/improving other projects. I have seen crazy ways to invoke a service or a stored procedure that doesn’t accept any inputs/parameters, for example: a stored procedure that gets all the existing codes from a SQL table.

Taking this sample, calling a stored procedure that gets all the existing codes from a SQL table, let’s see all the different approaches that I found. Why shouldn’t you do some of them and find out what’s the best approach to achieve this requirement in BizTalk Server. But be aware that all of these approaches actually work, of course, one better than others.

In this Proof-of-Concept (PoC), we have a simple SQL Server table called Codes that only contains a code identifier that is unique in the system, as you can see from the picture below and, once again, we will try to call a stored procedure that gets all the existing codes from that specific SQL table.

sql-query

We have the following basic Stored procedure:

CREATE PROCEDURE [dbo].[usp_GetCodes]
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

— Insert statements for procedure here
SELECT Code
FROM Codes
END

For all the approaches in this PoC, basically what we will do is create an orchestration that:

  • Receives a message that will trigger the orchestration
    • It can be by using the Scheduled Task Adapter to send a message to trigger the orchestration;
    • Or using a file adapter in which we will drop a message in a particular folder in our file system;
  • Create the SQL stored procedure GetCodes request message
  • Call the stored procedure and receive the response
  • And send the stored procedure result to an archive folder in our file system

So basically, all the approaches will have a similar orchestration structure:

orchestration-structure

Of course, in real case scenarios, this orchestration will probably have more additional steps, like after retrieval of the codes we might retrieve additional data based on the earlier retrieved codes, filter the information or any other thing. But for this PoC let’s keep it simple.

The most common approach

The most common approach that I have found to solve this problem, is actually to create a stored procedure with a dummy input parameter, so instead of using the stored procedure described above, customer actually had this kind of stored procedure:

CREATE PROCEDURE [dbo].[usp_GetCodesWithDummyInput]
— Add the parameters for the stored procedure here
@Dummy nvarchar(100)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
— Insert statements for procedure here
SELECT Code
FROM Codes
END

Note: This is the only approach that uses a different stored procedure, all the others approaches are using the first stored procedure described in this post, i.e., without any input parameters ([dbo].[usp_GetCodes])

The reason for them doing that is that now they can easily create a map to create the request message which is used to invoke the stored procedure, by simply dragging the input parameter to the Dummy element of the stored procedure.

invoke-stored-procedure

To be fair, it is actually a simple and effective solution to solve this problem, but here are the reasons why you shouldn’t use this approach:

  • We are using unnecessary elements in the Stored procedure schema, unnecessary because we are not using them in the SQL query to obtain the required result and this is actually adding more payload, although small, to the message;
  • We are using unnecessary artifacts, in this case, a map. This map needs to load two schemas from which one them is also unnecessary, because we don’t need any elements from the source schema to be mapped to the destination schema, but maps always need two schemas;
    • In some orchestrations/processes I saw 2 or 3 maps had been developed to solve these kind of problems, which is 2 or 3 unnecessary artifacts that also consume memory.

In general, although it is a simple solution to implement, is not the best way to address this kind of problems/requirements.

The crazy approach

As you can probably imagine by the approach name that I gave, this will be an even worse implementation.

There are, at least, two ways to create a message inside an orchestration and the only thing in common that they have is that they need to be inside of the Construct Message shape:

  • One is by using a Transform shape and in this case using a map to generate the message(s) and you will be able to specify one or more input messages and one or more output messages;
  • The other is to use the Message Assignment shape that enables you to construct messages by assigning one message to another, assigning individual message parts, use .NET or calling a .NET class/method to construct the message.

But some developers forget the second option, using Message Assignment shape, and because the request schema generated by the SQL adapter doesn’t have any inputs, as expected, it will be very strange and confusing for a BizTalk developer to use this kind of schemas in a map.

maps

However, this does not disable or discourage them to innovate! And in some scenarios, I saw this kind of maps being created and used:

maps-1

In the absence of an element to map from source to destination, and because the map requires at least one mapping rule inside, they map one of the element from the source to the root node in the destination schema.

So, why you should avoid this approach:

  • You are actually creating an invalid message inside the map. If you try to test the map you will receive the following output error:
    • error btm1046: Output validation error: The element cannot contain text. Content model is empty.success-invoking
      • This solution actually works, because we normally use the default XML Send pipeline in the send port and this pipeline will only perform a default validation to the message and not a deep validation, i.e., it will examine only the namespace and the root node name of a message to identify and validate if is a valid schema, it will not detect extra elements in message body.
      • For the same reasons described in the previous approaches, we are using unnecessary artifacts, in this case once again a map to accomplish this task; Unnecessary artifacts that also consume processing memory.

The unnecessary approach

This approach is actually very similar to the previous approach, the crazy approach, but in a smart way! A way that I never imagined to use, to be honest, but that can be helpful in some situations.

Everything is exactly the same as the previous approach, but instead of mapping a random element for the source schema to the root name of the destination schema, which will generate an invalid message,iIn this approach, we are:

  • Adding a String Concatenate Functoid onto the grid by dragging it from the toolbox
  • Drag-and-drop a link from the String Concatenate Functoid to the element root name of the destination schema
    string-concatenate
  • Double-click in the String Concatenate Functoid and set an empty string (without any blank character) as the input of the String Concatenate Functoid
    configure-string-concatenate-functoid

And surprisingly, this does the trick and it will generate a valid message:

<ns0:usp_GetCodes xmlns:ns0=”http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo” xmlns:ns3=”http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetCodes” />

output

So, why you should use this approach:

  • Only for the reason that we are using unnecessary artifacts, once again maps, to accomplish this task that consumes processing memory and CPU. They load unnecessary schemas, the source schema is being used meaningless. And again, if you think that in some scenarios I found in a single orchestration two or three identical maps being used… it’s a lot of unnecessary artifacts being created in your solution that at the end will penalize the performance of your solution.

The only situation I can imagine this approach being useful is when we are creating this type of scenarios using Content Based Routing, i.e., without any kind of orchestrations but, until now, I never encountered this requirement.

The C# developer approach

I will not address this approach, but I think you may encounter that a lot, by using a helper class library to invoke the SQL Stored procedure using C# code.

In my personal opinion, the best approach

And finally, we arrive to what, in my personal opinion, is the best or most correct solution to accomplish this task.

So, if you were following the post carefully, you may have noticed that in all the previous solutions we have used a map to generate the SQL Stored Procedure request message and we already saw why that is not the best approach. In this approach, what we will be doing is using the Message Assignment shape, instead of the Transform Shape (map), with the support of inline .NET code to create the request message.

call-sql-stored-procedure

So, for that we will also need to create a support variable in the orchestration:

  • In the Orchestration View window, right-click the Variables folder and then click New Variable.
  • Set the variable Name to “varXML” by typing a name in the Identifier property in the Properties window or by selecting the variable and press “F2”.
  • In the variable properties, we need to associate the variable with a type, and in this case, we will select the option “<.NET class…>” and then select System.Xml.XmlDocument class type
    variable-properties

We will be using this variable to manipulate the creation of the SQL request message.

The next important thing is to actually have a proper sample of the SQL request message and for us to easily achive that we can go to the schema generated by the WCF-SQL adapter, normally something like “TypedProcedure.dbo.xsd”, right-click and choose Generate schema.

Of course, sometimes is not that easi, sometimes we have several stored procedures calls, that will handle several operations like: inserts, deletes or selects. And sometimes, when we try to generate a schema instance, it will not generate the message that we want. In that cases, you need to open the schema in the Schema Editor:

  • Select the Schemanode in BizTalk Editor, ricght-click and select the “Properties” option.
  • On the Properties windows, set the “Root Reference” property to the root element that you want to generate
    • This property specifies the node that represents the outermost element in the XML business document represented by the schema, and is important when you have created more than one top-level node in the schema.
    • In our, case you should select “usp_GetCodes
      root-reference
    • From the Solution Explorer windows, right-click one more time in the schema and choose Generate schema
      output-1

Note: if you want, you then can revert the “Root Reference” property to “Default”.

Now you just need to copy this XML sample and place it inside the Message Assignment shape, with support of our variable that we created previously, something like this:

varXML  = new System.Xml.XmlDocument();

varXML.LoadXml(@”<ns0:usp_GetCodes xmlns:ns0=””http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo”” /> “);

msgSQLGetCodesReq = varXML;

xml

Why is this the best way for me?

  • I don’t need to use unnecessary maps to generate the SQL request message
    • If I need to create another similar message, I will follow the same approach and I will reuse the “varXML” variable to manipulate construction of the message
  • It is intuitive and in the overall, I think it will have a better performance

You can find the all the source code here: BizTalk Server: How to call a SQL Stored procedure without inputs

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

  • Stéphane MALTI

    Are you basically saying that creating an unnecessary orchestration has more performance than creating an unnecessary map? I’m not sure at all

    How does your orchestration handle the errors in case of sql name changing? The retries…etc.

    The advantage of using the schema and maps is that you can reuse them for instance in composite operation. You’re suggestion cannot be reused!

One Platform Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

One Platform - Operations, Monitoring and Analytics Software
BizTalk360

microsoft biztalk

Learn more

Over 500 customers across 30+ countries depend on BizTalk360

One Platform - Operations, Monitoring and Analytics Software
ServiceBus360

Azure service bus

Learn more

Start managing your Azure Service Bus namespaces in minutes

Back to Top