First, you must modify the stored procedure created in the SQL adapter sample to help derive the schema of the returned data.

This topic shows you how to do the following:

1. Modify a stored procedure.
   
   
2. Create an empty BizTalk project.
   
   
3. Add metadata to the BizTalk project.
   
   
4. Revert the stored procedure.
   
   
5. Add a required schema to the project.
   
   
6. Create an orchestration.
   
   
7. Create a map.
   
   
8. Create new messages.
   
   
9. Build the project.
   
   
10. Create a strong name key file.
    
    
11. Deploy the solution.
    
    
12. Create a send port to communicate with the SQL adapter in BizTalk Explorer.
    
    
13. Create a receive location by using BizTalk Explorer.
    
    
14. Bind ports.
    
    
15. Start an orchestration.
    
    

You must build and initialize the SQL adapter sample to be able to view the Loan Acceptance sample. For more information about installing the SQL adapter sample, see SQL Adapter Loan Application.

Once installed, the Loan Acceptance project is located in the <drive>:\Program Files\Microsoft BizTalk Server 2006\SDK\Sample\Adapter\SQLAdapter\Loan Acceptance folder.

1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2. In Enterprise Manager, in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, expand (LOCAL) Windows NT, expand Databases, expand BTS2004_SQL_Adapter_loans, and then click Stored Procedures.

3. In the right pane, double-click SP_Save_Loan_info.

4. In the Stored Procedure Properties - SP_Save_Loan_info dialog box, in the Text box, following select * from Loans where LoanID = @newLoanID for xml auto, type , xmldata.

   The following figure shows the updated procedure.

   Figure of the SP_Save_Loan_info stored procedure
   

   Note

   The SQL adapter only handles stored procedures that return XML. In this example, there is one simple SELECT FROM table FOR xml auto. Examine the stored procedure SP_Get_Agent_and_Interest() for a procedure that returns one or two items that you need for XML raw data. The added , xmldata would cause the SQLXML to emit a schema as well as data. The SQL Add Adapter Wizard uses , xmldata, which you must remove before actual operation of the SQL adapter or else the schema will confuse the BizTalk Messaging Engine.

5. Click OK.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. When prompted, connect to the SQL Server instance that contains the BTS2004_SQL_Adapter_Loans database.

3. In SQL Server Management Studio, in the left pane, expand Databases, expand BTS2004_SQL_Adapter_Loans, expand Programmability and then click Stored Procedures.

4. In the right pane, right-click SP_Save_Loan_info and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

5. Add the text , xmldata to the last statement in the stored procedure so that the last statement reads as follows:

   select * from Loans where LoanID = @newLoanID for xml auto, xmldata.

   Note

   The SQL adapter only handles stored procedures that return XML. In this example, there is one simple SELECT FROM table FOR xml auto. Examine the stored procedure SP_Get_Agent_and_Interest() for a procedure that returns one or two items that you need for XML raw data. The added , xmldata would cause the SQLXML to emit a schema as well as data. The SQL Add Adapter Wizard uses , xmldata, which you must remove before actual operation of the SQL adapter or else the schema will confuse the BizTalk Messaging Engine.

6. On the Query menu, click Execute to modify the stored procedure.

1. Click Start, point to Programs, point to Microsoft Visual Studio 2005, and then click Microsoft Visual Studio 2005.

2. On the File menu, point to New, and then click Project.

3. In the New Project dialog box, in the Project Types section, click BizTalk Projects, and then in the Templates section, click Empty BizTalk Server Project.

4. In the Name box, type SQL_Adapter_SP, and then click OK.

   The new SQL_Adapter_SP project appears.

1. In Visual Studio 2005, in Solution Explorer, right-click SQL_Adapter_SP, point to Add, and then click Add Generated Items.

2. In the Add Generated Items – SQL_Adapter_SP dialog box, in the Templates section, click Add Adapter Metadata, and then click Add.

