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.

By completing this procedure, you will re-create the Loan Acceptance project provided in the SQL adapter sample. You can install the SQL adapter sample on your computer as part of the SDK Sample Update. After installing the SDK Sample Update, 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 (BizTalk Server Sample). Once installed, the Loan Acceptance project is located in the <drive>:\Program Files\Microsoft BizTalk Server 2004\SDK\Sample\Adapter\SQLAdapter\Loan Acceptance folder.

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

To modify the stored procedure

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.

   

   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 Apply, and then click OK.

To create an empty BizTalk project

1. Click Start, point to Programs, point to Microsoft Visual Studio .NET 2003, and then click Microsoft Visual Studio .NET 2003.
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.

To add metadata to the BizTalk project

1. In Visual Studio .NET, 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, and then click Open.
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 a cell in the Value column twice (in two different locations) to type a value in the Value column, and then do the following.

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

    Click Generate.

    

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

    

    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.

Now go back to SQL Server Enterprise Manager and return the stored procedure to its original state.

To revert the stored procedure

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 Apply, and then click OK.
4. To close Enterprise Manager, on the File menu, click Exit.

To add a required schema to the project

1. In Visual Studio .NET, 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 2004\SDK\Sample\Adapter\SQLAdapter\Loan Acceptance, select LoanApplication.xsd, and then click Open.
3. In Solution Explorer, click LoanApplication.xsd.
4. In the Properties window, change the Namespace value to SQL_Adapter_SP.
5. In Solution Explorer, change the Target Namespace value to http://SQL_Adapter_SP/LoanApplication.

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

To create the first port in the orchestration

1. In Solution Explorer, double-click BizTalk Orcestration.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.

To create new messages

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.

To add shapes to the orchestration

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.

The orchestration should look like the following:

To add links to the generated map

1. In Solution Explorer, double-click the Make_SPROC_argument.btm tab.
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

To build the project

• On the Build menu, click Build Solution.

To create a strong name key file

1. Click Start, point to Programs, point to Microsoft Visual Studio .NET, point to Visual Studio .NET Tools, and then click Visual Studio .NET 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 Assigning a Strong Name.

To deploy the solution

1. In Solution Explorer, right-click Solution 'SQL_Adapter_SP' (1 project), and click Properties.
2. In the left pane, expand Configuration Properties, and then select Configuration.
3. In the right pane, in the Deploy column, select the check box to add this project to your solution, and then click OK.
4. On the Build menu, click Deploy solution.

To create a send port by using BizTalk Explorer

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

   

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.

Create source and destination folders

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

Create a second send port using BizTalk Explorer

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.
   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 Projects\SQL_Adapter_SP\Result_folder.
   File name	Type Accepted_Loan_%MessageID%.xml.
   Copy mode	Type 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 - Transport - Primary dialog box, in the Send Pipeline box, select Microsoft.BizTalk.DefaultPipelines.XMLTransmit.
8. Accept the rest of the default values in the dialog box and then click OK.

To create a receive location using BizTalk Explorer

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

To bind the ports to the orchestration

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.

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.

To start the orchestration

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.

Modify the sample data to work with your project

1. Using Windows Explorer, browse to the C:\Program Files\Microsoft BizTalk Server 2004\SDK\Samples\Adapters\SQLAdapter\Sample Data, right-click each file, click Properties, and 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 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.

To run the SQLAdapter sample

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 Using the SQL Adapter with an Updategram in an Orchestration.

See Also

Using the SQL Adapter

To download updated BizTalk Server 2004 Help from www.microsoft.com, go to http://go.microsoft.com/fwlink/?linkid=20616.

Copyright © 2004 Microsoft Corporation.
All rights reserved.