Post reply

Linked Server error on Mirror DB

  • May 31, 2013 at 5:44 am

    #274384

    Hi all - we are executing some code below to check if the instance is the principle and then run some code if it is.

    IF exists

    (SELECT TOP 1 * FROM [BENTLEY].MASTER.sys.DATABASE_MIRRORING

    WHERE mirroring_role_desc = 'PRINCIPAL')

    BEGIN

    insert into [sql1].[web].[dbo].test

    (id,object,compid,type,createdtime,updatedtime,requeststatus)

    SELECT *

    FROM [BENTLEY].[zane].[dbo].[test]

    WHERE requestStatus = 1

    END

    ELSE

    begin insert into [sql1].[web].[dbo].test

    (id,object,compid,type,createdtime,updatedtime,requeststatus)

    SELECT *

    FROM [FERRARI].[zane].[dbo].[test]

    WHERE requestStatus = 1

    end

    However we always get the error below even though BENTLEY is the PRINCIPLE. The code above looks to be trying to validate the FERRARI linked server before the code even runs. Any ideas how to get this to work?

    Thanks

    OLE DB provider "SQLNCLI10" for linked server "FERRARI" returned message "Unspecified error".

    Msg 954, Level 14, State 1, Line 1

    The database "Zane" cannot be opened. It is acting as a mirror database.

  • May 31, 2013 at 6:55 am

    #1620047

    The code is tested at parsing time for all objects to exist;

    it doesn't allow logical testing to be ignored.

    you could switch to dynamic SQL instead to avoid the error.

    IF exists

    (SELECT TOP 1 * FROM [BENTLEY].MASTER.sys.DATABASE_MIRRORING

    WHERE mirroring_role_desc = 'PRINCIPAL')

    BEGIN

    EXEC('insert into [sql1].[web].[dbo].test

    (id,object,compid,type,createdtime,updatedtime,requeststatus)

    SELECT *

    FROM [BENTLEY].[zane].[dbo].[test]

    WHERE requestStatus = 1')

    END

    ELSE

    begin

    EXEC('insert into [sql1].[web].[dbo].test

    (id,object,compid,type,createdtime,updatedtime,requeststatus)

    SELECT *

    FROM [FERRARI].[zane].[dbo].[test]

    WHERE requestStatus = 1 ')

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 31, 2013 at 7:21 am

    #1620061

    ah yes thanks, thats worked thank you.

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

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