Before completing this procedure, you must build and initialize the SQL Sample in the BizTalk Server SDK and create the SQL_Adapter_SP project. For more information about the SQL_Adapter_SP project, see Walkthrough: Using the SQL Adapter with a Stored Procedure in an Orchestration. The output of the orchestration in the SQL_Adapter_SP project is the input for the orchestration created in this procedure.

This topic shows you how to do the following:

1. Create an empty BizTalk project.
   
   
2. Add metadata to the BizTalk project and create a SQL adapter port.
   
   
3. Create multiple SQL adapter ports.
   
   
4. Merge orchestrations created by the Add Adapter Wizard.
   
   
5. Modify the UpdateLoanService schema.
   
   
6. Create a map.
   
   
7. Create new messages.
   
   
8. Create an orchestration.
   
   
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. Bind the port to the orchestration.
    
    
14. Start an orchestration.
    
    
15. Run the Loan Assignment project.
    
    

By completing this procedure in this topic, you will re-create the Loan Application project provided in the SQL Adapter Loan Application sample. You must build and initialize the SQL Adapter Loan Application sample to see the Loan Acceptance sample. For more information about installing the SQL Adapter Loan Application sample, see SQL Adapter Loan Application.

Once installed, the Loan Assignment project is located in the <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Loan Assignment folder.

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

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

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

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

   The new SQL_Adapter_Updategram project appears.

1. In Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Existing Item.

2. In the Add Existing Item - SQL_Adapter_Updategram dialog box, navigate to <drive>< <BizTalk installation directory>\SDK\Samples\AdaptersUsage\SQLAdapter\Loan Assignment, select AgentResponse.xsd and then click Open.

3. In Solution Explorer, select AgentResponse.xsd.

4. In the Properties window, change the Type Name value to SQLService_1.

5. In Solution Explorer, double-click AgentResponse.xsd.

6. In the Properties window, change the Target Namespace value to http://Microsoft.Sample.SQLAdapter.

7. In Solution Explorer, right-click AgentResponse.xsd, and then click Open With.

8. In the Open With - AgentResponse.xsd dialog box, select XML Schema Editor.

9. On the AgentResponse.xsd tab, right-click, and then click View XML Source.

10. Change the instance of http://Microsoft.BTS2006.Sample.SQL_Adapter to http://Microsoft.Sample.SQLAdapter.

11. On the File menu, click Save AgentResponse.xsd.

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

2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template section, click Add Adapter, and then click Add.

3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and then 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 BTS2006_SQL_Adapter_loans from the drop-down list.

6. Click OK.

   This sets the connection string for the SQL adapter.

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 UpdateLoanRequest.
   Response root element name	Type UpdateLoanResponse.

   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 Updategram, and then click Next.

11. On the Statement Information page, do the following.

    Use This	To do this
    Select the type of updategram	Select Update.
    Table name	Select Loans.
    Columns to Update	Select all columns.

    Note
    You should select all the columns at this time. Later, you can edit the schema so you have the <before> and <after> blocks you want.

    Note
    The Updategram will have problems if some of the columns have a NULL value (in the XML data, that attribute/element will be omitted). It is best to design the table so that all columns have a default value.

12. Click Next.

13. On the final page of the wizard, click Finish.

The Add Adapter Wizard generated and added two files to your project, UpdateLoansService.xsd and BizTalkOrchestration.odx. UpdateLoansService.xsd is the schema for the Updategram for the table (Loans). BizTalk Orchestration.odx is a blank orchestration with preconfigured port type named UpdateLoansPortType, viewable in the Orchestration View tab in the Types section, in the Port Types node. The orchestration also contains two multi-part message types, UpdateLoansRequest and UpdateLoansResponse, also viewable on the Orchestration View tab in the Multi-part Message Types node. Currently, these message types have red exclamation points in front of them indicating that they are not configured properly. Leave them unconfigured for now. They will be configured later in the procedure.

Figure showing the files added to 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"

Next, you need to add two additional SQL port types to this project using the same Add Adapter Wizard. These new port types use stored procedures. Just as you changed the stored procedure before using the Add Adapter Wizard in "Using the SQL Adapter with a Stored Procedure" topic, you also have to add , xmldata to these stored procedures before running the Add Adapter Wizard again.

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 BTS2006_SQL_Adapter_Loans database.

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

