Mirror Database - Create Stored Procedure

  • Hi,

    I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".

    My Current Logic:

    CREATE PROCEDURE abc123

    as

    BEGIN

    IF Server01 = 'ONLINE'

    BEGIN

    <Run My Query>

    END

    IF Server02 = 'ONLINE'

    BEGIN

    <Run My Query>

    END

    END

    The problem I am facing is:

    - Stored procedure is not created because "One of the server is not Online"

    Can anyone give some alternate solutions to this.

    Thanks in advance!!

  • I don't think you really care about "server" information. What you really want to test is the database state. See sys.database_mirroring in Books Online.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Sorry, I forgot to mention that I need bring data to another server.

    - DataServer (Where I need data)

    - Server01 (Database Mirroring Server)

    - Server02 (Database Mirroring Server)

    and I need to run from DataServer and I have linked server connection.

  • Declare @principalServer01 varchar(50);

    SELECT @principalServer01=state_desc

    FROM [Server01].master.sys.databases

    WHERE name = 'databasename';

    IF @principalServer01='ONLINE'

    Declare @principalServer02 varchar(50);

    SELECT @principalServer02=state_desc

    FROM [Server02].master.sys.databases

    WHERE name = 'databasename';

    IF @principalServer02='ONLINE'

    I use this query when I meant Server01='ONLINE'

  • 1) where are you trying to compile this sproc?

    2) what is the exact error you are having when you try to compile it?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 5 (of 5 total)

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