My ASP.Net 2.0 web site has a Data.xsd schema. I just added a table
adapter to the collection and configured it to use an existing SQL 2000
stored proc. When I tried to preview data from the table adapter, I
got "SQLOLEDB was unable to begin a distributed transaction" error.
The stored proc joins SQL 2000 tables via three linked servers. The
stored proc is declared on Win2003Host. Two of the linked servers
(Win2003A, Win2003B) running on Windows 2003 Servers and one (Win2000C)
running on a Windows 2000 Server. All the Win2003s have the Component
Serverices "Enable network DTC access" checked and properly configured.
The stored proc also runs fine in SQL Server management Studio. If I
remove the table from the Windows 2000 Server, then everything is fine.
Does anyone know how to configure SQL2000 in a mix Windows 2000 and
Windows 20003 join? Thanks. Happy New Year.
Here is my test stored proc on Win2003Host:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[procCompareAuthors]
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
select A.*, c.*
from [win2003A].[pubs].dbo.authors A
full outer join [win2003B].pubs.dbo.titleauthor B on A.au_id = B.au_id
full outer join [Win2000C].pubs.dbo.titles C on B.title_id = C.
title_id
END
