July 18, 2001 at 7:25 pm
I have been trying to create a complex view of tables in our local SQL Server 2000 database along with tables in the University's central storage. Between SQL Server databases, this is straightforward. In this case, the LINK_TO_AFFILIATE linked server is a Sybase DB. Here's a simplified example of what I'm trying to do:
CREATE VIEW dbo.K_Aff_DOB
AS
SELECT K.Last_Name, A.Affil_Birth_Date
FROM dbo.Kernel K INNER JOIN
LINK_TO_AFFILIATE.AFFILIATE.dbo.AFFILIATE A ON K.Affiliate_ID = A.Affiliate_ID
What I am trying to do is show users data from our database along with related data from the linked database, all in a view that mimics a gigantic Access table they used to have. I'm doing it this way so that all their queries won't break. But this create view statement fails, citing the following:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable to
begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [SYBASE][ODBC Sybase driver]Distributed transaction enlistment
failed.]
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [SYBASE][ODBC Sybase driver]Load of XA library failed.]
Where's the problem? The DBAs for the Affiliate DB have drawn a blank. Thanks in advance for any ideas.
July 18, 2001 at 10:27 pm
I am guessing that Microsoft Transactin Server is getting involved here and maybe your Sybase ODBC drivers do not support this. Check out this link you may find some relevant info here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmts/html/msdn_transfaq.asp
You may want to check the sybase site for the most recent ODBC drivers.
July 19, 2001 at 11:07 am
Thanks, Leon, and I'll bet you're right about the Sybase drivers. The link you included said that Sybase was hoping to support MTS in the second half of 1998. Hmmm.
We have obtained and installed the v12 ODBC and OLE-DB drivers from Sybase. No joy.
If views can't be made to work, is there another way to join tables across linked servers that is efficient? I'm familiar with using OpenQuery and OpenDataSource to speed things up for individual rows, but if I need to match several local rows to their corresponding data, I'm finding 40-50 seconds delay. Ugh. (The Sybase DB has several million rows to scan, vs about 20000 in my SQL Server DB.)
July 19, 2001 at 8:43 pm
Get both tables on the same server is probably your best bet. Replication might give you an easy way to manage this.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply