I'm having a problem trying to use SQL 2008 Profiler to trace a BizTalk 2006 orchestration. I'm trying to get the name and parameters of a stored procedure being called by an orchestration, but the profiler gags on the stored procedure execution. SQL Profiler shows the error message returned, but not the procedure call.

The BizTalk administrator got me a copy of the offending file, and I think that the size (70 Mb) was what caused the TDS execution of the procedure to fail, but I need more insight to prove that.

Can anyone give me any insight into how to trace something that SQL Profiler can't handle or how to configure SQL Server so that it can process an XML file of this size?

-PatP

Do you get any benefit from the other columns? Suppose you remove the textdata column from the output. What are you left with, then? You may still be able to filter based on part of the textdata, but I have not tried this, before. It may also be possible to just dump the trace to a file directly, in case the Profiler application is getting stuck with a small textsize parameter. Script out the trace, and run that as a job to see if this gets you anywhere (on a test server, of course).

Also, does the BizTalk integration need to set the textsize for the connection? 70 MB is just outside the default value.

Of course, if you have access to the stored procedure itself, you could toss in some insert statements and direct some output to a log table.