A useful BizTalk Sql Query

Found this sql statement on another blog that shows you what ports are still referencing a mapping. This is useful if you need to undeploy the maps and you are having trouble finding what port might be still referencing.

select
'RcvPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_receiveport_transform rt
inner join bts_receiveport r
on rt.nReceivePortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id
--order by Port, nSequence

union

select
'SendPort' PortType,
r.nvcName Port,
item.name MapName,
assem.nvcName Assembly,
nSequence, indoc_docspec_name, outdoc_docspec_name
from bts_sendport_transform rt
inner join bts_sendport r
on rt.nSendPortID = r.nID
inner join bt_mapspec ms
on ms.id = rt.uidTransformGUID
inner join bts_assembly assem
on ms.assemblyid = assem.nID
inner join bts_item item
on ms.itemid = item.id

order by PortType, Port, nSequence

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.

Some tips on BizTalk 2004 to remember.

So here is a list of things that solved some of my errors that I have been having in developing a
BizTalk 2004 application.

1) I was having some trouble in developing maps for 2 of them. I then decided to use an external XSLT instead. I started getting un inforormative errors like unexpected ":" and missing ";" in the btm mapping on line 8 and 9. These errors of course made no sense, since there were no line 8 or 9. I knew the problem had to be in the XSLT. The probelm ended up being that I had to get rid of the Version = 1.0 attribute and a var declaration that I had in the xml:stylesheet declaration. This is what the line should of been:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:userCSharp="urn:userCSharp"
exclude-result-prefixes="msxsl userCSharp">

<xsl:output method="xml"
omit-xml-declaration="yes" /> <xsl:template match="/">

2) When writing scripts that use that same functions over and over again, then make a external assemblies. I also made the assembly version hard coded, so that when I change the scripts I don't have to worry about resetting the maps.