4. In the right pane, right-click SP_monitor_for_Loan_to_Assign 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 end of the if clause so that this statement reads as follows:

   and Loans.LoanID = @LoanID for xml data, xmldata

6. Add the text , xmldata to the end of the else clause so that this statement reads as follows:

   select * from Customers,Loans where 1 = 0 for xml auto, xmldata

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

8. Click the Summary tab to display the list of stored procedures again.

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

10. Add the text , xmldata to the end of the if clause so that this statement reads as follows:

    select * from Customers where CustomerID = @CustomerID for xml auto, xmldata.

11. Add the text , xmldata to the end of the else clause so that this statement reads as follows:

    select * from Customers where 1 = 0 for xml auto, xmldata.

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

    Now that the stored procedures have been modified, you can run the Add Adapter Wizard to create the additional port types and message types.

1. To open the Add Adapter Wizard, in Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Generated Items.

2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template 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 then click Next.

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 BTS2006_SQL_Adapter_loans from the drop-down list.

6. Click OK.

   This sets the connection string for the SQL adapter.

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 Receive Port.
   Document root element name	Type LoanRoot.

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_monitor_for_Loan_to_Assign from the drop-down list.

12. Click Generate to generate the EXEC statement in the Generated Script box, and then click Next.

    If you receive an error that the SQL statement is incorrect you did not modify the stored procedure correctly.

13. Click Finish to complete the wizard.

The Add Adapter Wizard generated and added two files to your project, SQLService.xsd and BizTalkOrchestration_1.odx. SQLService.xsd is the schema for the SP_monitor_for_Loan_to_assign stored procedure. BizTalk Orchestration_1.odx is a blank orchestration with preconfigured port type named SQLServiceExec, viewable in the Orchestration View tab, in the Types section, in the Port Types node. The orchestration also contains a multi-part message type named procedureRequest, also viewable on the Orchestration View tab in the Multi-part Message Types node.

1. To open the Add Adapter Wizard, in Solution Explorer, right-click SQL_Adapter_Updategram, point to Add, and then click Add Generated Items.

2. In the Add Generated Items – SQL_Adapter_Updategram dialog box, in the Template section, click Add Adapter, and then click Add.

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

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 BTS2006_SQL_Adapter_loans from the drop-down list.

6. Click OK.

   This sets the connection string for the SQL adapter.

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 SPROCRequest.
   Response root element name	Type SPROCResponse.

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_Monitor_for_New_Customers from the drop-down list.

12. Click Generate to generate the EXEC statement in the Generated Script box, and then click Next.

    If you receive an error that the SQL statement is incorrect you did not modify the stored procedure correctly.

13. Click Finish to complete the wizard.

The Add Adapter Wizard generated and added two files to your project, SQLService_1.xsd and BizTalkOrchestration_2.odx. SQLService_1.xsd is the schema for the SP_monitor_for_new_customer stored procedure. BizTalk Orchestration_2.odx is a blank orchestration with preconfigured port type named SQLServiceExec, viewable in the Orchestration View tab in the Types section, in the Port Types node. The orchestration also contains two multi-part message types, procedureResponse and procedureRequest, also viewable on the Orchestration View tab in the Multi-part Message Types node.

Now that you are done using the Add Adapter Wizard, you need to remove the , xmldata that you added to the stored procedures.

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

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

3. Remove the text , xmldata from the end of the if clause so that this statement reads as follows:

   and Loans.LoanID = @LoanID for xml data

4. Remove the text , xmldata from the end of the else clause so that this statement reads as follows:

   select * from Customers,Loans where 1 = 0 for xml auto

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

6. Click the Summary tab to display the list of stored procedures again.

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

8. Remove the text , xmldata from the end of the if clause so that this statement reads as follows:

   select * from Customers where CustomerID = @CustomerID for xml auto

9. Remove the text , xmldata from the end of the else clause so that this statement reads as follows:

   select * from Customers where 1 = 0 for xml auto

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

