About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Saturday, November 18, 2006

SQL Server Adapters for BizTalk

Ok, so in my recent project I have multiple calls to make to a sql server's database. I ran into a problem calling the same database, but to get around that you simply add a unique ID to the end of the URI. It then would look something like: SQL://localhost/database_name/uniqueID

I got that information from the MSDN website.

Some FYI and Tips:

1) Have SQL Server do most of the work, with stored procedures when it comes to a large amount of updating.
2) Use some kind of status update when grabing data from a table to prevent multiple pickups with BizTalk. Use something like a date field or some type of varchar naming.
3) Use "select top 1" to make the document only have one root element.
4) SQL Adapters are limited to 60 messages a minute, unless you poll while data is found.
5) Try to keep the message size to under 1MB.
6) Save Tran and Rollback Tran will cause an error in the SQL Adapter
7) The generated root element is made in the adapter, not the store procedure.

No comments: