So, you have to run a stored procedure on a SQL Server from your BizTalk install.  Not only that but you have to send parameters to the stored proc and consume the results as well.  I found that it really does turn into a pain when you need to do it.  If you use temp tables in your stored proc then what you get back is a loosely named schema  that looks like this.

The first set is the schema for what you need to pass as parameters to the stored procedure.  The second set is the data that is returned to you from that same procedure.

This is really not much use if you want to do anything with the results from the stored procedure.   The <Any> nodes are where your content should be, but the schema that is returned back from the stored proc does not have everything you need.

 

 

 

So, below is how you add a strong name to that dataset so you can use it in Orchestrations or in maps.  I will start at the beginning, with adding the Generated Items and then all the way to  being able to use the items in a map.

 

 

So, first create a new project in Visual Studio 2010 and add a BizTalk project to it.  I always select the empty project since I don’t want all the sample data in the project.  Once the project is created you need to go and get the schema for the stored procedure as VS 2010 and BizTalk see it.  You do that by adding a generated item, by right clicking on the project name and then hovering over add, and then selecting “Add Generated Item.

Next you select Consume Adapter Service and then click on add.  This is where it is going to start asking you the harder questions if you have never connected to a SQL server with BizTalk projects in Visual Studio 2010.

 

 

 

This is the Consume Adapter Service Wizard and once configured and ran it will create the schema files and the binding file that you will be using later.

If you know the URI connection string to your SQL Server and database then go ahead and knock yourself out.  However, since I know that I have to be in the configuration for that connection I go ahead and hit configuration button and enter everything from there.

 

 

 

So, click on the drop down titled “Select a binding” and choose sqlBinding.  The click on the configuration button and start filling out the fields you need.  We always use Windows Authentication for our databases so you can skip the first tab of the window that opens up.  Click on the second tab and start entering the information for your SQL Server.

So this is where I found a couple of “Got-yas”.  One it does not attempt to use the default port for SQL if you don’t put one there.  So you have to put in the port number along with the server name for it to connect.

Second, don’t put any special characters in the InboundID field.  I tried using something with an underscore “_” and BizTalk died with it in there.  I normally name this something having to do with the stored proc but I am being lazy right now.

 

 

 

 

 

If you have used Typed Polling before to get messages into BizTalk then you are probably familiar with this screen.  It’s where you set the polling information for getting the data from SQL into a message.

We are going to do basically the same thing but a few changes to it.  One, I leave the “InboundOperation Type” to polling.  Then I just use some random table to get a count over 0 (Zero) in the “PollDataAvailableStatement” field. This time I used  “Select count(*) from dbo.Products” because I know that the table will always have something in it.

For the “PollingStatement field I add a SQL command calling the stored proc along with all of the parameters.  This is what I am using for this one.

Exec sp_Get_Direct_Print_Details ‘RC_SAM3067’, ‘Reg’

 

 

 

 

So now, click on OK and it takes you back to the Consume Adapter Service Wizard.   Click on Connect and if everything went as planned you should connect to the database and the Category field gets populated with information from the database.

Leave the Contract type at client, and in the “Select a category” field click on Procedures to select it.  That will cause a list of all the stored procedures on the database to be displayed.  Select the stored procedure you wish to run and then click on add.

This will put the stored procedure down into the “Added categories and operations”.  Notice the name of the Node ID and it should be something like Procedure/DBO/your sp name.  You will start getting used to this in the next part of this procedure.

Add a FileName Prefix if you want and then click on OK.

 

 

 

Now the screen should disappear and you will find three new objects in your solution explorer.  The first one is the DataSetSchema.xsd, then the Procedure.dbo.xsd, and finally an oddly named XML file that will be something like WcfSendPoirt_SqlAdapterBinding_Custom.bindinginfo.xml.   You will end up using all three of the items so don’t delete them.  Before I clicked on the OK button of the last step I entered Sch_ into the filename Prefix field so don’t be alarmed if you don’t see the same file names as what you have.

Now comes the fun part….  Open Up BizTalk Server Administration Console and Create a new application.  Just name it anything because you are going to delete this as soon as we are done.  You also don’t need anything else from your VS project.

So, now that the Application has been created you need to create a new Receive Port.  OPen up the application viewer and right click on Receive Ports and select new\One-way receive Port.

Give it a name that is simple to remember because we will be using it in a few minutes. Click on Receive Locations on the left and then New…

 

Once the Receive Location Properties window is open change the Type drop down to WCF-SQL and click on Configure.  This opened up almost an identical configuration windows for the database that VS 2011 did. On the General Tab of this window click on configure and set the database connection information to be the same as the VS project.  Then click on the binding tab and do the same thing, setting the Data Available Statement and the Polling Statement.

Now click OK three times to save the location and port configuration.

 

I have a feeling that putting “Select 1” in the PolledDataAvailableStatement would work the same as the count statement but I have not tried it yet.  I may update the page if it works when I try it.  If someone tried it before me then let me know.

Now we need to create a File Send port and set the filter so it will route the messages from the Receive Port to a local file directory.  Right click on Send Ports and select New\Static One-Way Send Port.  You can leave the name the same and then change the Type to “File” and click 0n Configure.  Set the Destination Folder to something easy to get to like c:\temp, and then change the File Name to getSchema.xml.  Now click on OK to close the configuration window.

 

Now that the file part is configured we need to configure the Send Port to “Subscribe” to the messages coming out of the database.  Click on Filters and then set Property to “BTS.ReceivePortName”, Operator to “==”, and the Value needs to be set to the name you gave your RFeceive port.  For me, thats RecSQL.

 

Now we get to run our BizTalk application.   First, open up a Windows explorer window to the directory that you told the send port to drop the file.  Then go back to BizTalk and right click on the send port and click start, then right click on the Receive Location and click on Enable.  Switch back to the file explorer window and  if you configured everything correctly you should soon have a file dropped in that directory from BizTalk.

Switch back to BizTalk and disable the receive port down.  Or else every thirty seconds the file will be replaced as the polling will keep going out and getting the same file.

Open getSchema.xml with your favorite editor and remove the schema definition  from the file.  What you are looking for is the <DiffGram> and the schema name spaces that are in the file.  So, anything that starts with <xs: or </XS: you can delete.  Some may say to remark it but I just find it easier to get delete it. Once you have done that save the file and close it.

 

 

 

 

Now go to Start\All Programs\Visual Studio 2010\Visual Studio Tool and run the Visual Studio Command Prompt. You really need to run this one and not just open up a command prompt because this one adds the paths and environmental settings that you are going to need.  Change to the directory where you saved the file, in my case C:\temp.

Confirm that you are in the correct directory and then run the following command.

xsd.exe getSchema.xml

When this runs it is going to create three WSD schema definition files. They will be something like getSchema.xsd, getSchema_app1.xsd, and getSchema_app2.xsd.  One of the files is going to have the the schema for just the diffgram that has the elements from the stored procedure result set.  For me that has always been the _app1.xsd.  Open them up in your editor and it will be the only one that has all of the elements in it.  Rename that to match your naming convention and copy it to the project directory where all the other schema definition files are located. (The root of the project).  Go back into Visual Studio now and add an existing item and select the file you just dropped.

You will notice here that I named my new schema Sch_Strong_Name_Dataset.xsd.  This is what I am going to use now to add a strong name values to my procedures result set. But I have to do a couple things to it first or it will not work correctly.

 

 

 

Open the new schema file in Visual Studio, and single click on the <Schema> node to select it.  Then in the properties window click on the … for Imports.  Remove the first two elements that refer to the files that you did not copy over.  For me that was the first two lines that have a location of getSchema.xsd, and getSchema_app2.xsd.  If you forget to do this part then you will receive an error on the next step. Click on OK to save the changes and you can close and save your open schema file.

Now open up the DataSet schema that was created when you did the Add Generated Items.  For me that is Sch_DataSetSchema.xsd and this is what it looks like when you open up all the nodes.  We have two fields in the data set and they are both set to <any> and we are going to replace the second one.

 

 

 

 

But first we need to import the strongly named data set into our collection.  So single click on the <Schema> node and then on the … next to Imports in the properties.

Leave the drop down on XSD and click on add.  Manauver through the +s until you find the schema that we added to our project.  Select it and then click on OK twice to close the windows.  You might want to save your open schema file now.

 

With the schema file open delete the second <Any> node that is under DataSet.  Right click on DataSet and select Insert Schema Node\Child Record.  Name it diffgram and then single click on it to select it.  Over in the properties window click on the drop down for Data Structure Type and choose  ns0:diffgram.

 

 

 

Your diffgram can now be opened and it will have all of the elements under it that you needed.   Save your schema file and start using your strongly named schema in maps or orchestrations.

 

 

 

 

 

If you found this helpful them please leave a comment or a link back to this article.