Month: October 2022

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

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.

SQL Sample 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

VBScript – Adding Days and Months to a Date Using the DateAdd Function

VBScript has a built in function called “DateAdd” that specifically deals with adding or subtracting when dealing with a date/time variable.

To use it you will need to know the syntax and parameters that it needs to make the magic happen.

For my example I’m going to get the current date and time of the system by calling the “Now()” function which grabs the date and time from the PC the script is being run on.

To add month to today’s date: 

DateAdd("m",3, Now())

Since I wrote this code in March it should return a numeric “6” for June.  To spell out the month you would have to due some more work and maybe I’ll cover that in another post later.  The important thing to remember when working with dates and times using DateAdd, all your output will be numeric.

Calling the DateAdd Function in VBScript

The VBScript function, DateAdd, needs a couple of parameters separated by commas to make it work.

1. DateAdd needs to know the “Interval” parameter which is the part of the date your are wanting to work with.  In the sample above I used “m” to tell it that I want to add to the month. 

HINT: Don’t forget to wrap interval parameter text in double quotes.

2. DateAdd needs the number you want to add to the date and in my sample I used “3”. If you want to get a previous month then simply give DateAdd a negative number.

3. DateAdd needs to know what date you are working with. In my sample I used Now() to the current system date/time. You could have passed the date via a variable.

Where I had used the string value “m” to add to the month, you only have to change to interval parameter to change the year, month, day, hour, minute or second.  Here is a list of all the interval parameters you can pass to the DateAdd function:

  • yyyy – Year
  • q – Quarter
  • m – Month
  • y – Day of year
  • d – Day
  • w – Weekday
  • ww – Week of year
  • h – Hour
  • n – Minute
  • s – Second

I know for some people the DateAdd function is like preschool but for people who are new to VBScript I know it helps a lot if someone stops to explain the how and why of how this stuff works.

Good luck and happy coding!
Rick

Windows 7 Error: The user profile service failed the logon. User profile cannot be loaded

Background

I Came home today and tried logging in to my Windows 7 PC and got the following error: The user profile service failed the logon. User profile cannot be loaded.

I initially felt a little panic as this was the first time that Windows 7 had ever given my any real trouble.

Steps 1-15 Below Fixed My Windows 7 User Profile Issue

If there is another administrator account that you can log into, then jump to step 7 after logging into that account. However, if you can’t get to any accounts at all, then start at step 1.

1. Restart your computer and hit F8 multiples times until you see a menu-like screen, if you see the Windows splash screen then repeat this step
2. Highlight and hit enter on Safe Mode with Command Prompt. Try logging in there. If it still doesn’t work, then go to step17
3. If you are able to login, once a command prompt pops up, type: net user administrator password /active:yes (you can specify whatever password you want for the administrator account.)
4. If you get a message saying “The command completed successfully”, then restart your computer by typing: shutdown -r
5. Boot up again pressing F8, but this time choose just Safe Mode.
6. You will be able to login as Administrator with the password you set in Step 3
7. Hit (windows logo)+R
8. Type regedit
9. Once the registry editor opens up, look for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
10. In the left pane, find the one that starts with S-1-5….. and ends with .BAK; if you don’t find one, skip to step 15
11. Right click it and click Rename, then change the .BAK to .BK
12. Right click the one with the same numbering but without the .BAK and add .BAK add the end
13. Right click the one that you renamed to .BK and clickRename, delete the .BK
14. Eventually, you have switched the .BAK from the end of the second entry to the first. That should probably fix your problem.
15. If you didn’t find a .BAK then try this: Open Windows Explorer to C:\Users\Default\AppData\Local\Application Data
16. If you find another Application Data below the one you’re on, then delete it. That should probably also fix your problem.
17. If you can’t login in Safe Mode, then reboot and hit F8 until you see the menu again, then hit enter highlightingRepair Your Computer
18. Wait until all Windows Files finish loading, then hitNext when it asks keyboard language.
19. Try logging in here also, choose your user from the dropdown menu, and type the password, then jump to step 23
20. If that still doesn’t work, then if you have your Windows Installation CD still, put it in and restart your computer.
21. Hit F12 until you see a menu of boot options, choose Boot from CD
22. Choose keyboard language then hit next. Find Repair Your Computer from a little below the center left.
23. Choose Open Command Prompt and type: net user administrator password /active:yes (here again you can specify a password)
24. Close the command prompt and click Restart and jump to step 5