3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and click Next.

   You can leave the other entries on the page blank or with their default values, unless you are connecting to a remote SQL Server computer. If connecting to a remote SQL Server computer, type the name of the server in the SQL Server box, and the name of the database in the Database box. Leave the Port box empty.

4. On the Database Information page, click Set.

5. In the DataLink Properties dialog box, on the Connection Tab, do the following.

   Use this	To do this
   Select or enter a server name	Type localhost.
   Enter information to log onto the server	Select Use Windows NT Integrated security.
   Select the database on the server	Select BTS2004_SQL_Adapter_loans from the drop-down menu.

6. Click OK.

   This sets the connection string for the SQL adapter.

   Note
   This connection is stored only when you run this wizard. The actual connection will be formed later during the port binding process.

7. Click Next.

8. On the Schema Information page, do the following.

   Use This	To do this
   Target namespace	Type http://Microsoft.Sample.SQLAdapter.
   Select the port type	Select Send port.
   Request root element name	Type MyRequestRoot.
   Response root element name	Type MyResponseRoot.

   Note
   Write this information down. You will need it later. Take note that everything you type is case-sensitive, for example, SQLAdapter is not the same as SQLadapter.

9. Click Next.

10. On the Statement Type Information page, select Stored Procedure, and then click Next.

11. On the Statement Information page, select SP_Save_Loan_info from the drop-down box.

12. On the Parameter values page, click once to the right of the check box in a cell in the Value column to select the cell, then wait for one or two seconds and click again to the right of the check box to edit the cell. Ensure that the check box is not checked as the check box sets the value to NULL. Repeat this procedure for each cell in the Value column and enter the following values.

    Use this	To do this
    Value cell of the @Amount row	Type 100.
    Value cell of the @City row	Type Redmond.
    Value cell of the @FirstName row	Type Max.
    Value cell of the @LastName row	Type Benson.
    Value cell of the @State row	Type WA.
    Value cell of the @Street row	Type 123 Main St.

    Note
    The check box sets the argument to NULL.

    Note
    Clicking twice in one place will toggle the NULL value check box.

    Note
    The arguments entered in this step are stored only for schema generation. The actual arguments during operation will be in a solicit-request message.

13. Click Generate.

    Completed Statement Information page
    

14. Click Next, and then click Finish to complete the wizard.

    The Add Adapter Wizard generated and added two files to your project, BizTalk Orchestration.odx and SQLService.xsd. SQLService.xsd is the schema containing both the Request and Response structures. BizTalk Orchestrations.odx is a blank orchestration with some preconfigured port types.

    New files created in the BizTalk project
    

    Note
    If you know that the stored procedure only returns one row (as in this example), adjust properties of elements in the response schema from the default: Max Occurs=unbounded ; Min Occurs=0.

15. Now go back to SQL Server Enterprise Manager (or SQL Server Management Studio) and return the stored procedure to its original state.

1. In SQL Server Enterprise Manager, in the right pane, double-click SP_Save_Loan_info.

2. In the Stored Procedure Properties - SP_Save_Loan_info dialog box, in the Text box, remove , xmldata from the following line:

   select * from Loans where LoanID = @newLoanID for xml auto, xmldata

3. Click OK.

4. To close Enterprise Manager, on the File menu, click Exit.

1. In the right pane, right-click SP_Save_Loan_info and click Script Stored Procedure As, ALTER To, New Query Editor Window.to edit the stored procedure text.

2. Remove the text , xmldata from the last statement in the stored procedure so that the last statement reads as follows:

   select * from Loans where LoanID = @newLoanID for xml auto

3. On the Query menu, click Execute to modify the stored procedure.

1. In Visual Studio 2005, on the Project menu, click Add Existing Item.

2. In the Add Existing Item - SP_Adapter_SP dialog box, browse to <drive>:\Program Files\Microsoft BizTalk Server 2006\SDK\Samples\AdapterUsage\SQLAdapter\Loan Acceptance, select LoanApplication.xsd, and then click Add.

3. In Solution Explorer, double-click LoanApplication.xsd.

