Linked Server View

  • 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:



    SELECT K.Last_Name, A.Affil_Birth_Date

    FROM dbo.Kernel K INNER JOIN


    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


    [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.

  • 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

    You may want to check the sybase site for the most recent ODBC drivers.

  • 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.)

  • Get both tables on the same server is probably your best bet. Replication might give you an easy way to manage this.


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply