locked
Accessing Mirrored DBs on SQL2k5 (x86) from SQL2k RRS feed

  • Question

  • Hi

     

    I'm trying to select data from a mirrored database situated on a SQL2k5 server, via a linked server on SQL2k.

     

    I can select the data fine using SQLOLEDB but it's not aware of the mirroring so if the DB fails-over it errors.

     

    To get around this, I installed SQLNCLI.msi to install the SQL2k5 provider and created a linked server like this:

     

    exec sp_addlinkedserver @server=N'TEST2',@srvproduct=N'SQLNCLI',@provider=N'SQLNCLI',@provstr=N'Server=server1;failover partner=server2;'
    exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST2',@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='password'

     

    Executing the statement: select * from test2.database.dbo.table

     

    gives the following error:

     

    Server: Msg 7356, Level 16, State 1, Line 1
    OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
    OLE DB error trace [Non-interface error:  Column 'IsActive' (compile-time ordinal 2) of object '"database"."dbo"."table"' was reported to have changed. The exact nature of the change is unknown].

     

    If i change the statement to

     

    select * from openquery(test2,'select * from database.dbo.table')

     

    It works. I don't really want to have to go and refactor all the SQL though!

     

    I've seen similar posts related to Oracle and SQL2k5 64bit but they don't seem to be relevant to this situation.

     

    Here's the version info:

     

    2000 server (SP3):

     

    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    2005 mirrored servers (SP2 3054):

     

    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

     

    I've also tried applying the following to the 2005 servers: sp2(3159) sp2(3161) but makes no difference.

     

    Any ideas?

     

    TIA

    Friday, September 14, 2007 8:56 AM

All replies

  • I've also got 1 SQL2000 server and attempting to configure a linked server to connect to mirrored SQL2005 databases. If I install the SQL native client and still use the "ole db provider for SQL", would this work? Or, do I need to go thru the "native client" provider?

     

    Secondly, where and what do I specify the failover partner?  I'm thinking either in the "data source" or the "provider string" fields.

     

    Would appreciate any clarification on this one.

     

     

    Tuesday, November 18, 2008 7:15 PM