4. In Solution Explorer, change the Target Namespace value to http://SQL_Adapter_SP/LoanApplication.

   The LoanApplication.xsd file is now included in your project.

1. In Solution Explorer, double-click BizTalk Orchestration.odx.

2. From the BizTalk Orchestrations Toolbox, drag a Port onto the right Port Surface on the design surface.

3. In the Port Configuration Wizard, on the Welcome page, click Next.

4. On the Port Properties page, in the Name box, type SPROC_Call_Port, and then click Next.

5. On the Select a Port Type page, do the following.

   Use this	To do this
   Select the port type to be used for this port	Select Use an existing Port Type.
   Available Port Types pane	Select SQL_Adapter_SP.SQLServiceExec.

6. Click Next.

7. On the Port Binding page, do the following.

   Use this	To do this
   Port direction of communication	Select I'll be sending a request and receiving a response.
   Port binding	Select Specify later.

8. Click Next.

   Note
   You can only use the Specify later (Late Binding) option on SQL adapter ports.

9. On the Completing the Port Wizard page, click Finish.

1. In Solution Explorer, double-click BizTalk Orchestration.odx.

2. On the Orchestration View tab, right-click Messages, and then click New Message.

3. In the Properties window, do the following.

   Use this	To do this
   Identifier	Type LoanApplicationMsg.
   Message Type	Expand Schemas, and select SQL_Adapter_SP.LoanApplication.

4. On the Orchestration View tab, right-click Messages, and then click New Message.

5. In the Properties window, do the following.

   Use this	To do this
   Identifier	Type SPROC_input.
   Message Type	Expand Multi-part Message Types, and select SQL_Adapter_SP.procedureRequest.

6. On the Orchestration View tab, right-click Messages, and then click New Message.

7. In the Properties window, do the following.

   Use this	To do this
   Identifier	Type SPROC_result.
   Message Type	Expand Multi-part Message Types, and select SQL_Adapter_SP.procedureResponse.

1. Drag the following shapes onto the orchestration design surface, and set the properties as specified in the following table:

   Shape	Location	Property Settings
   Receive	Between the Begin (green circle) and the End (red octagon)	Name: Receive_Application Message: LoanApplicationMsg Activate: True Leave the Operation property blank. A red exclamation point appears on the shape in the orchestration indicating that this property must be set.
   Construct Message	Below the Receive_Application shape	Name: Construct_SPROC_Input Messages Constructed: SPROC_input
   Transform	Inside the Construct_SPROC_Input shape	Name: Make_SPROC_Argument Map Name: Click the ellipsis (…) button. In the Transform Configuration dialog box, in the left pane, select Source, and then in the right pane, select LoanApplicationMsg as the Variable Name. In the Transform Configuration dialog box, in the left pane, select Destination, and then in the right pane, select SPROC_input.parameters as the Variable Name. Clear the When I click OK, launch BizTalk Mapper box. Click OK, and then return to the BizTalk Orchestration.odx tab.
   Send	Below the Construct_SPROC_Input shape	Name: Call_SPROC Message: SPROC_input Again, leave the Operation property blank.
   Receive	Below the Call_SPROC shape	Name: Receive_SPROC_Result Message: SPROC_result Again, leave the Operation property blank.
   Send	Below the Receive_SPROC_Result shape	Name: Save_Result Message: SPROC_result Again, leave the Operation property blank.
   Port	On the left Port Surface, next to the Receive_Application shape	Name: LoanApplication_In_Port Create a new Port Type Port Type name: Loan_Application_Port Communication Pattern: One-Way Access Restrictions: Internal Port direction of communication: I'll always be receiving messages on this port. Port binding: Specify later
   Port	On the left Port Surface, next to the Save_Result shape	Name: Save_Result_Port Create a new Port Type Port Type name: Receive_Results_Port Communication Pattern: One-Way Access Restrictions: Internal Port direction of communication: I'll always be sending messages on this port. Port binding: Specify later

