Welcome to the first WARDY IT Solutions monthly newsletter.  This newsletter is designed as a medium to keep in contact with our valuable associates and clients as well as to provide invaluable SQL Server information and resources.  Each month we will endeavour to provide a list of articles and code snippets that we have identified in the previous mouth that are of interest to the SQL Server community or that we have come across with clients in the field.

 

Should you have any suggestions or feedback on how we can improve the newsletter, or content that you would like covered than do not hesitate to email newsletter@wardyit.com.

 

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

Phone: +61 7 3312 0115      Mobile: +61 0 403 177 761

In this Issue:

 

QLD SQL Server User Group

Microsoft Knowledge Base Alerts

SQL Server 2005 Hands on Labs

Comparing SQL Server 2005 and IBM DB2 v8.2

A first Look at SQL Server 2005 for Developers

FxCop 1.312 Adds Check for SQL Injection

DMO of the Month

T-SQL of the Month

SQL Server Tip of the mouth

QLD SQL Server User Group Presentation:

Last mouth WARRDY IT Solutions presented at the Queensland SQL Server User Group (SIG) at Microsoft’s Brisbane office on SQL Server automation with SQL-DMO and VBScript.  The PowerPoint deck and scripts created during the presentation can be downloaded from http://www.wardyit.com/files/oct2004sig.zip.

 

You can register your interest in future SQL Server user groups and events at http://www.sqlserver.org.au/.

 

 

Microsoft Knowledge Base Alerts:

If you have ever wanted to be notified when a new Microsoft Knowledge Base article is released for SQL Server than KBAlertz (www.kbalertz.com) is a free service that will automatically e-mail new knowledge base articles as they are released for a particular technology.

 

2005 Hands on Labs

SQL Server 2005 Hands on Labs:

Microsoft have released a series of hands on labs that can be downloaded for use in conjunction with SQL Server 2005 Beta 2 from http://msdn.microsoft.com/SQL/Labs/default.aspx.

 

 

Comparing SQL Server 2005 and IBM DB2 v8.2

IBM’s upcoming release of Universal Database for DB2 (aka Stinger) is competing directly with SQL Server 2005’s Common Language Runtime (CLR) Integration by allowing .Net assemblies to be incorporated as Stored Procedures.  This study compares the core technologies used by both DB2 and SQL server 2005 and identifies the differences in performance and manageability of the two platforms. 

http://www.microsoft.com/sql/evaluation/compare/ibm/db4developers.asp

 

 

A first Look at SQL Server 2005 for Developers

If you are a SQL Server developer wondering what SQL Server 2005 (aka Yukon) has to offer you than this is the book for you.  The book provides a basic introduction to a large number of the SQL Server 2005 features including .NET and CLR integration as well as Notification Services, Service Broker and Reporting Services.

http://www.amazon.com/exec/obidos/ASIN/0321180593/exploresql-20/102-1513019-1290518

 

 

FxCop 1.312 Adds Check for SQL Injection

The FxCop tool from Microsoft, which scans .NET assemblies for various development flaws, has added a check for SQL Injection flaws in its security checks. This check will determine if a SQL command was assembled using the appropriate SqlCommand object or via string-building.

http://www.gotdotnet.com/team/fxcop

 

 

 

DMO of the Month:

 

The VBSript code snippet below will connect to a specified SQL Server Instance and display as the Recovery Model being used for each database.  The script is designed to be run on SQL Server 2000.

 

Dim oSQLServer

Dim oDatabase

 

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

Set oDatabase = CreateObject("SQLDMO.Database")

 

oSQLServer.LoginSecure = True ' True = use Windows authentication, False = SQL Server authentication

 

' Replace (local) with the SQL Server Instance to connect to

' To use SQL Server authentication the connection will be

' oSQLServer.Connect(servername, SQLServerUsername, SQLServerPassword)

oSQLServer.Connect("WDHXJGC1S\TEST")

 

' Loop through each database belonging to the selected instance

' and display a message box with the Database name and recovery

' model being used

For Each oDatabase, In oSQLServer.Databases

 

      Select Case oDatabase.DBOption.RecoveryModel

            Case 1 model = "Bulk Logged"

            Case 2 model = "Full"

            Case 0 model = "Simple"

            Case Else model = "Unknown"

      End Select

                 

      MsgBox oDatabase.Name & " : " & model

Next

 

oSQLServer.DisConnect()

 

Set oSQLServer = Nothing

Set oDatabase = Nothing

 

 

T-SQL of the Month:

I am sure many of you are aware of the ISNULL T-SQL Function that replaces a NULL value with a specified value.

 

ie.

 

SELECT ISNULL(NULL, 'ABC') AS ISNULL_TEST

 

Returns:

 

ISNULL_TEST

-----------

ABC

 

(1 row(s) affected)

 

But you may not be aware of NULLIF function.  This function is used to compare if two values are equivalent and return NULL when they match:

 

SELECT NULLIF('ABC', 'ABC') AS NULLIF_TEST

 

Returns:

 

NULLIF_TEST

-----------

NULL

 

(1 row(s) affected)

 

If the values do not match than the first (left) expression is returned

 

SELECT NULLIF('ABC', 'CBA') AS NULLIF_TEST

 

Returns:

 

NULLIF_TEST

-----------

ABC

 

(1 row(s) affected)

 

 

SQL Server Tip of the mouth:

Have you ever watched a transaction with a rollback status and wondered how long it will take to finish rolling back.  The KILL command is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources.  However by running the KILL command with the SPID  (Server Process ID) and WITH STATUSONLY (KILL spid WITH STATUSONLY) the estimated percentage of the roll back complete and remaining duration will be returned.

 

ie.

 

KILL 52 WITH STATUSONLY

 

Returns

 

SPID 52: transaction rollback in progress. Estimated rollback completion: 20%. Estimated time remaining: 8 seconds.

 

WARDY IT Solutions is a highly regarded IT Solutions provider specialising in Microsoft SQL Server.  You may not be aware though that WARDY IT Solutions is also a registered Microsoft Partner and has an established relationships with numerous hardware and software vendors, enabling us to provide competitive pricing on your next hardware or software acquisition.  For more information on how WARDY IT Solutions can assist with your next acquisition email: contact@wardyit.com.

You are receiving this monthly newsletter as a valuable associate of WARDY IT Solutions. Should you wish to unsubscribe then send a blank e-mail to newsletter_remove@wardyit.com. Alternatively if you have been forwarded this newsletter and would like to subscribe than send a blank email to newsletter_add@wardyit.com.

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/nov2004.htm