Touring Italy

I will be on leave from the 30th of March until the 26th of April. During this time I will be touring Italy with my wonderful wife Jay Dee.

Unfortunately as a result there will no SQL Server newsletter for March and no blog posts until I return from leave.

Should you require support from WARDY IT Solutions during this time then email support @ wardyit.com.

QLD SQL Server User Group – SMO Presentation

I will be attending the Queensland SQL Server User Group on Thursday the 27th April 2006 to present on the SQL Management Object (SMO).  Below is a synopsis for the presentation as well as the details of where the presentation will be held.

For more details regarding the Queensland SQL Server User Group and refer to http://www.qssug.org/.

Presentation Synopsis:
Do you find that day-to-day in your job you are ‘fire-fighting’ issues that could have been prevented if you had the time to perform proactive monitoring of the SQL Server instances in your enterprise; Or maybe that you would like to implement an enterprise management solution but are restricted by budget constraints?  In this session, Peter Ward from WARDY IT Solutions (www.wardyit.com) will introduce the SQL Management Object (SMO) and how applications and scripts can use this object to access the management features of SQL Server 2005.  In this session Peter will demonstrate how SMO can be applied in the enterprise to assist in automating common administrative tasks as well as to extend the functionality of SQL Server.

Location:
Microsoft Brisbane Office
Level 9
Waterfront Place
1 Eagle St
Brisbane
Thursday, 27th April 2006, 18:00-19:30

Generating a Calender Table

Often the use of an auxiliary table can assist you to write more efficient code than a solution that doesn’t. Auxiliary tables are great at solving problems that you may be tempted to use a cursor to solve in the place of a set based solution. The following example shows how to generate an auxiliary numstable and then use this table to generate a calendar table.  This task is one that is often performed as a row-by-row operation in a cursor as apposed to using a set based solution.

— ~~~ START Generate Auxiliary Table ~~~
CREATE TABLE dbo.Digits (i INTEGER NOT NULL PRIMARY KEY)
INSERT dbo.Digits SELECT 0
INSERT dbo.Digits SELECT 1
INSERT dbo.Digits SELECT 2
INSERT dbo.Digits SELECT 3
INSERT dbo.Digits SELECT 4
INSERT dbo.Digits SELECT 5
INSERT dbo.Digits SELECT 6
INSERT dbo.Digits SELECT 7
INSERT dbo.Digits SELECT 8
INSERT dbo.Digits SELECT 9

CREATE TABLE dbo.Nums (seq INTEGER NOT NULL PRIMARY KEY)
INSERT INTO dbo.Nums
SELECT (10000 * D5.i) + (1000 * D4.i) + (100 * D3.i) + (10 * D2.i) + D1.i
FROM  Digits AS D1, Digits AS D2, Digits AS D3, Digits AS D4, Digits AS D5
WHERE  ((10000 * D5.i) + (1000 * D4.i) + (100 * D3.i) + (10 * D2.i) + D1.i) > 0
— ~~~ END Generate Auxiliary Table ~~~

CREATE TABLE dbo.Calendar (dt DATETIME PRIMARY KEY)
INSERT  INTO dbo.Calendar
SELECT  DATEADD(DAY, seq, ‘20051231’)
FROM  dbo.Nums
WHERE  DATEADD(DAY, seq, ‘20051231’) <= ‘20061231’

SQL Server 2005 SP1 CTP

The Community Technology Preview (CTP) of SQL Server 2005 Service Pack 1 is now available for download from http://www.microsoft.com/sql/ctp_sp1.mspx. As well as providing fixes and enhancements SP1 also delivers new functionality for database mirroring as well as SQL Server Management Studio for SQL Server Express.  One thing to note is that the CTP license agreement precludes deployment of the SQL Server 2005 SP1 CTP into production.

RMO Example

A question that was asked during the SMO presentation in Adelaide last week was how to use the Replication Management Object (RMO) in SQL Server 2005 to programmatically control replication. The VB.Net snippet below configures the replication publisher and distribution.

~~~

Dim distributionDBName As String = “distribution”
Dim publisherDBName As String = “northwind”
Dim distributionDB As DistributionDatabase
Dim distributor As ReplicationServer
Dim publisher As DistributionPublisher
Dim SMOServer As ServerConnection = New ServerConnection()

‘ Define the distribution database
distributionDB = New DistributionDatabase(distributionDBName, SMOServer)
distributionDB.MaxDistributionRetention = 30
distributionDB.HistoryRetention = 60

‘ Install the distributor and create the distribution db
distributor = New ReplicationServer(SMOServer)
distributor.InstallDistributor((CType(Nothing, String)), distributionDB)

‘ Install the Publisher
publisher = New DistributionPublisher(publisherDBName, SMOServer)
publisher.DistributionDatabase = distributionDB.Name
publisher.WorkingDirectory = “\\” + publisherDBName + “\repldata”
publisher.PublisherSecurity.WindowsAuthentication = True
publisher.Create()

~~~

Camtasia Movie of SQL SMO Presentation

The SQL Server Automation & SQL SMO presentation that I performed on Thursday the 9th March in Adelaide for the Adelaide SQL Server User Group was recorded using Camtasia (http://www.techsmith.com/camtasia.asp). The wmv produced recorded the entire presentation including the audio, so if you missed the presentation it is available for download from http://www.wardyit.com/presentations/WARDYITSolutionsAdelaideSQLSMO.wmv. The wmv is approximately 49mb. Camtasia records the entire screen so the PowerPoint deck and code examples are all available to be viewed so it is just like being at the presentation.

DMO and SMO AutoShrink

The following SQLDMO and SMO VBScript code snippets can be used to check each database on a SQL Server instance and identify any databases where the AutoShrink database option is enabled.  Note the DBOptions Class in SQLDMO has been moved to DatabaseOptions in SMO.

~~~ DMO Example ~~~

Dim db
Dim oDMOServer

Set oDMOServer = CreateObject(“SQLDMO.SQLServer”)

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

For Each db In oDMOServer.Databases
If db.DBOption.AutoShrink = True Then
MsgBox db.Name & ” – Auto Shrink Enabled”
End If
Next

oDMOServer.DisConnect
Set oDMOServer = Nothing

MsgBox “Done”

~~~

~~~ SMO Example ~~~

Dim db
Dim oSMOServer

Set oSMOServer = CreateObject(“Microsoft.SQLServer.Management.SMO.Server”)

For Each db In oSMOServer.Databases
If db.DatabaseOptions.AutoShrink = True Then
MsgBox db.Name & ” – Auto Shrink Enabled”
End If
Next

Set oSMOServer = Nothing

MsgBox “Done”

~~~