Next, you will merge all three of the orchestrations into one orchestration. Each time you run the Add Adapter Wizard, it creates a new BizTalk orchestration file. Because you have now run the wizard three times, you should have three orchestration files: BizTalk Orchestration.xsd, BizTalk Orchestration_1.xsd, and BizTalk Orchestration_2.xsd.

1. In Visual Studio, in Solution Explorer, double-click Orchestration_1.odx.

2. On the Orchestration View tab, in the Types section, in the Port Types node, right-click SQLServiceExec and click Copy.

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

4. On the Orchestration View tab, in the Types section, right-click the Port Types node, and then click Paste to add the port type to the combined orchestration.

5. In the Properties window, in the Identifier value box, type SQLServiceExec1.

6. In Solution Explorer, double-click Orchestration_1.odx.

7. On the Orchestration View tab, in the Types section, in the Multi-part Message Types node, right-click procedureRequest and click Copy.

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

9. On the Orchestration View tab, in the Types section, right-click the Multi-part Message Types node, and then click Paste to add the multi-part message type to the combined orchestration.

10. In the Properties window, in the Identifier value box, type procedureRequest1.

11. In Solution Explorer, right-click BizTalk Orchestration_1.odx, and then click Exclude From Project.

    Note
    If you make an error and need to copy and paste the port type or message type again, you can add the orchestration file back to the project using the Add Existing Item option on the Project menu.

12. Repeat the copy and paste operations for the SQLServiceExec port type, procedureRequest and procedureResponse message types in BizTalk Orchestration_2.odx file. The following table provides the new names for each item.

    Item	Previous name	New name
    Port Type	SQLServiceExec	SPROCServiceExec
    Multi-part Message Type	procedureRequest	SPROCRequest
    Multi-part Message Type	procedureResponse	SPROCResponse

13. In Solution Explorer, right-click BizTalk Orchestration_2.odx, and then click Exclude From Project.

14. In Solution Explorer, right-click SQLService_1.xsd, and then click Exclude From Project.

1. In Orchestration View, expand Port Types, expand SQLServiceExec1, expand procedure, and then expand Request.

2. In the Properties window, change the Message Type to SQL_Adapter_Updategram.procedureRequest1.

3. In Orchestration View, expand SPROCServiceExec, procedure, Request.

4. In the Properties window, change the Message Type value to SQL_Adapter_Updategram.SPROCRequest.

5. In Orchestration View, expand SPROCServiceExec, procedure, Response.

6. In the Properties window, change the Message Type value to SQL_Adapter_Updategram.SPROCResponse.

7. In Orchestraion View, expand Multi-Part Message Types, SPROCRequest, parameters.

8. In the Properties window, change the Type to SQL_Server_Updategram.AgentResponse.AgentRequest.

9. In Orchestraion View, expand Multi-Part Message Types, expand SPROCResponse, and then expand parameters.

10. In the Properties window, change the Type to SQL_Server_Updategram.AgentResponse.AgentResponse.

11. In Orchestraion View, expand Multi-Part Message Types, expand UpdateLoansRequest, and then expand parameters.

12. In the Properties window, change the Type to SQL_Server_Updategram.UpdateLoansService.MyRequestRoot.

If any of the Port Types or Multi-part Message Types still have a red exclamation point in front of them, refer to the BizTalk Orchestration.odx file in the Loan Assignment project located at drive:\Program Files\Microsoft BizTalk Server 2009\SDK\Sample\Adapter\SQLAdapter\Loan Assignment for the correct properties and modify your orchestration appropriately.

1. In Solution Explorer, double-click UpdateLoanService.xsd.

2. Expand all of the nodes in the schema. Your schema should look like the one in the following figure:

   Figure showing the initial schema
   

3. From the before node, select all of the columns except the LoanID column, and delete them.

4. From the after node, select and delete the CustomerID and RequestAmt column.

   Your schema should now look like the one in the following figure:

   Figure showing the final schema
   

   All columns are deleted from the before node except for the key column.

5. Select the sync node, and then in the Properties window, do the following.

   Use this	To do this
   Max Occurs	Change the value to 1.
   Min Occurs	Ensure that the value is 1.

6. Select the after node in the schema.

