Linked Server View

  • RickInMesa

    Old Hand

    Points: 303

    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.

  • Leon Platt

    SSCommitted

    Points: 1921

    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.

  • RickInMesa

    Old Hand

    Points: 303

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

  • Andy Warren

    SSC Guru

    Points: 119676

    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 4 (of 4 total)

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