2. Connect the ports to the shapes as specified in the following table.

   Port	Name	Connect To
   LoanApplication_In_Port	Request	Receive_Application
   SPROC_Call_Port	Request	Call_SPROC
   SPROC_Call_Port	Response	Receive_SPROC
   Save_Result_Port	Request	Save_Result

   The orchestration uses these connections to generate a property value for the Operation properties that were left blank in the orchestration.

   For more information about connecting ports, see Using Ports in Orchestrations.

3. Verify the orchestration looks like the following:

   

1. In Solution Explorer, double-click Make_SPROC_argument.btm.

2. In the Source Schema and Destination Schema panes, expand all of the nodes so that all of the elements are visible.

3. Connect the following source schema nodes to the destination schema nodes by dragging and dropping from the source schema node to the destination schema node.

   Source Schema Node	Destination Schema Node
   FirstName	FirstName
   LastName	LastName
   Street	Street
   City	City
   State	State
   RequestedAmount	Amount

1. On the File menu, click Save All.

2. On the Build menu, click Build SQL_Adapter_SP.

1. Click Start, point to Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

2. At the command prompt, browse to the folder in which you saved your project, type sn -k SQL_Adapter_SP.snk at the command prompt, and then press ENTER.

   You should receive a message at the command prompt stating that the system wrote the key pair to SQL_Adapter_SP.snk.

3. Assign the strong name to the project. For instructions about assigning strong name keys, see How to Configure a Strong Name Assembly Key File.

• In Solution Explorer, right-click SQL_Adapter_SP, and click Deploy.

1. On the View menu, click BizTalk Explorer.

2. In BizTalk Explorer, expand BizTalk Configuration Databases, select the Configuration database, right-click Send Ports, and then click Add Send Port.

   Note
   The BizTalk Management database is also referred to as the BizTalk Configuration database.

3. In the Create New Send Port dialog box, select Static Solicit-Response Port, and then click OK.

4. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, do the following.

   Use this	To do this
   Name	Type MyLoan_Acceptance_SPROC_Port.
   Transport Type	Select SQL.
   Address (URI)	Click the ellipsis (...) button to open the SQL Transport Properties dialog box.

5. In the SQL Transport Properties dialog box, do the following.

   Use this	To do this
   Connection String	Click the ellipsis (...) button to open the Data Link Properties dialog box, and proceed to step 7.
   Document Target Namespace	Type http://Microsoft.Sample.SQLAdapter.
   Response Root Element Name	Type MyResponseRoot.

6. Click OK, and then proceed to step 9.

7. On the Connection tab, do the following.

   Use this	To do this
   Select or enter a server name	Type localhost.
   Enter information to log onto the server	Select Use Windows NT Integrated security.
   Select the database on the server	Select BTS2004_SQL_Adapter_loans from the drop-down list.

8. Click OK, and then go back to step 5.

9. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Send folder, and then select General.

   Figure showing the General page
   

10. On the Static Solicit-Response Send Port Properties - Configurations - Send - General dialog box, do the following.

    Use this	To do this
    Send Pipeline	Select Microsoft.BizTalk.DefaultPipelines.XMLTransmit.
    Receive Pipeline	Select Microsoft.BizTalk.DefaultPipelines.XMLReceive.

11. Click OK.

• Using Windows Explorer, create three folders named Input_folder, Result_folder, and Sample_data in your SQL_Adapter_SP project directory.

1. Right-click Send Ports, and then click Add Send Port.

2. In the Create New Send Port dialog box, select Static One-Way Port, and then click OK.

3. On the Static One-Way Send Port Properties - Configurations - Transport - Primary dialog box, do the following.

   Use this	To do this
   Name	Type MyLoan_Acceptance_Result_Port.
   Transport Type	Select FILE.
   Address (URI)	Click the ellipsis (...) button to open the File Transport Properties dialog box.