7. In the Properties window, do the following.

   Use this	To do this
   Max Occurs	Change the value to 1.
   Min Occurs	Change the value to 1.

8. Select the Loans node located under the after node in the schema.

9. In the Properties window, do the following.

   Use this	To do this
   Max Occurs	Change the value to 1.
   Min Occurs	Change the value to 1.

10. To make some of the fields distinguished, right-click the Schema node, point to Promote, and then click Show Promotions.

11. In the Promote Properties dialog box, in the left pane, expand Schema, expand MyRequestRoot, expand sync, expand after, and then expand Loans.

12. In the left pane, select AgentID, and then click Add.

13. Repeat step 12 for Interest, and Status, and then click OK.

14. On the File menu, click Save UpdateLoanService.xsd.

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 Assignable_loan_msg.
   Message Type	Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.procedureRequest1.

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 UpdategramInputMsg.
   Message Type	Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.UpdateLoansRequest.

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 UpdategramResultMsg.
   Message Type	Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.UpdateLoansResponse.

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

9. In the Properties window, do the following.

   Use this	To do this
   Identifier	Type AgentInterestSPROCInputMsg.
   Message Type	Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram. SPROCRequest.

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

11. In the Properties window, do the following.

    Use this	To do this
    Identifier	Type AgentInterestSPROCResultMsg.
    Message Type	Expand the Multi-part Message Types node, and then select SQL_Adapter_Updategram.SPROCResponse.

1. In Solution Explorer, double-click BizTalk Orchestration.odx to open the orchestration in the Visual Studio design surface.

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 UpdateLoanAssignment_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 SQLAdapterUpdategram.UpdateLoansTypePort.

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 Specify Later (Late Binding) on SQL adapter ports.

9. On the Completing the Port Wizard page, click Finish to complete and close the wizard.

10. Add additional shapes and ports to the orchestration by dragging the following shapes onto the orchestration design surface, and then 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: Get_Assignable_loan Message: Assignable_loan_msg Activate: True
    Construct Message	Below the Get_assignable_loan shape	Name: Form_SPROC_Arguments Messages Constructed: AgentIntrestSPROCInputMsg
    Transform	Inside the Form_SPROC_Arguments shape	Name: Transform_SPROC_Input 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 Assignable_loan_msg.parameters as the Variable Name. In the Transform Configuration dialog box, in the left pane, select Destination, and then in the right pane, select AgentInterestSPROCInputMsg.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 Form_SPROC_Arguments shape	Name: Call_SPROC Message: AgentInterestSPROCInputMsg Leave the Operation property blank. A red exclamation point appears on the shape in the orchestration indicating that this property must be set.
    Receive	Below the Call_SPROC shape	Name: Get_SPROC_Result Message: AgentInterestSPROCResultMsg Again, leave the Operation property blank.
    Construct Message	Below the Save_Result shape	Name: Form_Updategram Message Constructed: UpdategramInputMsg
    Transform	Inside the Form_updategram shape	Name: Transform_Updategram 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 Assignable_loan_msg.parameters as the Variable Name. In the Transform Configuration dialog box, in the left pane, select Destination, and then in the right pane, select UpdategramInputMsg.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.
    Message Assignment	Inside the Form_Updategram shape, below the Transform_Updategram shape	Name: Set_Agent_and_Interest Expression: Click the ellipsis (…) button. In the BizTalk Expression Editor dialog box, type the following: UpdategramInputMsg.parameters.sync.after.Loans.AgentID = AgentInterestSPROCResultMsg.parameters.row.AgentID; UpdategramInputMsg.parameters.sync.after.Loans.Interest = AgentInterestSPROCResultMsg.parameters.row.InterestRate; UpdategramInputMsg.parameters.sync.after.Loans.Status = "Assigned"; Click OK.
    Send	Below the Set_Agent_and_Interest shape	Name: Send_Updategram Message: UpdategramInputMsg Leave the Operation property blank.
    Receive	Below the Send_Updategram shape	Name: Get_Updategram_Result Message: UpdategramResultMsg Leave the Operation property blank.
    Send	Below the Get_UpdateGram_Result	Name: Save_Results_to_File Message: UpdategramResultMsg Leave the Operation property blank.
    Port	On the left Port Surface, next to the Get_Assignable_loan shape	Name: ReceiveAssignableLoan_port Use an existing Port Type Available Port Types: SQL_Adapter_Updategram.SQLServiceExec1 Port Direction of communication: I'll always be receiving messages on this port. Port binding: Specify later.
    Port	On the right Port Surface, next to the Call_SPROC shape	Name: CallLoanAssignmentSPROC_port Use an existing Port Type Available Port Types: SQL_Adapter_Updategram.SPROCServiceExec Port direction of communication: I'll be sending a request and receiving a response. Port binding: Specify later.
    Port	On the left Port Surface, next to the Save_Results_to_File shape	Name: Save_LoanAssignment_Port Create a new Port Type Port Type name: PortType_SaveLoanAssignment Communication Pattern: One-Way Access Restrictions: Internal Port direction of communication: I'll always be sending messages on this port. Port binding: Specify later

