|
| | | Forum Guru
 
Group: Forum Members
Last Login: 10/13/2008 7:26:33 AM
Posts: 72, Visits: 178 |
| Many Jobs on SQL Servers have to be executed on special Timestamps, eg. sending of newsletters or a backup job. To be shure all of the SQL servers have set up the same system-time i wrote a small script.
Your servers have to provide a Database with a Table containing 1 row and column (type Datetime), to hold your SQL System Time. When creating this Table one record have to be inserted. In another script I wrote the creation of the Database and Table and Records, but administrativly rights in a active script a shurely not good.
Here is the script:
'Set the result code of the check (0=Success, 1=Error)
result = 1
Const adUseClient = 3
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Driver={SQL Server};Server=" & Context.GetProperty("Address") & ";Database=DATABASE;Uid=USER;Pwd=PWD;"
conn.Open
Set recordset = CreateObject("ADODB.Recordset")
recordset.CursorLocation = adUseClient
recordset.Open "UPDATE TABLENAME SET COLUMNAME = CONVERT(CHAR(8),GETDATE(),8)" , conn
Set recordset=nothing
Set recordset = CreateObject("ADODB.Recordset")
recordset.CursorLocation = adUseClient
recordset.Open "SELECT * FROM TABLENAME" , conn
firstrow = recordset.GetRows(1,0)
comparetime = TimeValue(firstrow(0,0))
systime=Time
diff = DateDiff("n",comparetime,systime)
'Here you can Setup the maximum difference of that timespan, in this case 5min
If diff > -5 or diff < 5 Then
result = 0
End If
'Statushandling
If result = 0 Then
Context.SetResult 0, "Successfull"
Context.LogMessage "Checking Address=" & Context.GetProperty("Address") & " Systemtime is OK!"
End If
If result = 1 Then
Context.SetResult 1, "Error"
Context.LogMessage "Checking Address=" & Context.GetProperty("Address") & " Systemtime differs from WUP Server!"
End If
recordset.Close
Set recordset=nothing
conn.Close
Set conn=nothing
'End of script
Some may mention an UPDATE is not possible. You can write a stored procedure maybe to do that and only execute it with this script and then do the check.
Hope somebody like the idea of this script
Is it possible to do a system time compare without a sql server on that machine? I didn't search about that...
Greetings Jan
|
| |
|
|