SQL Server – Cursor Example

25 11 2014

Cursors in SQL Server allow developers to iterate through a collection of values, normally based off a select statement. This is very useful for scripts that must apply to many items at once. More often than not that is iterating through database names to make changes to each database on an instance. Here is an example of how to iterate through all of the databases on an instance using cursors.


DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print @name;
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Advertisements




Query Scheduled Tasks

3 12 2009

Recently I was tasked to create a script to query all servers in our farm for their scheduled tasks information. Instinctively I opened up notepad and started writing a script to use WMI (Windows Management Instrumentation). Using CIM Studio I found the Win32_ScheduledJob class which at first glance looked like it was the one I needed. I ran the script only to get no data back and as a bonus no errors.  A quick Google search and a little reading and I discovered that the Win32_Scheduledjob class would only enumerate scheduled tasks that were created using the AT command. Since this is 2009 all scheduled tasks in our environment were created using the windows GUI. The best way to query these tasks is through the use of the schtasks.exe command. Fortunately the schtasks.exe allows for connection to systems so I could run the command locally and gather data from the servers. I set to work creating a script based on this and it worked out very well. A little Excel magic and I had the information I needed!


Const READ = 1

set objShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")

‘open a text file containing a list of all systems to query
Set objFile = objFSO.GetFile("servers.txt")
set objStream = objFile.OpenAsTextStream(READ, -2)

‘open a text file for the output
set objLog = objFSO.OpenTextFile("tasks.csv", 8, True)

‘Read the contents of the servers.txt file one line at a time
While not objStream.AtEndOfStream
    strServer = objStream.Readline

   ‘Run the schtasks.exe command against the server
    set objCMD = objShell.Exec("schtasks.exe /Query /S " & strServer & " /FO CSV /NH /V")
   
    ‘capture the output and write it to tasks.csv
    objLog.WriteLine objCMD.stdOut.ReadAll()
Wend

‘Let me know when you are done doing my bidding
msgbox("complete")





VB Script to Modify Registry Keys

3 04 2009

'Script adds or modifies existing values and paths to the windows registry.
'By Christopher Provolt

Const REG_SZ = "REG_SZ"
Const REG_DWORD = "REG_DWORD"
dim objShell

Set objShell = WScript.CreateObject("WScript.Shell")

UpdateRegKey "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\CCM\HttpPort", "50", REG_DWORD

Sub UpdateRegKey(strRegPath, strValue, strType)
objShell.RegWrite strRegPath, strValue, strType
End Sub

UpdateRegKey Function Input
'Registry Path,"Value","Type"

'DWORD values should be converted from DEC to HEX