11. On the right port surface, drag the UpdateLoanAssignment_port next to the SendUpdategram shape.

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

    Port	Name	Connect To
    ReceiveAssignableLoan_Port	Request	Get_Assignable_loan
    Call_LoanAssignmentSPROC_Port	Request	Call_SPROC
    Call_LoanAssignmentSPROC_Port	Response	Get_SPROC_result
    UpdateLoanAssignment_port	Request	Send_Updategram
    UpdateLoanAssignment_port	Response	Get_Updategram
    SaveLoanAssignment_port	Request	Save_Results_to_File

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

2. 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
   LoanID	LoanID (before) and LoanID (after)
   AgentID	AgentID
   Interest	Interest
   Status	Status

3. In Solution Explorer, double-click Transform_SPROC_Input.btm.

4. 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
   State	State
   Rating	Rating

1. On the File menu, click Save All.

2. On the Build menu, click Build SQL_Adapter_Updategram.

   If you receive error BEC2017, you did not change the Type Name of the AgentResponse.xsd file to SQLService_1. Change the type name and rebuild.

1. Click Start, point to Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools. Right-click Visual Studio 2008 Command Prompt and then select Run as administrator.

2. At the command prompt, browse to the folder where your project resides, select Loan Assignment.dll, on the command line, type sn -k SQL_Adapter_Updategram.snk, and then press ENTER.

   You should receive a message at the command prompt stating that the key pair has been written to SQL_Adapter_Updategram.snk.

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

• In Solution Explorer, right click SQL_Adapter_Updategram and click Deploy.

1. On the View menu, click BizTalk Explorer.

   You may have to right-click the root node and select Refresh to ensure that you are seeing the latest data.

2. In BizTalk Explorer, expand BizTalk Configuration Databases, <BizTalk 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_Assignment_SPROC_Port.
   Transport Type	Select SQL.
   Address (URI)	Click the ellipsis (...) 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 (...) 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 AgentResponse.

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 BTS2006_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 click General, as shown in the following figure.

   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.

12. Repeat steps 1 through 11 to create another send port, except in step 4 name the send port MyLoan_Assignment_Updategram_Port, and set the Response Root Element Name to UpdateLoanResponse.

1. In BizTalk Explorer, expand BizTalk Configuration Databases, <BizTalk 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.

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

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

   Use this	To do this
   Name	Type MyLoan_Assignment_Result_Port.
   Transport Type	Select FILE.
   Address (URI)	Click the ellipsis (...) 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 2008\Projects\SQL_Adapter_SP\Result_folder.
   File name	Type Assigned_Loan_%MessageID%.xml.
   Copy mode	Type Create New.

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

6. 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 BTS2006_SQL_Adapter_loans from the drop-down list.

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

8. On the Static Solicit-Response Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Send folder, and then click General, as shown in the following figure.

9. 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.

10. 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_Assignment_Receive_Port, accept the defaults, and then click OK.

   The MyLoan_Assignment_Receive_Port receive port now appears in BizTalk Explorer.

