Results 1 to 2 of 2

Thread: Ideas on Comparing SQL Records vs. Calendar

  1. #1

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    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:
    1. Sub FindMissingBackups(ByVal DaysToCheck As Integer, ByVal StoreNumber As String)
    2.         Dim TodaysDate As SqlDateTime = Now()
    3.         Dim PastDate As SqlDateTime = DateAdd(DateInterval.Day, -DaysToCheck, Now())
    4.  
    5.         MsgBox("Today: " & TodaysDate.ToString & vbNewLine & "Past: " & PastDate.ToString)
    6.         Try
    7.             Dim ConnString As String = "Server=" & _sqlServerAddress & ";Database=" & _sqlDatabase & ";User ID=" & _sqlUsername & ";Password=" & _sqlPassword & ";"
    8.             Dim SqlString As String = "SELECT DATESTAMP FROM " & _sqlTablename & " WHERE STOREID='" & StoreNumber & "' AND DATESTAMP >='" & PastDate.ToString & "'"
    9.             Dim SqlConn As New SqlConnection
    10.             Dim SqlCmd As New SqlCommand
    11.             SqlConn.ConnectionString = ConnString
    12.             SqlConn.Open()
    13.             SqlCmd.Connection = SqlConn
    14.             SqlCmd.CommandText = SqlString
    15.             SqlCmd.ExecuteNonQuery()
    16.             SqlConn.Close()
    17.         Catch ex As Exception
    18.             'Catch any errors here
    19.         End Try
    20.     End Sub
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

  2. #2

    Thread Starter
    Hyperactive Member mbutler755's Avatar
    Join Date
    May 2008
    Location
    Peoria, AZ
    Posts
    417

    Re: Ideas on Comparing SQL Records vs. Calendar

    Any ideas anybody?
    Regards,

    Matt Butler, MBA, BSIT/SE, MCBP
    Owner, Intense IT, LLC
    Find us on Facebook
    Follow us on Twitter
    Link up on LinkedIn
    mb (at) i2t.us

    CODE BANK SUBMISSIONS: Converting Images to Base64 and Back Again

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
  •  



Click Here to Expand Forum to Full Width