2

We are monitoring the execution of database job using a in-house tool which requires an URL call after the job has been finished.

For our SQL jobs we use custom function (to be found here) that creates an MSXML2.ServerXMLHTTP.3.0 Object to send the URL request.

Our job runs every 5 minutes, so there is a URL call roughly every 5 minutes.
Under SQL Server 2005 and Win2k3 this solution worked perfectly for some years.
After we have recently migrated the jobs to a SQL Server 2008 (Win2k3 also), this URL call works for roughly 3 days and then fails.
The solution to fix this is a reboot of the server (restarting the sql server service does not work), so I suggest some memory leak. However I do have no clue, how to debug this memory leak- there are no error messages in the event log or no log files, what so ever.

The code for the custom function (property of Adam Mechanic- see above) is:

BEGIN   
  DECLARE @Object INT
  DECLARE @Return TINYINT
  DECLARE @Valid BIT
  SET @Valid = 0 --default to false
  --create the XMLHTTP object
  EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT 

  IF @Return = 0
  BEGIN 
    DECLARE @Method VARCHAR(350)
    --define setTimeouts method
    --Resolve, Connect, Send, Receive
    SET @Method = 'setTimeouts(45000, 45000, 45000, 45000)'
    --set the timeouts      
    EXEC @Return = sp_oamethod @Object, @Method

    IF @Return = 0
    BEGIN
      --define open method
      SET @Method = 'open("GET", "' + @URL + '", false)'
      --Open the connection
      EXEC @Return = sp_oamethod @Object, @Method
    END         

    IF @Return = 0      
    BEGIN   
      --SEND the request
      EXEC @Return = sp_oamethod @Object, 'send()'
    END     

    IF @Return = 0
    BEGIN           
      DECLARE @Output INT
      EXEC @Return = sp_oamethod @Object, 'status', @Output OUTPUT
      IF @Output = 200
      BEGIN         
        SET @Valid = 1
      END
    END
  END

  --destroy the object
  EXEC sp_oadestroy @Object
  RETURN (@Valid)
END

I have debugged it so far, that I know that

--define open method
SET @Method = 'open("GET", "' + @URL + '", false)'              
--Open the connection           
EXEC @Return = sp_oamethod @Object, @Method

fails.

Any clue is very much appreciated.

GSerg
  • 76,472
  • 17
  • 159
  • 346
ladam
  • 21
  • 3

0 Answers0