BizTalk Development Series (25) SQL Adapter

For more information, see the BizTalk hands-on experiment series directory.

BizTalk Development Series

SQL Server is the preferred database for. NET development. Of course, the development of many BizTalk applications cannot leave SQL Server. BizTalk for SQL Server Data Operations provides the SQL Adapter as an interface for interacting with the database. This is a basic article, part of which is taken from the BizTalk help document. This article mainly introduces the knowledge that needs to be learned during the use of SQL Adapter. If you are familiar with BizTalk development, please add.

The SQL adapter consists of the receiving and sending adapters. The SQL receiving adapter is the polling adapter that regularly polls the SQL result set. SQL commands are stored in the configuration PROPERTIES OF THE RECEIVING LOCATION. You can use the adapter architecture to generate wizard calls. The SQL receiving adapter allows you to call SELECT statements and stored procedures that return a single XML data format result set each time you run the command.

The SQL sending adapter can send dynamically created Updategram or dynamically called stored procedures to SQL Server. Updategram is an XML segment used to map XML nodes based on database tables and columns to insert, update, or delete data in the SQL Server database. After Updategram is complete, SQL Server Returns an optional Response document that contains status information to indicate whether the update is successful. If an error occurs during the update, the SQL adapter will cause an exception handled by the BizTalk message engine. If you configure the SQL sending adapter to call a stored procedure, the adapter returns all results in the form of a record set in a single XML format.

 

SQL architecture generation wizard

Create a BizTalk project and add a generated project.

Select SQL from the list of generated adapters.

In the database connection attribute settings. If you are using a database account to log on, select Allow saving password. Otherwise, you cannot use the wizard.

Enter the namespace in the target namespace in the schema information, and select the receiving or sending port from the port type. The sending port needs to enter the name of the root node for the returned message.

In the input SQL script, you need to use for xml auto or for xml auto. elements returns the result in the form of an XML record set.

You can also select a stored procedure. In the Stored Procedure List, select the stored procedure name and assign values to the stored parameters in the table. You must be patient when assigning values to parameters. Otherwise, NULL is selected accidentally.

