I am trying to set up the SQL Adapter in BizTalk 2009 to use a Stored Procedure in our SQL 2008 DB. For some reason, when I click "generate" in the wizard, and then click "next," I receive the following message:
Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. New Transaction cannot enlist in the specified transaction coordinator.
I know the stored proc works, because we call it from C# code as well and I don't have a problem with it. I'm pretty sure I'm missing something basic, but I can't find what it is, and the only information I can find on using Stored Procs through the BizTalk SQL Adapter only show Select statements, so I don't know if there's some other setting I"m missing.
Here's the Stored Proc:
SET NOCOUNT ON;
Declare @Client int
set @Client = (Select Client_Id from Clients where Client_Name = @clientName)
Insert Into [FTP_Data].[dbo].[FileLog](Client_Id, Client_Name, FileType, Received)
Values(@Client, @clientName, @fileType, GETDATE());
Edit/Update: When I move the solution off my developer box and onto the actual server box, it works fine. As far as I can tell, the only difference is that the actual BizTalk Server components (instead of just the developer stuff) are on the same machine as the SQL Server to which I'm connecting. I would think that should be an issue, though...