-
May 2nd, 2011, 09:36 AM
#1
Thread Starter
Hyperactive Member
Ideas on Comparing SQL Records vs. Calendar
I have a program that backs-up remote locations to a centralized FTP server. Upon successful completion, a record is written to a SQL database with the following information:
Code:
GUID (uniqueidentifier)
StoreID (varchar(5))
DateStamp (sqldatetime)
ProgramAndVersion(varchar(50))
I am currently managing the program by running a report on who has written a record to the database. If the record is not written, I manually go in and backup the necessary files.
The problem with this, is that it can take up to 30 minutes/day to do this task. While that's not a long time, it's getting somebody to be dedicated when I'm off and that has proven to be difficult.
I want the program to automatically search for missing records, but I'm not sure how to do this. I was thinking about bringing in the last 30 days with a SELECT statement and then comparing the information that I have in the SQL database versus a calendar control (or something). I need to make this process completely automated and not very intensive on the SQL server. I need some ideas...
If you have any suggestions, I would love to hear them. Thus far, I only have the following code:
2010 Code:
Sub FindMissingBackups(ByVal DaysToCheck As Integer, ByVal StoreNumber As String)
Dim TodaysDate As SqlDateTime = Now()
Dim PastDate As SqlDateTime = DateAdd(DateInterval.Day, -DaysToCheck, Now())
MsgBox("Today: " & TodaysDate.ToString & vbNewLine & "Past: " & PastDate.ToString)
Try
Dim ConnString As String = "Server=" & _sqlServerAddress & ";Database=" & _sqlDatabase & ";User ID=" & _sqlUsername & ";Password=" & _sqlPassword & ";"
Dim SqlString As String = "SELECT DATESTAMP FROM " & _sqlTablename & " WHERE STOREID='" & StoreNumber & "' AND DATESTAMP >='" & PastDate.ToString & "'"
Dim SqlConn As New SqlConnection
Dim SqlCmd As New SqlCommand
SqlConn.ConnectionString = ConnString
SqlConn.Open()
SqlCmd.Connection = SqlConn
SqlCmd.CommandText = SqlString
SqlCmd.ExecuteNonQuery()
SqlConn.Close()
Catch ex As Exception
'Catch any errors here
End Try
End Sub
-
May 3rd, 2011, 11:06 PM
#2
Thread Starter
Hyperactive Member
Re: Ideas on Comparing SQL Records vs. Calendar
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|