The wizard Builder automatically generates a Schema. An Orchestration with this Schema type is also generated. The Schema type used in Orchestration is no different from other Schemas (custom or generated. I will not introduce it here.

　 　　　　

 

Use Updategram

 By using updategram, you can modify (insert, update, or delete) databases from existing XML documents in Microsoft SQL Server. For more information about Updategram, see Updategram introduction (SQLXML 4.0)

All Updategram instances have the same basic structure:

<InboundRootElementName>
<Sync>
<Before>
<TableName col1 = 'value' col2 = 'value'/>
</Before>
<After>
<TableName col1 = 'value' col2 = 'value'/>
</After>
</Sync>
</InboundRootElementName>

 

The following definitions describe the roles of each block:

• <Before>. Identifies the existing status of the Instance (also known as the "previous status") and acts as the WHERE clause in the SQL statement.
• <After>. Identifies the new State in which data is to be changed.
• <Sync>. Contains <before> blocks and <after> blocks. <Sync> A block can contain multiple groups of <before> blocks and <after> blocks. If multiple groups of <before> blocks and <after> blocks exist, you must specify them in pairs (even if they are empty ). In addition, Updategram can have multiple <sync> blocks. Each <sync> block is a transaction unit (this means that all items in <sync> are running or none are running ). If multiple <sync> blocks are specified in Updategram, one <sync> block error does not affect other <sync> blocks.

Whether Updategram deletes, inserts, or updates the record instance depends on the content of the <before> block and <after> block:

• If the record instance only appears in the <before> block but does not have the corresponding instance in the <after> block, Updategram deletes the instance.
• If the record instance only appears in the <after> block but does not have the corresponding instance in the <before> block, the insert operation is performed.
• If the record instance appears in the <before> block and a corresponding instance exists in the <after> block, update the instance. In this case, the value specified in the <after> block updates the corresponding record instance.

Use the Add generated item Wizard to select the operation to be performed on the table and generate the architecture that only supports this operation. For example, if you select insert, only the <before> block on Updategram is displayed in the architecture. To generate a multi-purpose architecture that can be used for any of the preceding operations, select the update operation for all columns from the "columns to be updated" list. Then, you can select the block to be created when creating an XML instance based on this architecture.

The Updategram XML architecture generated by adding the generated item.

 

Configure the SQL receiving Adapter

The SQL receiving adapter can only be bound to one-way receiving port. Note the following When configuring the SQL Transmission Properties dialog box: the root node and target namespace of the document must be consistent with the generated Schema. Of course, you can also tune the namespace and root node in the Schema to be consistent with the configuration of the Adapter and re-deploy the project. You can configure the connection string through data link properties. SQL commands can be generated based on the existing Schema or manually input SQL commands or stored procedures.

The Poll While Data Found attribute in the receiving configuration specifies whether to submit other batches after the stored procedure or query does not return any results, or whether to submit a stored procedure result or query result at each polling interval. The default value is False. It indicates that a stored procedure result or query result is submitted at each polling interval. Polling Interval specifies the number of units between Polling requests. Polling Unit of Measure specifies the measurement units used between Polling requests.

　

Finally, we will introduce the URI. If the configured SQL receiving port URI address is the same as the existing one receiving port configuration of BizTalk, the Port Configuration Wizard will prompt that this location has been used. However, if you must use this position, you can manually add an I id after UR. For example: SQL: //./pubs/Next

 

Configure the SQL sending Adapter

The SQL sending adapter is easy to configure and can be bound to one-way or two-way ports. Pay attention to the consistency between the namespace, root node, and Schema.

 

Best practices for SQL adapters

1. The stored procedure is preferred. You can also use updategram for occasional operations.

2. Avoid competition from resubmitting the same data in the receiving end. It is best to set a Status field in the table to prevent SQL operations from sending the same information to the BizTalk Server during long execution of business processes.

3. Use an envelope message to return multiple rows each time. The SQL adapter can receive only 60 messages per minute.

4. If the message is greater than 1 MB, reduce the number of rows returned from SQL Server to keep the message at 1 Smaller than MB. We recommend that you set the continuous polling attribute to True so that the adapter can only submit the remaining rows in a separate batch. The adapter submits a batch until the SQL Server computer returns an empty result set. At this time, the SQL adapter releases the thread until the next polling interval.

 

Known SQL adapter Problems and Solutions

1. The receiving function returns an empty document.
If a SELECT statement or stored procedure generates an empty result set, the documents returned by the SQL adapter receiving function only include the root and target namespaces.

This problem occurs if the SELECT statement or stored procedure contains the xmldata keyword.

To solve this problem, delete the SELECT statement in the SQL adapter endpoint configuration or the xmldata keyword in the stored procedure.

2. An error occurred while sending updategram to the SQL adapter.
When sending an updategram request to an SQL adapter, an error message "syntax error occurs during conversion from string to datetime" may occur ".

This problem occurs if the SQL table contains a date and time column and updategram tries to update the column with an incorrect value.

To solve this problem, do not use the BizTalk er functoid to create a date and time value mapped to updategram. The date and time values created by these functoids are in the format of ": 20: 00.000 ". The format of the date and time values required for the SQL datetime column is ": 20: 00.000 ". You can manually create a date and time value by calling the Parse or ParseExact method of the DateTime class, instead of using the default date and time functoid.

3. Use the money data type column in updategram
When an updategram architecture is generated by adding an SQL adapter wizard, The Wizard will represent the money data type column as an XSD type decimal in the schema.

This problem occurs because SQLXML requires updategram to pass the money column in "$123.45" format.

To use the money column in updategram, change the attributes of the money column in The updategram architecture from the xs: decimal type to the xs: string type. When you map data to this column, remember to include the leading dollar sign.

4. Compilation error: Invalid 'name' Attribute Value
When a project is generated, the compilation error "'name' attribute value is invalid: The name cannot start with'' (hexadecimal value

This problem occurs if the SELECT statement or stored procedure encounters an invalid character (as described in the System. Xml. XmlConvert class) to be imported to the Add adapter wizard.

To correct this error, modify the SELECT statement defined in the Add adapter wizard or the table name or column name listed in the stored procedure so that these names include System. xml. the valid value defined by the XmlConvert class. For more information about characters supported by the XmlConvert class, see the XmlConvert class in the. NET Framework documentation.

5. columns are missing from the generated XML schema.
Columns are missing from the XML schema generated by the Add adapter wizard.

This problem occurs if the Add adapter wizard does not match the XSD data type.

To solve this problem, change the column type SQL _Variant TO THE supported data type.

6. the SQL transmission architecture generation wizard is accidentally disabled in BizTalk Server.
Problem: In BizTalk Server 2006, the SQL transmission architecture generation wizard unexpectedly closes when an attempt is made to generate an architecture for the SQL adapter sending port. This problem occurs when you try to generate a schema for one of the following updategram to update the database tables in SQL Server 2005:

Insert updategram
Update updategram
Delete updategram
In addition, you may receive an error message similar to the following:

Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. New transaction cannot enlist in the specified transaction coordinator.

This error message may be received when you use the SQL transmission architecture generation wizard in SQL Server 2005 to query database tables.

Cause:

This problem occurs when the database table contains columns of the following data types:

Varbinary (MAX) Data Type
Varchar (MAX) Data Type
Nvarchar (MAX) Data Type
Xml Data Type
In BizTalk Server, the SQL adapter does not support these data types.

Solution:

These data types are newly added in SQL Server 2005. When a database table contains one of the data types, do not use the SQL adapter in BizTalk Server to insert data to the database table. In addition, do not use the SQL adapter in BizTalk Server to retrieve data from database tables.

 

 

 

References Microsoft BizTalk Server 2006 Documentation