4. Expand the new MyLoan_Assignment_Receive_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
   Name	Type MyLoan_Assignment_SQLReceive.
   Transport Type	Select SQL.
   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 SQL Transport Properties dialog box, do the following:

   Use this	To do this
   Poll While Data Found	Ensure the value is False.
   Polling Interval	Ensure the value is 30.
   Polling Unit of Measure	Ensure the value is Seconds.
   Connection String	Click the ellipsis (...) button to open the Data Link Properties dialog box, and skip to step 9.
   Document Root Element Name	Type LoanRoot.
   Document Target Namespace	Type http://Microsoft.Sample.SQLAdapter.
   SQL Command	Click the ellipsis (...) to open the Import information from a generated schema dialog box, and skip to step 11.
   Address (URI)	Change the value to SQL://localhost/BTS2006_SQL_Adapter_Loans/02.

   Note
   If necessary, you can edit the SQL Command field. You may charge arguments or add a WHERE clause, but it is highly recommended to not modify the returned format of the command.

   Note
   Since this is the only place that BizTalk stores the SQL command (a SELECT or EXEC command), using a stored procedure is preferable for complex SELECT operation. You can debug and test the stored procedure through SQL Query Analyzer first without having to use BizTalk Server.

   Note
   The Polling Interval property controls the speed of the SQL command. This forces the maximum document speed to 60 messages per minute. Consider this when designing the system.

   Note
   The Poll while Data Found property controls what happens the SQL command returns an empty table. The default setting, False, does not issue messages for empty returns. This is the preferred mode to avoid unnecessary messages.

8. Click OK, and then proceed to step 13.

9. In the Data Link 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 BTS2006_SQL_Adapter_loans from the drop-down list.

10. Click OK, and then go back to step 7 to configure the Document Root Element Name.

11. On the Import information from a generated schema dialog box, do the following:

    Use this	To do this
    Project	Select SQL_Adapter_Updategram.
    Schema	Select SQL_Adapter_Updategram_New.SQLService,SQL_Adapter_Updategram_New, Version=1.0.0.0.

12. Click OK, and then go back to step 7 to configure the URI.

    Note
    If necessary, you can edit the SQL Command field. You may charge arguments or add a WHERE clause, but it is highly recommended to not modify the returned format of the command.

    Note
    Since this is the only place that BizTalk stores the SQL command (a SELECT or EXEC command), using a stored procedure is preferable for complex SELECT operation. You can debug and test the stored procedure through SQL Server Management Studio first without having to use BizTalk Server.

    Note
    The Polling Interval property controls the speed of the SQL command. This forces the maximum document speed to 60 messages per minute. Consider this when designing the system.

    Note
    The Poll while Data Found property controls what happens the SQL command returns an empty table. The default setting, False, does not issue messages for empty returns. This is the preferred mode to avoid unnecessary messages.

13. On the Receive Location Properties – Configurations – General dialog box, do the following:

    Use this	To do this
    Receive Handler	Select BizTalkServerApplication.
    Receive Pipeline	Select Microsoft.BizTalk.DefaultPipelines.XMLReceive (Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35).

14. Click OK.

1. In BizTalk Explorer, expand Orchestrations, right-click SQL_Apapter_Updategram.BizTalk Orchestration_1, and then click Bind.

2. In the Port Binding Properties – SQL_Adapter_Updategram.BizTalk_Orchestration – Configurations – Binding dialog box, do the following:

   Use this	To do this
   ReceiveAssignableLoan_port	Select MyLoan_Assignment_Receive_Port.
   CallLoanAssignmentSPROC_port	Select MyLoan_Assignment_SPROC_Port.
   UpdateLoanAssignment_port	Select MyLoan_Assignment_Updategram_Port.
   Save_LoanAssignment_port	Select MyLoan_Assignment_Result_Port.

3. In the left pane, click Host.

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

1. In BizTalk Explorer, expand the Orchestrations node, and then right-click SQL_Adapter_Updategram.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.

• The Loan Assignment.BizTalk Orchestration.odx file picks up the output of the orchestration, which assigns the loan to an agent, if you tested the orchestration created in the Walkthrough: Using the SQL Adapter with a Stored Procedure in an Orchestration. To verify that the orchestration assigned the loan to an agent, view the Loans table of the BTS2006_SQL_Adapter_loans database.

Other Resources