0

Context: I have a SQL Server database engine called DB4, and it's updating all its databases from another database engine called DB5 through the SQL Server agent every 5 hours. I don't have access to DB5, but I have been told DB5 is also updating from somewhere else.

Problem: The problem is that sometimes the two database engines will update their databases simultaneously, so the DB4 cannot update completely.

Question: Is there any way I can detect if DB5 is updating? Then I can write in the SQL server agent jobs, like if the DB5 is not updating then update DB4, otherwise do nothing.

PS: The way DB4 updates is processed by many Agent Jobs. Somebody wrote many scripts in the jobs. Basically, the scripts are like this format:

TRUNCATE Table_Name
INSERT INTO Table_Name
SELECT field_name1,field_name2 ......
FROM DB5.database_name.table_Name
CodTango
  • 345
  • 1
  • 3
  • 15
  • Is there anything distinctive about the DB5 update, for example, it connects as specific user ? – Angst Apr 02 '14 at 19:58
  • 1
    What do you mean by "update"? Replication? – usr Apr 02 '14 at 20:04
  • @Angst I have no idea, I don't have access to DB5. Only thing I know about DB5 is that it's an exchange database engine, DB5 updates from some other places, then some database engines like DB4 will pull data from it. – CodTango Apr 02 '14 at 20:05
  • well, if you have access to DB4, and you knew that when DB5 does its update, it uses a certain user to do this, you could check for the presence of a session for that user in DB4. – Angst Apr 02 '14 at 20:09
  • @usr Somebody wrote many scripts in SQL Server Agent. Basically, it's just TRUNCATE a table, then SELECT from DB5 and INSERT into the table. By this way, DB4 updates all its databases. – CodTango Apr 02 '14 at 20:11

1 Answers1

0

By DB4, DB5 you mean servers not databases which the names here are confusing. Nevertheless, if you have access to DB4 and DB4 is selecting from DB5, that means DB5 is a linked server registered in DB4 with a user who has a query permission on DB5 databases and MAY have insert/update/delete/create objects permissions. If so, then you can create a table in DB5.database_name as follows:

CREATE TABLE DB5.database_name.dbo.Table_Flag(Uflag bit NOT NULL)
GO
INSERT INTO DB5.database_name.dbo.Table_Flag(Uflag) values (0)

then you can create a trigger for the updating table in DB5-Database which will update the Uflag to 1 if there are any newly updated/inserted/deleted rows

Then you can modify the job in DB4 as:

declare @count int

set @count  = (select count(*) fromDB5.database_name.dbo.Table_Flag where Uflag = 1)
if (@count > 1)
begin
TRUNCATE Table_Name
INSERT INTO Table_Name
SELECT field_name1,field_name2 ......
FROM DB5.database_name.table_Name

UPDATE DB5.database_name.dbo.Table_Flag SET Uflag = 0
end
Haz
  • 112
  • 1
  • 1
  • 8