How To Get Data From Primary

Hi Guys,

We have three servers.
Server A - Primary
Server B - Mirror
Server C - Stand Alone (also used for reporting, but, not using SQL Reporting).
All the three servers are interconnected using linked servers.

Heres the brief overview of my problem:

A certain procedure requires me to get live data from the 'Primary' to Server C on regular basis. This procedure will be scheduled to run once a day on Server C.

Heres the code:


create procedure sp_getDataFromPrimary
as


--First I check if Server A is primary
if exists( select * from [serverA].master.sys.database_mirroring where name = 'database1' and
mirroring_role_desc = 'primary')

begin

insert into [serverC].[database1].[dbo].[table1]
select * from [serverA].[database1].[dbo].[table1]
end
else

begin

insert into [serverC].[database1].[dbo].[table1]
select * from [serverB].[database1].[dbo].[table1]
end

go

When I try to create the above procedure, I get an error message similar to 'cannot access
[server B].[database1].[dbo].[table1] as the database is being mirrored. I tried to use variablesfor the server, database and tables, but, no avail -- I still get the same error.

My qn now is : How can I make this work. If you think my approach is faulty, pls, suggest any other.

Eagerly awaiting for your response.

Thankyou.

p.s: Cannot use SSIS for it will complicate the other additional processing that needs to be done.