Given a table, EventLog, the poll SQL statement that the TableChangeEvent uses to get the rows from that table is: SELECT * from EventLog. If the Delete After Poll property is enabled, the -post poll statement for that is: DELETE from EventLog.
A receive location, when enabled, polls at the specified interval, regardless if there is an orchestration to process the message. If there is no orchestration processing the message BizTalk Server returns an error and the adapter does not perform the post poll statement. The post poll statement is only executed if there is no error sending the message to BizTalk Server.
.gif) Note |
|---|
|
The statements are not used if there are no orchestrations for the SQLEvent method.
|
The statement for the SQLEvent can be any valid SQL statement:
-
A SELECT/UPDATE/DELETE/INSERT.
-
A call to a procedure.
-
Many statements.
| Note |
|---|
|
Multiple statements do not work with Oracle (and other databases might prevent this as well). Oracle prevents what is called SQL injection, a mechanism that a hacker would use to get the database to run code within the database.
|
| Note |
|---|
|
MS-SQL is one database that allows many statements.
|
-
Create a table.
The result of the SQLEvent is the same as the SQLExecute. A poll statement is similar to the SQLExecute statement (without the parameterized statements). The post poll statement is executed on successful notification to BizTalk Server.
The SQL statement executes if the statement is a valid SQL statement and the permissions are correct in the database.
For example:
Poll statement: SELECT * from EventLog where hasPolled=0
Post poll statement: UPDATE EventLog set hasPolled=1 where hasPolled=0
See Performing SQL Queries on Databases for the structure of the returned data from the poll statement. The data is received in BizTalk Server and the integrator can manipulate it within an orchestration. The post poll statement logs errors and warnings; it does not return anything.
The poll returns all columns of all rows that have hasPolled=0. After sending the data to Biztalk Server, it sets the hasPolled to 1 for all the same rows. This excludes the same rows from being sent a second time.
| Note |
|---|
|
The UPDATE statement includes the where clause so that the UPDATE applies only to the rows picked out by the SELECT statement.
|
It is important to note that the execution of the poll statement and post poll statement is performed within a transaction of serializable isolation level. Records or changes that were not visible when executing the poll statement are also not visible when the post poll statement executes. This may reduce throughput on some systems, but it guarantees that the above-mentioned example only affects the data that was sent to BizTalk Server with the poll statement.
Example of a ResultSet Returned From Select SQL Statement
The following code example is the resultset returned from a call to select top 5 * from eventlog.
The *.XML return looks like:
<?xml version="1.0" encoding="utf-8" ?>
- <NativeSQL:SQLExecuteResponse
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:exposed="http://Microsoft.com/exposed"
xmlns:NativeSQL="http://Microsoft.com/exposed/sql_ssw/NativeSQL">
- <NativeSQL:Return>
- <NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>EventLogKey</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>RequestId</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>Severity</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>TimeStamp</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>ProcessName</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>ProcessId</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>ThreadName</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>ThreadId</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>SubSystemId</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
- <NativeSQL:columnMetadata>
<exposed:colName>LogData</exposed:colName>
<exposed:colType>string</exposed:colType>
</NativeSQL:columnMetadata>
</NativeSQL:columnMetadata>
- <NativeSQL:rowData>
- <NativeSQL:columnData>
<NativeSQL:string>27974389</NativeSQL:string>
<NativeSQL:string>0</NativeSQL:string>
<NativeSQL:string>Debug</NativeSQL:string>
<NativeSQL:string>2005-02-07 11:33:54.000</NativeSQL:string>
<NativeSQL:string>4148</NativeSQL:string>
<NativeSQL:string>main</NativeSQL:string>
<NativeSQL:string xsi_2001:nil="1"
xmlns:xsi_2001="http://
www.w3.org/2001/XMLSchema-instance" />
<NativeSQL:string>ENGINE</NativeSQL:string>
<NativeSQL:string>Starting LoggingAgent</NativeSQL:string>
</NativeSQL:columnData>
- <NativeSQL:columnData>
- <NativeSQL:columnData>
- <NativeSQL:columnData>
</NativeSQL:rowData>
</NativeSQL:Return>
</NativeSQL:SQLExecuteResponse>
See Also