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.