SQLDMO Script SQL Server Jobs

The following VBScript code snippet can be used to script all SQL Server Jobs on a SQL Server instance:

~~

Const ForWriting = 2

Dim job
Dim oFSO
Dim oFSOWrite
Dim oSQLServer

Set oFSO = CreateObject(“Scripting.FileSystemObject”)
Set oSQLServer = CreateObject(“SQLDMO.SQLServer”)

oSQLServer.LoginSecure = True
oSQLServer.Connect “(local)”

Set oFSOWrite = oFSO.OpenTextFile(“c:\sqljobs.sql”, ForWriting, True)

For Each job in oSQLServer.JobServer.Jobs
oFSOWrite.WriteLine job.script
Next

oFSOWrite.Close

Set oFSO = Nothing

~~

Scan an IP Range for SQL Server Instances

There are other tools such as SQLPing (http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26) that can be used to scan an IP address range for SQL Server instances. However if you need to also accurately determine the build of each SQL Server instances including MSDE instances to ensure that the latest service pack has been applied then the SQL Server 2000 SQL Scan Tool is a the tool for you. Although a little slow scanning a large range of addresses it generates a CSV file with the IP Address and the Product Level and OS information along with additional build information.  SQL Server 2000 SQL Scan Tool was originally released by Microsoft to assist with determining machines that were vulnerable to the ‘Slammer’ worm and can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=9552D43B-04EB-4AF9-9E24-6CDE4D933600&displaylang=en.

SQL Wish Email Alias

For as long as I can remember the sqlwish@microosft alias has been used for submitting features and ideas to the Microsoft SQL Server team.  However I noticed that Greg Low has a blog post http://msmvps.com/blogs/greglow/archive/2005/12/14/79146.aspx that the email alias is being deprecated and that SQL Server feedback should now be posted to http://lab.msdn.microsoft.com/productfeedback.

Creating a Deadlock

Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:

— 1) Create Objects for Deadlock Example
USE TEMPDB

CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1

— 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1

— 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1

— 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1

Connection two will be chosen as the deadlock victim

ie.

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.