In past I written posts on interaction between BizTalk and Windows Azure SQL Database (former known under SQL Azure) using the WCF-SQL Adapter. I have revisited one of my posts that dealt with calling a stored procedure. To bring back old memories I tried to do same against using BizTalk Server 2013 (this time through using an orchestration and messaging)
The WCF-SQL Adapter (binding) from the BizTalk adapter pack has the following capabilities:
- Execute create-read-update-delete statements on tables and views;
- execute stored procedures and generic T-SQL statements
- Database polling via FOR XML
- Database polling via traditional tabular results
- Proactive database push via SQL Query Notification
- Expansive adapter configuration which impacts connection management and transaction behavior
- Support for composite transactions which allow aggregation of operations across tables or procedures into a single atomic transaction
- Rich metadata browsing and retrieval for finding and selecting database operations
- Support for the latest data types (e.g. XML) and SQL Server 2008, 2008 R2, 2012 platform, Windows Azure SQL Database
- Reusable outside of BizTalk applications by WCF or basic HTTP clients
- Adapter extension and configuration through out of the box WCF components or custom WCF behaviors
- Dynamic WSDL generation which always reflects current state of the system instead of fixed contract which always requires explicit updates
Depending on the version of BizTalk Server and its subsequent adapter pack the supported SQL Server versions range from 2005 to 2012. Beside the on premise SQL Server versions the adapter also supports connectivity with Windows Azure SQL Database. In the Wiki article the execution of a stored procedure in Windows Azure SQL Database will be discussed. . I have written my experience down in the TechNet Wiki article and posted the code in MSDN Gallery:
- BizTalk Server 2013: WCF-SQL executing a stored procedure in Windows Azure SQL Database
- MSDN Gallery: WCF-SQL executing a stored procedure in Windows Azure SQL Database
Cheers,
Steef-Jan
