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 |