-1

I am trying to create an integrity check to tell me when my two servers are more than 5 seconds apart in time.

I have two servers

HP1 is the server I am running this from HP2 is the remote server

This code works but I need to be able to produce a value that I can do a check against

select GETDATE()
exec ('select getdate()') at HP2

this is the code I am trying to produce

if( ABS(SUM(GETDATE() - exec ('select getdate()') at Jupiter)) > 5)
begin
print 'not in sync'
end
David
  • 95
  • 8
  • If you want to execute a query on a different, you'll need to create a Linked Server. – Jeremy Jan 06 '15 at 16:34
  • maybe some of [this](http://stackoverflow.com/q/1144051/2186023) might help you – DrCopyPaste Jan 06 '15 at 16:35
  • I don't understand why this was migrated from ServerFault. I get that it may seem inappropriate there because it's using SQL, but is SQL even the right way to be monitoring your servers for time sync problems (I would expect more appropriate methods for checking time sync *would* fall under the ServerFault umbrella)? And if it is, why wouldn't you move to http://dba.stackexchange.com ? – alroc Jan 06 '15 at 16:40
  • I have a linkserver set up but not allowing remote transactions, is there a way to get the output of exec ('select getdate()') at HP2 into a variable so I can use it in my check. – David Jan 06 '15 at 17:03
  • @David: This got migrated as I thought you wanted to perform some extra sanity checks when dealing with two SQL servers in some code you write (not dba.se as the SQL part is trivial). Frankly, I never even considered you wanted to do actual status monitoring - any database is about the very last tool I would consider for this job. A good question on [SF] would run along the line of "I have two DB servers running SnailSQL 2015 on FoobarOS 10.4. How can I check/make sure their time is in sync?". – Sven Jan 06 '15 at 18:23
  • I don't think this is a server error, it's more of an integrity rule to stop any process if the servers are more than 5 seconds apart as we use this time stamp in our system. I would like to know how I would approach this from a server side rather than an sql side if you know of a process or literature to help with this. – David Jan 07 '15 at 09:33

1 Answers1

0

I managed to come up with this solution, it works well and am able to use it in other ways.

I declare a variable

declare @ExecOutput date = null

I then run the execute command and assign the variable as the output of the execute command, this is done using the ?, It allows you to assign the output into a variable.

exec ('select ? = Getdate()',@ExecOutPut OUT) at HP2;

I can then run a condition against this variable.

if(datediff(ss, GETDATE(), @ExecOutPut) > 5 )
begin
raiserror ('Not In Sync', 1, 3)
end
David
  • 95
  • 8