The best way to use multiple SQL-functions?

Hello

Using BizTalk Server 2006. I need to get data from database with SQL-function. There is many SQL-functions, first function’s output value is the next one’s input value. I have to use SQL-functions, but if it’s not possible to use them with BizTalk, I will make stored procedure to call the SQL-function. The SQL server is Microsoft SQL 2000 (no, there will be not 2005 at this point).

1. Is it possible to make this kind of chain of multiple database calls?
2. What is the best way to do it?

I tried the default SQL adapter but it seems that it’s not possible to use SQL-functions with it. Secondly, it seems that it requires DTC (=MSDTC) to be on and working with both servers. I found DTC ping tool from Microsoft and it requires that the server’s netbios name is working. This could be a problem, because the BizTalk-application will be installed on many servers all over the world and it could be pain in the a** to get every server’s DTC and Netbios settings right.

I also discovered that with default SQL adapter schema files will be needed to generate FOR XML, AUTO statement first. There seems to be strange 256 character limit in SQL Server 2000 with xml… or should this be possible with SQL Server 2000?

Is it possible to specify SQL-server connection string in a configuration file with default SQL adapter? It’s possible with database functoid to use .UDL files, but is it with the adapter?

I’m very close to decision that I will make .dll with C# .NET, use Scripting functoid to use it and make all SQL-calls from there. This way I can also define my own .xml setting file for the connection string etc.. But if all my requirements would be possible to achieve with default BizTalk functionality, that would be great.

Any ideas? I’m quite new to BizTalk. It seems to be very tricky system (most of the time I have been just banging my head to the monitor).