To disable the administrator account, type this in an elevated command prompt: net user administrator /active:no

Thanks to Roi A. for posting this on answers.microsoft.com.

PowerShell: Looping Through a List of Servers and Copying Files

I’m new to Windows PowerShell and have been wanting to learn it for some time now.  I finally had a situation come up the other day that I thought might be a good excuse to sit down and write my first PowerShell script.

The scenario:

I have three different servers running the same application.  There is application data on each server that needs to be backed up and stored in a folder named in a date format but it is not necessarily the date the script is run. 

The folder structure is exactly the same on each server.  I needed a script that could make a new folder to store the backed up data. 

I prompt the user to name the new folder defined on 3 different servers and copy files from another folder to this new folder, essentially creating a backup of data from a root folder to the new folder.

PowerShell Script Example: Looping through a list of servers

#  Script name:    Backup_Data.ps1
#  Created on:     2010-05-14
#  Author:         Rick Cable
#  Purpose:        Backup Files

#new folder name in date format mm-dd-yy
$NewFolderName = "" #Stores the name of the folder 
#Create array of Servers
$Servers = @("Server1","Server2","Server3")
$ServerName = @("Server 1 Description","Server 2 Description","Server 3 Description")

#Prompt user to enter the new folder name
$NewFolderName = read-Host "Enter the name of the new folder in date format mm-dd-yy"

#Function to process the files for each server in the array list.
Function Backup {

#Loop throug each of the servers
for ($i = 0; $i -le $Servers.length -1; $i++) 
{
write-Host $Servers[$i]
$BasePath = "\\" + $Servers[$i] + "\Share\"
$DonePath = $BasePath + "\Backup\" + $NewFolderName + "\"

if (test-Path $DonePath)  'if folder already exists
{        
write-Host "Copying files for" $ServerName[$i]
$FilePath = $BasePath + "*.dfm"
Copy-Item $FilePath $DonePath
write-Host "Copying log files"
$FilePath = $BasePath + "*.log"
Copy-Item $FilePath $DonePath
write-Host "Copying txt files"
$FilePath = $BasePath + "*.txt"
Copy-Item $FilePath $DonePath

write-Host "Files Copied"
} 
else
{
[IO.Directory]::CreateDirectory($DonePath)

write-Host "Copying files for" $ServerName[$i]
$FilePath = $BasePath + "*.dfm"
Copy-Item $FilePath $DonePath
write-Host "Copying log files"
$FilePath = $BasePath + "*.log"
Copy-Item $FilePath $DonePath
write-Host "Copying txt files"
$FilePath = $BasePath + "*.txt"
Copy-Item $FilePath $DonePath

write-Host "Folder Created and Files Copied"
}

}
}

#Run the function   
Backup


VBScript: Adding Leading Zeros to a Date

I was recently developing a script to loop through an excel file and write the contents in to a text file.  One of the specifications was that the output dates had to have leading zeros like 01/01/2021 but the excel file had them as 1/1/2021.

I thought there must a VBScript function like the built in CDate for or FormatDateTime but neither of these seemed to return dates with leading zeros so I wrote my own function to do it.

As in all programing, there are many ways to write this to get the same output but this way worked for me on the project I used it for.  

VBScript FixDate Function to fix leading zeros in a date value

Function FixDate(strDate)
Dim iTemp, arrDate, item, strTemp

 ' my custom date fix function
 ' split the date in to an array by the "/" character
 ' check each date item and check to see if it is less than 1000
 arrDate = Split(strDate,"/")
 for each item in arrDate
  if len(item) < 2 then
   item = "0" & item
  end if
  
  ' Next section makes sure there is no / at the end of the rebuilt date when I put it back together.
  if item < 100 then
   strTemp = strTemp & item & "/"
  else
   strTemp = strTemp & item
  end if
 Next
 ' Put the date back together
 FixDate = strTemp
 
End Function

How to call this function

FixDate("1/1/2011") 
 or 
FixDate(YourStringDate)