VBscript to remove all traces of computers from DS Database

Article ID: 26082
Running the following VBScript will remove all traces of computers from the DS database:
———————————————————————-
Option Explicit ‘**** The script displays a list of computer_ids and the associated computer name and ‘ allows the user to enter the computer_id that it to be removed from the express ‘ database. All occurrences of the record are removed from the database ‘ADO Const conADOStateClosed = &H00000000 Const conADOStateOpen = &H00000001 Const adStateClosed = 0 Dim strErr dim cmdArgs ‘**************** Call the Delete Computer function ************************* DeleteComputer ‘Line 18 ‘******************************************************************************** ‘Function to capture error messages ‘******************************************************************************** Function GetErrorString(objErr, strContext) Dim strError strError = "An error has occurred. " If (strContext <> "") Then strError = strError & strContext & ". " If (objErr.description <> "") Then strError = strError & objErr.description Else strError = strError & "Error number " & objErr.number End If GetErrorString = strError objErr.Clear End Function ‘Line 40 ‘*********************************************************************************** ‘Function to insert parameter from command line (computer_id) into the query ‘ ‘********************************************************************************* Sub InsertIdValue(objConn, lComputer_Id) On Error Resume Next ‘wscript.echo "The computer_id value is " & lComputer_id Dim strSQL, strErr ‘wscript.echo "Ready to insert" & lComputer_Id & " into database" strSQL = "INSERT INTO ##DeleteComputer Values(" + CStr(lComputer_Id) + ")" objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete computer " & strComputer_Id) logMessage strErr, 1 End If End Sub ‘Line 62 ‘*************************************************************************************** ‘Function to delete the computer selected ‘ The first part of this is providing the data to make connection to the SQL database ‘******************************************************************************* Function DeleteComputer On Error Resume Next ‘Line 73 Dim objConn, objAdmin Dim strTableName, strSQL, strDBName, strDBMachine, strDBUserPW, strDBUser, strConnection Dim objRecSet, strTabs, strDelete, strTableNumber, strHostType, strDummy, strComputer_Id Dim objRs, objTest, strAllComputers, intCountObjects, intStringLength, objDiskRs, strComputerNumber Dim gstrServer, gstrDatabase, gstrUser, gstrPwd, objWShell, strRegLocation, strUseCmdLine, strIBTxt, strInstruct strHostType = right(wscript.fullname,11) strHostType = lcase(left(strHostType,7)) ‘Line 84 ‘**************Connection to the database**************** Set objConn = CreateObject("ADODB.Connection") objConn.Open strConnection if err.number <> 0 then LogError "Error - doing SQL connection as the current logged on user.", err.number, err.description strDBUser = inputbox("Please enter a SQL user -" & vbCRLF & "(sa will always work if you have the password)" & vbCRLF & "The password will be asked for in the next box.", "SQL logon", "Enter SQL user") strDBUserPw = inputbox("Please enter the SQL user password", "SQL password", "Enter SQL user password") Err.clear strConnection = strConnection & ";UID=" & strDBUser & ";PWD=" & strDBUserPw objConn.Open strConnection If err.number <> 0 then LogError "Error - doing SQL connection with new user and pw.", err.number, err.description strDBMachine = inputbox("Please enter the SQL server machine name", "SQL machine name", "Enter SQL machine name") strDBName = "express" Err.clear strConnection = "DRIVER={SQL Server};SERVER=" & strDBMachine & ";DATABASE=" &strDBName & ";UID=" & strDBUser & ";PWD=" & strDBUserPw ‘Line 103 objConn.Open strConnection If err.number <> 0 then LogError "Error - doing SQL connection with new machine name, db, user and pw.", err.number, err.description LogError left(strConnection, inStrRev(strconnection,"PW") - 1), err.number, err.description Wscript.echo "The connection to SQL was not created." & vbCRLF & vbCRLF & "The script will halt now. Please resolve the SQL connection error." ‘wscript.echo "DBName is " & strDBName wscript.quit ‘Line 111 Else LogError "SQL connection with SQL user name, password, machine and db name.", err.number, err.description End if else LogError "SQL connection with SQL user name and password.", err.number, err.description End If Else LogError "SQL connection as the current logged on user.", err.number, err.description End If objConn.CommandTimeout = 600 ‘Line 119 ‘******************Create a global temp table of ************** strSQL = "if isnull(object_id(’tempdb..##DeleteComputer’), 0)<>0 Delete From ##DeleteComputer " +_ "else CREATE TABLE ##DeleteComputer ([Computer_Id] [int] PRIMARY KEY NOT NULL)" objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete computer_Id " & strComputernumber) logMessage strErr, 1 End If On Error Goto 0 on Error Resume Next ‘Line 136 ‘********************************************************************************************************************* ‘ Display list of computers in Database ‘************************************************************************************* strSQL = "Select computer_id, [Name] from Computer" Set objRs = objConn.Execute(strSql) intCountObjects = 0 strInstruct = "If you do not want to delete any computers, enter 0 in the input box." + vbCrLF + vbCrLF + strInstruct strInstruct = "When the input box appears, enter the number." + vbCrLF + strInstruct strInstruct = "Record the Computer ID of the computer you wish to delete." + vbCrLF + strInstruct strInstruct = "Press to see the next set of 100 computers." + vbCrLF + strInstruct strInstruct = "A list containing the first 100 computers will appear." + vbCrLF + strInstruct wscript.echo strInstruct While Not objRs.EOF ‘Line 154 ‘********************** Display the computers in groups of 100 ******************* While (intCountObjects < strallcomputers =" strAllComputers" intcountobjects =" intCountObjects" intcountobjects =" 0" strallcomputers = " " strcomputernumber =" InputBox" objconn =" Nothing"> 0 Then ‘wscript.echo "Err = " + Err.Description ‘End If ‘************* Call the InsertIdValue function to insert value in the temp table *************** InsertIdValue objConn, strComputerNumber ‘****************** ‘Get table list with computer_id column******************* ‘Line 182 strSQL = "select DISTINCT name as TableName from sysobjects where xtype=’U’ and id in (select distinct id from syscolumns where name = ‘Computer_Id’)" Set objRecSet = objConn.Execute(strSQL) If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete Computer " & strComputernumber) logMessage strErr, 1 End If strSQL = "" If (objRecSet.State <> adStateClosed) And (Not objRecSet.EOF) Then While (Not objRecSet.EOF) strTableName = objRecSet.Fields.Item(0).value ‘Join temp table with table to delete joint rows strSQL = "delete [" + strTableName + "] from ##DeleteComputer, [" + strTableName + "] where ##DeleteComputer.Computer_Id = [" + strTableName + "].Computer_Id" ‘Line 199 objConn.Execute strSQL If (Err.number <> 0) Then strErr = GetErrorString(Err, "Failed to execute SQL to delete Computer " & strComputernumber) logMessage strErr, 1 End If objRecSet.MoveNext() Wend wscript.echo "Finished Deleting Computer " & strComputernumber wscript.quit End If objRecSet.Close Set objRecSet = Nothing ‘Line 217 End Function ‘Completed - 11/12/2004 by Ken Spinder
———————————————————————-
You can also download the attached file; just remember to remove the ‘.txt’ at the end of the filename.