Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22

    Unanswered: Stored procedure problem ? Help needed...

    While trying to run an .exe file which analyzes the log files from IIS 5.0, for a web-site, I get this error...

    Run-time error '5'. Invalid procedure call or argument

    Is it possible this error to be generated from a stored procedure ???


    Thanks !
    Visit...
    http://kanenas.net
    News and Ideas about programming

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    is your stored procedure in the database you are using in your connection string?

    if so can you trace the call to it in profiler and try to execute that call directly in the query analyzer and tell us what message you get then?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While a stored procedure is able to generate almost any error message, that doesn't sound like one that would come naturally from a stored procedure. I'd bet that the problem is coming from elsewhere, very probably from Visual BASIC.

    -PatP

  4. #4
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    That is what i think too ! But I could really use a second opinion from a more experienced person. Thanks for the link Pat Phelan!!!
    Visit...
    http://kanenas.net
    News and Ideas about programming

  5. #5
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    Quote Originally Posted by Thrasymachus
    is your stored procedure in the database you are using in your connection string?

    if so can you trace the call to it in profiler and try to execute that call directly in the query analyzer and tell us what message you get then?
    Yes it is.
    Of course I will give it a shoot, just in case...
    Thanks !
    Visit...
    http://kanenas.net
    News and Ideas about programming

  6. #6
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    I have tried to trace the call with SQL Profiler (I haven't really used SQL Profiler many times...) and I couldn't find something !

    The steps I took...

    I have created a new trace and from the Event tab and I have added all the Errors and Warnings, Stored procedures and then I runned the trace !

    After that I runned the exe file which analyzes the log files from IIS 5.0 .

    Nothing happened !
    Visit...
    http://kanenas.net
    News and Ideas about programming

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The Profiler only shows you interactions between the SQL Server and other machines. If my assumption is correct and your error message came from VB before it connected to the SQL Server, then it makes perfect sense that you wouldn't see anything in the Profiler trace because the program never got far enough for the connection to the SQL Server to be opened.

    -PatP

  8. #8
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    Quote Originally Posted by Pat Phelan
    The Profiler only shows you interactions between the SQL Server and other machines. If my assumption is correct and your error message came from VB before it connected to the SQL Server, then it makes perfect sense that you wouldn't see anything in the Profiler trace because the program never got far enough for the connection to the SQL Server to be opened.

    -PatP
    OK then. Here is the code, written in Visual Basic 6...
    Code:
    Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFilename As String) As Long
    Dim msLogFile As String
    Sub Main()
    Dim aArgs() As String, sInifile As String, sConns() As String, sConn As String
    Dim i%, j%, oVar As Object, aLogPaths() As String, aFilespecs() As String
    Dim dDate As Date, nLogDays As Integer, nLogDelay As Integer, sTmp As String
    Dim nTimeZone As Integer, sLabel As String, sSiteRoot As String, bConnsFound As Boolean
    Dim sSiteName As String, sSiteNameStart As Integer, sSiteNameStop As Integer, sSiteExclude As String
    Dim nSessionMinutes As Integer, sCommand As String, bClearLog As Boolean
    Dim dStart As Date, dEnd As Date, nSite As Integer, bDateSpecified As Boolean
    sInifile = App.Path & "\Log.ini"
    msLogFile = App.Path & "\Log.log"
    LogMessage "Log started"
    
    'Read and remove optional /d switch. Rest of arguments is [n] [d1 [d2]]
    '[n] number of site
    '[d1] start date
    '[d2] end date
    sCommand = Trim(Command$)
    If LCase(Left(sCommand, 2)) = "/d" Then
       bClearLog = True
       sCommand = Trim(Mid(sCommand, 3))
    End If
    
    aArgs = Split(sCommand, " ")
    If UBound(aArgs) < 2 Then
       ReDim Preserve aArgs(2)
    End If
    
    If IsNumeric(aArgs(0)) Then
       nSite = aArgs(0)
       If IsDate(aArgs(1)) Then
          bDateSpecified = True
          dStart = aArgs(1)
          If IsDate(aArgs(2)) Then
             dEnd = aArgs(2)
          Else
             dEnd = dStart
          End If
       End If
    ElseIf IsDate(aArgs(0)) Then
       bDateSpecified = True
       dStart = aArgs(0)
       If IsDate(aArgs(1)) Then
          dEnd = aArgs(1)
       Else
          dEnd = dStart
       End If
    End If
    If bDateSpecified Then
       nLogDays = DateDiff("d", dStart, dEnd) + 1
    End If
    If nSite > 0 Then
       sConn = ReadINI(sInifile, "Site" & nSite, "ConnString")
       If sConn = "" And nSite = 1 Then
          sConn = ReadINI(sInifile, "Site", "ConnString")
       End If
       If sConn = "" Then
          LogMessage "Site not found in the .ini file. Operation aborted."
          LogMessage "Log finished."
          Exit Sub
       End If
       ReDim sConns(0)
       sConns(0) = sConn
    Else
       i = 1
       Do While True
          sConn = ReadINI(sInifile, "Site" & i, "ConnString")
          If sConn = "" And i = 1 Then
             sConn = ReadINI(sInifile, "Site", "ConnString")
          End If
          If sConn <> "" Then
             ReDim Preserve sConns(i - 1)
             sConns(i - 1) = sConn
             bConnsFound = True
          Else
             If i = 1 Then
                LogMessage "No site(s) or invalid connection string(s). Operation aborted."
                LogMessage "Log finished."
                Exit Sub
             End If
             Exit Do
          End If
          i = i + 1
       Loop
    End If
    
    For i = 1 To UBound(sConns) + 1
       sLabel = "Site " & i & ": "
       Set oVar = CreateObject("CMS.AdminVar")
       oVar.ConnString = sConns(i - 1)
       ReDim aLogPaths(1)
       On Error Resume Next
       aLogPaths(0) = oVar.GetValue("LogPath1") & ""
       If Err.Number <> 0 Then
          LogMessage sLabel & Err.Source & ", " & Err.Description & ". Operation aborted"
          LogMessage "Log finished."
          Set oVar = Nothing
          Exit Sub
       End If
       aLogPaths(1) = oVar.GetValue("LogPath2") & ""
       On Error GoTo 0
       If Trim(aLogPaths(0) & aLogPaths(1)) = "" Then
          LogMessage sLabel & "No 'LogPath1' or 'LogPath2' defined. Operation aborted"
       Else
          aFilespecs = Split(oVar.GetValue("Log Filespecs") & "", ",")
          sTmp = oVar.GetValue("Log Average Session")
          If Not IsNumeric(sTmp) Then
             LogMessage sLabel & "'Log Average Session' parameter not found. Using default of 20 minutes"
             nSessionMinutes = 20
          Else
             nSessionMinutes = CInt(sTmp)
          End If
          If Not bDateSpecified Then
             sTmp = oVar.GetValue("Log Days")
             If Not IsNumeric(sTmp) Then
                LogMessage sLabel & "'Log Days' parameter not found. Using default of 1 day"
                nLogDays = 1
             Else
                nLogDays = CInt(sTmp)
             End If
             sTmp = oVar.GetValue("Log Delay")
             If Not IsNumeric(sTmp) Then
                LogMessage sLabel & "'Log Delay' parameter not found. Using default of 1 day"
                nLogDelay = 1
             Else
                nLogDelay = CInt(sTmp)
             End If
          End If
          sTmp = oVar.GetValue("Time Zone")
          If Not IsNumeric(sTmp) Then
             nTimeZone = 0
          Else
             nTimeZone = CInt(sTmp)
          End If
          sTmp = oVar.GetValue("DST")
          If sTmp = "on" Then
             nTimeZone = nTimeZone + 1
          End If
          sSiteRoot = oVar.GetValue("Site Root Directory") & ""
          sSiteExclude = oVar.GetValue("Log Sites Exclude") & ""
          sSiteName = oVar.GetValue("Preview Link") & ""
          If Len(sSiteName) > 7 Then
            sSiteNameStart = InStr(1, sSiteName, "http://")
            sSiteNameStop = InStr(sSiteNameStart + 7, sSiteName, "/")
            sSiteName = Mid(sSiteName, sSiteNameStart + 7, sSiteNameStop - sSiteNameStart - 7)
          Else
            sSiteName = ""
          End If
          LogMessage "SiteName: " & sSiteName
          If Not bDateSpecified Then
             dStart = DateValue(Now())
             dStart = DateAdd("d", -nLogDelay - nLogDays + 1, dStart)
          End If
          For j = 1 To nLogDays
             dDate = DateAdd("d", j - 1, dStart)
             On Error Resume Next
             AnalyzeLog dDate, nTimeZone, aLogPaths, sConns(i - 1), sSiteRoot, nSessionMinutes, aFilespecs, bClearLog, sSiteName, sSiteExclude
             If Err.Number <> 0 Then
                LogMessage sLabel & dDate & ", " & Err.Source & ", " & Err.Description & "."
                Err.Clear
             End If
             On Error GoTo 0
          Next
          LogMessage sLabel & "Finished."
       End If
    Next
    Set oVar = Nothing
    LogMessage "Log finished."
    End Sub
    
    Function GetLogFiles(aLogPaths() As String, dDate As Date, ByRef aFiles() As String) As Integer
    Dim oFS As Scripting.FileSystemObject, oFile As Scripting.File, oFolder As Scripting.Folder
    Dim nFileCount As Integer, i%, dStart As Date, dEnd As Date, nPathCount As Integer, dYear As String
    Dim dMonth As String, dDay As String
    
    nPathCount = UBound(aLogPaths) + 1
    
    dStart = CDate(DateValue(dDate) & " 00:00:00")
    dEnd = CDate(DateValue(dDate) & " 23:59:59")
    Set oFS = New Scripting.FileSystemObject
    nFileCount = 0
    
    For i = 0 To nPathCount - 1
       If oFS.FolderExists(aLogPaths(i)) Then
          Set oFolder = oFS.GetFolder(aLogPaths(i))
          For Each oFile In oFolder.Files
                dYear = CStr(Year(dDate) - 2000)
                If CInt(dYear) < 10 Then
                    dYear = "0" + dYear
                End If
                dMonth = CStr(Month(dDate))
                If CInt(dMonth) < 10 Then
                    dMonth = "0" + dMonth
                End If
                dDay = CStr(Day(dDate))
                If CInt(dDay) < 10 Then
                    dDay = "0" + dDay
                End If
                
                If oFile.Name = "ex" + dYear + dMonth + dDay + ".log" Then
                'MsgBox ("ex" + dYear + dMonth + dDay + ".log")
       '         If oFile.DateLastModified >= dStart And oFile.DateCreated <= dEnd Then
                nFileCount = nFileCount + 1
                ReDim Preserve aFiles(nFileCount - 1)
                aFiles(nFileCount - 1) = oFile.Path
             End If
          Next
       End If
    Next
    Set oFolder = Nothing
    Set oFS = Nothing
    Set oFile = Nothing
    GetLogFiles = nFileCount
    End Function
    Function ReadINI(FileName As String, tmpSecname As String, tmpKeyname As String, Optional sDefaultReturn As String = "") As String
    Dim tmpString As String
    Dim secname As String
    Dim KeyName As String
    Dim keyvalue As String
    Dim anInt
    Dim defaultkey As String
    
    On Error GoTo ReadINIError
    ReadINI = sDefaultReturn
    
    If IsNull(tmpSecname) Or Len(tmpSecname) = 0 Then
      Exit Function
    End If
    If IsNull(tmpKeyname) Or Len(tmpKeyname) = 0 Then
      Exit Function
    End If
    
    secname = tmpSecname
    KeyName = tmpKeyname
    defaultkey = "Failed"
    keyvalue = String$(2048, 32)
    anInt = GetPrivateProfileString(secname, KeyName, defaultkey, keyvalue, Len(keyvalue), FileName)
    If Left(keyvalue, 6) <> "Failed" Then        ' *** got it
       tmpString = keyvalue
       tmpString = RTrim(tmpString)
       tmpString = Left(tmpString, Len(tmpString) - 1)
       ReadINI = tmpString
    End If
    
    Exit Function
    ReadINIError:
       LogMessage Error
       Exit Function
    End Function
    Sub LogMessage(sMessage As String)
    Dim sLine As String, f As Long, nLen
    f = FreeFile
    sLine = CStr(Now()) & " " & Trim(sMessage)
    Open msLogFile For Append As #f 'Len = Len(sLine)
    Print #f, sLine
    Close #f
    End Sub
    The most difficult part is... to read code written from others !!!
    Any suggestions ?
    Visit...
    http://kanenas.net
    News and Ideas about programming

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Your best bet is to read up on error handling in VB. All you need to do here is put a breakpoint on the first line, run the code and then press F8. The breakpoint stops code execution on the line with the breakpoint and F8 moves execution on a line at a time. This will continue until the code errors. You then know which is the offending line and you are 90% of the way to fixing it.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    First of all, thanks for replying !

    All you need to do here is put a breakpoint on the first line...
    OK !
    run the code and then press F8.
    OK ! So far...
    The breakpoint stops code execution on the line with the breakpoint and F8 moves execution on a line at a time. This will continue until the code errors.
    So with the first stop, I have the first error and so on ?
    Visit...
    http://kanenas.net
    News and Ideas about programming

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - you could probably do with one change.

    Comment out this line:
    On Error GoTo ReadINIError

    Remember to put it back once finished!

    Now when you step through the code (pressing F8) you will get a message box (hopefully with the Runtime error 5 : ..... message) when the offending line is executed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    Quote Originally Posted by pootle flump
    Comment out this line:
    On Error GoTo ReadINIError
    Now when you step through the code (pressing F8) you will get a message box (hopefully with the Runtime error 5 : ..... message) when the offending line is executed.
    I did that and nothing happened ! No errors...
    WTF
    Visit...
    http://kanenas.net
    News and Ideas about programming

  13. #13
    Join Date
    Aug 2006
    Location
    Athens - Greece
    Posts
    22
    Ok ! Here is another clue that might help.
    The log.exe file runs without problems for the first 68 sites, but when it comes to 69 stops without displaying any errors !!!
    Visit...
    http://kanenas.net
    News and Ideas about programming

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. Remove the breakpoint and just run the code with that line I mentioned commented out still. If an unhandled error occurs you should get a box asking you if you want to debug or end. Press debug and post the line of code that is highlighted in yellow here.

    BTW - personally I like to put On Error Resume Next in my error logging procedures. There is not much that can go wrong with your code but if it did it would get itself into an infinite loop:

    Code:
    Sub LogMessage(sMessage As String)
    On Error Resume Next
    
    Dim sLine As String, f As Long, nLen
    f = FreeFile
    sLine = CStr(Now()) & " " & Trim(sMessage)
    Open msLogFile For Append As #f 'Len = Len(sLine)
    Print #f, sLine
    Close #f
    End Sub
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •