Month: September 2020

SQL – How to Update Records in a Table Using a Loop and Cursor

This article is a popular re-post from my Blogger blog from November of 2011 but is still relevant today.

I had a challenge yesterday that I would normally solve using some Visual Basic code but had to do it in pure SQL on a SQL 2005 server.

THE CHALLENGE:
Loop through all the records in a table and then update the table based on some logic or condition.

THE SOLUTION:

Using a SQL Cursor, I was able to loop through all the records in a table and then run an SQL update command for certain records that matched a particular criteria.

Here is the code: 

DECLARE @myEmpID int
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT  EmployeeID FROM Employees WHERE Company=64

OPEN myCursor
--Read the initial emploee id value from the cursor
FETCH NEXT FROM myCursor
INTO @myEmpID

WHILE @@FETCH_STATUS = 0
BEGIN

--Update goes here 
UPDATE Employees 
SET [Status] = 'T'
WHERE EmployeeID = @myEmpID AND Company=54
FETCH NEXT FROM MyCursor
INTO @myEmpID
END
CLOSE MyCursor

Hope this helps someone!

Regards,
Cyber Abyss

VBScript Error – MSXML3.dll error ‘800C0005’

This is a popular re-post from my old Blogger blog.

I’m working on  a website up-time monitoring script.

After I had my initial prototype working, I received more requirements for logging output to CSV in addition to storing output in Access DB and decided to add CPU usage percentages to the logging.

Once I had all that done, I started getting odd errors when solution should be detecting a down-time event.

Testing the script with older version of Microsoft.XmlHttp had issues where certain but not all websites the script would call were incorrectly displaying HTTP status code 404 but a 200 OK was really sent back verified by using Fiddler.  Very odd behavior.

The error is related to VBScript’s use of the older version of the Microsoft.XmlHttp object.

The Code

Function isPortalOffline(strURL)
 'Set WshShell = WScript.CreateObject("WScript.Shell")
 Set http = CreateObject("Microsoft.XmlHttp")

 http.open "GET", strURL, False
 http.send 
 
 'Only check for error of the http Get request
 if err.Number <> 0 Then
  isPortalOffline = True
 Else
  'Wscript.Echo "error" & Err.Number & ": " & Err.Description
  isPortalOffline = False
 End If

 'Clear the error after setting isPortalOffline
 err.clear
 'set WshShell = Nothing
 Set http = Nothing 

 ReportError("isPortalOffline")
End Function

Error: MSXML3.dll error ‘800C0005’ The System cannot locate the resource

Changed MSXML Objects.

The Fixed Code

Replaced Microsoft.XmlHttp with MSXML2.ServerXMLHTTP. 

Function isPortalOffline(strURL)
 'Set WshShell = WScript.CreateObject("WScript.Shell")
 Set http = CreateObject("MSXML2.ServerXMLHTTP")

 http.open "GET", strURL, False
 http.send
 
 'Only check for error of the http Get request
 if err.Number <> 0 Then
  isPortalOffline = True
 Else
  'Wscript.Echo "error" & Err.Number & ": " & Err.Description
  isPortalOffline = False
 End If

 'Clear the error after setting isPortalOffline
 err.clear
 'set WshShell = Nothing
 Set http = Nothing 

 ReportError("isPortalOffline")
End Function

This resolved my issues!

I hope this helps someone. 🙂