January 18, 2006 at 4:59 pm
This question will apppear to be more about BizTalk, but I have the BizTalk side of it working (talks happily to SQL 2000), so wondering if someone is able toenlighten me on how to get it talking to SQL 2005.
Now that I am aware that BTS 2K4 will NOT install on a SQL 2K5 box, I am
trying to use the SQL receive adapter/SQL send adapter for Biztalk 2004 to
read/write data to SQL 2005 on a remote server.
I get the an error when trying to create a SQL adapter to access the remove
SQL server.
I can see the stored procedure I want which suggests that I am connecting
OK, then I get this message
"Failed to execute SQL Statement. Please ensure that the supplied syntax is
correct. The transaction manager has disabled its support for remote/network
transactions."
This would appear to be some setting in SQL 2K5, but not one I've yet come
across.
Or, perhaps, and more likely, this error means something else.
The syntax is generated by the SQL adapter wizard so I am assuming it is OK
- however I am going to check the stored procedure syntax and results again
to confirm that what I am extracting is correct.
Any thoughts or suggestions appreciated.
January 19, 2006 at 3:26 pm
Try adding the 'FOR xml auto, xmldata' syntax in the stored procedure when setting up the schema.
Then you have to take the ',xmldata' clause off before actually using it from an orchestration.
January 19, 2006 at 4:52 pm
Yeah, I have all of that from getting it to work on a BTS 2K4 to SQL 2000 setup.
Someone mentioned something about DTC and that has rung a few bells so I'l lbe checking that out next.
When I find the answer I'll drop another post here incase anybody is interested.
January 20, 2006 at 11:14 am
Please do since we also use Biztalk and I was not aware there may be an issue
January 22, 2006 at 1:18 pm
Well, now the boss has pulled me away from this issue.
However, the DTC stuff I mentioned just seems to be around ensuring permissions are correctly setup in a multi machine environment, unfortunately I no longer have time on my hands to play with this.
May 30, 2006 at 4:14 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply