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.