4. In the File Transport Properties dialog box, do the following.

   Use this	To do this
   Destination Folder	Type the directory location of the Result_folder, for example C:\Documents and Settings\<username>\My Documents\Visual Studio 2005\Projects\SQL_Adapter_SP\Result_folder.
   File name	Type Accepted_Loan_%MessageID%.xml.
   Copy mode	Select Create New.

5. Click OK.

6. In the left pane, expand the Send folder, and then click General.

7. On the Static One-Way Send Port Properties - Configurations - Send - General dialog box, in the Send Pipeline box, select Microsoft.BizTalk.DefaultPipelines.XMLTransmit and then click OK.

1. Right-click Receive Ports, and then click Add Receive Port.

2. In the Create New Receive Port dialog box, select One-Way Port, and then click OK.

3. On the One-Way Receive Port Properties - Configurations - General dialog box, in the Name box, type MyLoan_Acceptance_Input_Port, accept the defaults, and then click OK.

   The MyLoan_Acceptance_Input_Port receive port now appears in BizTalk Explorer.

4. Expand the new MyLoan_Acceptance_Input_Port node, right-click Receive Locations, and then click Add Receive Location.

5. In the Receive Location Properties - Configurations - General dialog box, do the following.

   Use this	To do this
   Transport Type	Select FILE.
   Address (URI)	Click the ellipsis (...) button and then proceed to step 7.
   Receive Handler	Select the host you bound to the orchestration, BizTalkServerApplication by default.
   Receive Pipeline	Select Microsoft.BizTalk.DefaultPipelines.XMLReceive.

6. Click OK and then continue to To bind the ports to the orchestration.

7. In the File Transport Properties dialog box, in the Receive Folder, type the directory location of the Input_folder, accept the default values, and then click OK. Now return to step 5 to set the receive location.

1. In BizTalk Explorer, expand Orchestrations, and then double-click SQL_Adapter_SP.Orchestration_1.

   Note
   If you do not see SQL_Adapter_SP.Orchestration_1, try refreshing BizTalk Explorer by right clicking BizTalk Configuration Databases, and then clicking Refresh. Note that the BizTalk Management database is also referred to as the BizTalk Configuration database.

2. In the Port Binding Properties – SQL_Adapter_SP.BizTalk_Orchestration_1 – Configurations – Binding dialog box, do the following.

   Use this	To do this
   LoanApplication_in_port	Select MyLoan_Acceptance_Input_Port.
   SPROC_Call_Port	Select MyLoan_Acceptance_SPROC_Port.
   Save_Result_Port	Select MyLoan_Acceptance_Result_Port.

3. In the left pane, click Host.

4. In the Port Binding Properties – SQL_Adapter_SP.BizTalk_Orchestration – Configurations – Host dialog box, select the host for your orchestration, BizTalkServerApplication by default, and then click OK.

1. In BizTalk Explorer, in the Orchestrations node, right-click SP_Adapter_SP.Orchestration_1, and then click Start.

2. In the BizTalk Explorer - Express Start dialog box, accept the defaults, and then click OK.

   When the orchestration starts, the orchestration icon in BizTalk Explorer appears in color.

1. Using Windows Explorer, browse to the <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Sample Data, right-click each file, click Properties, and ensure that the files are not marked read only.

   If Read-only is checked, remove the check.

2. Copy the LoanApplication_sample.xml file from <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Sample Data to SQL_Adapter_SP\Sample_data folder.

3. Right-click LoanApplication_sample.xml and click Edit.

4. In the sample data, change http://Loan_Acceptance.LoanApplication to http://SQL_Adapter_SP/LoanApplication.

1. Copy the LoanApplication_sample.xml file from SQL_Adapter_SP\Sample_data to SQL_Adapter_SP\Input_folder.

2. Notice in the SQLAdapter\Result_folder that the Accepted_loan_{guid}.xml appears when the Loan Acceptance orchestration is complete.

To continue recreating the SQLAdapter sample provided in the SDK, continue to Walkthrough: Using the SQL Adapter with an Updategram in an Orchestration.

Other Resources