Windows Service ?Error?

[jmeckley]

This isn't so much an error as it is not working properly.

I have a windows service that has been running since mid january. Every night between 11pm and 12am it will query a sql server database and email the results to staff at our office. If there are 0 records the service emails staff that no events occured that day.

Starting feb. 1 (2/1/2004) the system has been reporting that no events occured that day. However this is not true because when i query the database using SQL Analyser there are events for that date. The records datetime stamp indicates the records were entered back in january to occur in february, so the information has been there for some time.

The servers date is correct. I don't think it is SQL Server. Do you have any idea why this "Error" is occuring. I have posted by code below.

Private Sub tmrOverview_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles tmrOverView.Elapsed
        If Date.Now >= Date.Today & " 11:00:00 PM" And Date.Now <= Date.Today & " 11:59:59 PM" Then
            Dim Cmd As New ExecuteCommand(AppSettings("cnn"))
            Dim BodyText As New System.Text.StringBuilder()
            Dim Input As New ArrayList()
            Dim OutputTbl As New DataTable()
            Dim OutputRow As DataRow
            Dim EmailTo As String
            Dim FeedDate As Date = Date.Today

            Try
                EmailTo = AD(AppSettings("ADOpsReport")) 'Select staff to email
                Input.Add(FeedDate)
                OutputTbl = Cmd.GetData("sp_Feed_Daily_s", Input) 'get data from the data base
                'create email message
                BodyText.Append("Date: " & FeedDate & "<P>")
                If OutputTbl.Rows.Count = 0 Then
                    BodyText.Append("There were no feeds scheduled for today (" & Date.Today & ").")
                Else
                    For Each OutputRow In OutputTbl.Rows
                        BodyText.Append("<TABLE>") 'begin outter table
                        BodyText.Append("<TR><TD>") 'begin outter row 1
                        '--------------------------------------------------------------------
                        BodyText.Append("<TABLE BORDER=1>") 'begin inner table
                        BodyText.Append("<TR>") 'begin inner row 1
                        BodyText.Append("<TD>Time Of Event</TD>")
                        BodyText.Append("<TD>" & OutputRow.Item(0) & "</TD>")
                        BodyText.Append("<TD>Completed</TD>")
                        BodyText.Append("<TD>" & OutputRow.Item(3) & "</TD>")
                        BodyText.Append("</TR>") 'end inner row 1
                        BodyText.Append("<TR>") 'begin inner row 2
                        BodyText.Append("<TD>Type Of Event</TD>")
                        BodyText.Append("<TD>" & OutputRow.Item(1) & "</TD>")
                        BodyText.Append("<TD>Successful</TD>")
                        BodyText.Append("<TD>" & OutputRow.Item(4) & "</TD>")
                        BodyText.Append("</TR>") 'end inner row 2
                        BodyText.Append("<TR>") 'begin inner row 3
                        BodyText.Append("<TD>Company</TD>")
                        BodyText.Append("<TD>" & OutputRow.Item(2) & "</TD>")
                        BodyText.Append("<TD>Completed By</TD>")
                        BodyText.Append("<TD>")
                        If Not IsDBNull(OutputRow.Item(5)) Then BodyText.Append(OutputRow.Item(5))
                        BodyText.Append("</TD>")
                        BodyText.Append("</TR>") 'end inner row 3
                        BodyText.Append("</TABLE>") 'end inner table
                        '--------------------------------------------------------------------
                        BodyText.Append("</TD></TR>") 'end outter row 1
                        BodyText.Append("<TR><TD>Anomalies<BR>") 'begin outter row 2
                        If Not IsDBNull(OutputRow.Item(6)) Then BodyText.Append(OutputRow.Item(6))
                        BodyText.Append("</TD></TR>") 'end outter row 2
                        BodyText.Append("</TABLE>") 'end outter table
                        BodyText.Append("<P>")
                    Next
                End If
                SendEmail(EmailTo, "Uplink Ops Report", BodyText.ToString, MailFormat.Html) 'send email
            Catch ex As Exception
                TimerError(ex)
            Finally
                OutputTbl.Dispose()
                OutputTbl = Nothing
            End Try
        End If
    End Sub

    Private Function AD(ByVal ADGroup As String) As String
        Dim DirEntry As New DirectoryEntry(AppSettings("ad"), AppSettings("admin"), AppSettings("pw"))
        Dim GroupSearch As New DirectorySearcher(DirEntry)
        Dim EmailSearch As New DirectorySearcher(DirEntry)
        Dim GroupResult, EmailResult As SearchResult
        Dim EmailTo As New System.Text.StringBuilder()
        Dim X As Integer

        EmailSearch.PropertiesToLoad.Add("Mail") 'select email address for user search
        GroupSearch.PropertiesToLoad.Add("member") 'select users for the group search
        GroupSearch.Filter = (ADGroup)
        GroupResult = GroupSearch.FindOne
        For X = 0 To GroupResult.Properties("member").Count - 1 'send each user an email
            EmailSearch.Filter = ("(" & Left(GroupResult.Properties("member").Item(X), InStr(GroupResult.Properties("member").Item(X), ",") - 1) & ")")
            EmailResult = EmailSearch.FindOne
            EmailTo.Append(EmailResult.Properties("Mail").Item(0) & "; ")
        Next

        DirEntry.Dispose()
        DirEntry = Nothing
        GroupSearch.Dispose()
        GroupSearch = Nothing
        EmailSearch.Dispose()
        EmailSearch = Nothing

        Return EmailTo.ToString
    End Function

    Private Sub TimerError(ByVal Ex As Exception)
        Dim ErrMsg As New System.Text.StringBuilder()

        ErrMsg.Append("Error accessing data and emailing staff for feed notification.")
        ErrMsg.Append(Chr(10) & Chr(13))
        ErrMsg.Append("Error Number: " & Err.Number)
        ErrMsg.Append(Chr(10) & Chr(13))
        ErrMsg.Append("Exception: " & Ex.Message)
        ErrMsg.Append(Chr(10) & Chr(13))
        ErrMsg.Append("Source: " & Ex.Source)
        ErrMsg.Append(Chr(10) & Chr(13))
        ErrMsg.Append("Stack Trace: " & Ex.StackTrace)
        NotifyAdmin(ErrMsg.ToString, System.Diagnostics.EventLogEntryType.Error)
    End Sub

    Private Sub NotifyAdmin(ByVal Msg As String, ByVal Type As System.Diagnostics.EventLogEntryType)
        ELog.WriteEntry(Msg, Type)
        SendEmail(AppSettings("ETo"), "Feed Notification Status", Msg, MailFormat.Text)
    End Sub

    Private Sub SendEmail(ByVal ETo As String, ByVal ESub As String, ByVal EMsg As String, ByVal Format As MailFormat)
        Dim Email As New MailMessage()

        SmtpMail.SmtpServer = AppSettings("EServer")
        With Email
            .To = ETo
            .From = AppSettings("EFrom")
            .Subject = ESub
            .Body = EMsg
            .BodyFormat = Format
        End With
        SmtpMail.Send(Email)
    End Sub

The function Cmd.GetData([sp], [array list]) is a class i created that can dynamically query sql server and return a data table of the records. if you would like I can post that as well.

Thank you for your assistance. I am racking my brain trying to figure this one out.

[jmeckley]

I found the issue.

it was a SQL Server "error" The data type of my parameter was varchar. I changed this to datetime and now it works.

I still don't understand why this worked in january and not february, but it works. I also tested for the other months and they report correctly as well.

[Grimfort]

Its possibly because feb has 29 days this year ? Or the format is your default instead of the american format it expects in strings.