November 5, 2014 at 9:16 am
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!!
November 5, 2014 at 9:35 am
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
November 5, 2014 at 9:43 am
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.
November 5, 2014 at 9:46 am
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'
November 5, 2014 at 10:55 am
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