There has recently been a great deal of interest in the power that a little bit of scripting and SQL-DMO can bring to a Database Administrator’s toolkit.  As a result of this interest, on Friday 31st September I will be presenting a one day course titled ‘A DBA’s Guide to SQL-DMO’ at Clifton's training facility in Brisbane.  If you are interested in this course you will need to book early as bookings are limited to twelve participants and places have already begun to fill.  For those who book and pay prior to the 29th July 2005  there is an early bird rate of $450 inc. GST per person.  The course material is currently being finalised and as soon as an outline is available I will post it to the WARDY IT blog http://www.wardyit.com/blog, as well as include it in next months newsletter.  For more information regarding this course or the running of this course in other locations e-mail courses@wardyit.com.

 

SQL Server 2000 Service Pack 4 was released on the 12th May, but before you install the Service Pack you may want to be aware of a bug that has been found affecting servers where Address Windowing Extensions (AWE) is enabled.  The bug limits the amount of memory that can be used by SQL Server to 50 percent of the physical memory that is on the server.  KB Article 899761 (http://support.microsoft.com/kb/899761) provides more information regarding this issue. 

 

 

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

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

Phone: +61 7 3856 5411     Mobile: +61 0 403 177 761

In this Issue:

 

Tech.Ed 2005

Greg Low’s CLR Book

April MSDN Updae

SQL Server to Oracle using SSIS

Knowledge Base RSS Feed

SQL Server POD Casts

RegExp Function in SQL Server

SQL Server 2005 MSDN Forum

Looping Through Routines

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

Reporting Services Tip of the Month

Tech.Ed 2005

Registration is now open for Tech.Ed 2005.  Tech.Ed Australia will be held this year at the Gold Coast convention centre from the 30th August to 2nd September.  The Early Bird registration is available until the 30th June for $1399 ex. GST.  For MSDN member’s registration is available for $1199 ex. GST up until the commencement of the event. http://www.microsoft.com/australia/events/teched2005/default.aspx

 

 

Greg Low’s CLR Book

Greg is in the final stages of his book ‘A SQL Server DBA's Guide to the .NET Framework and CLR Integration’.  Greg is a leading figure in the SQL Server and .Net community and for those who attended the recent Security Summit or the MSDN Update you would have seen Greg’s presentation on the CLR integration in SQL Server 2005.  To register to be advised when this book is available and to secure it at the pre-release price visit http://www.sqldbaguide.com/

 

 

April MSDN Update

For those who were unable to attend the April MSDN Update session or would like to review the presentations from Neil Roodyn, Greg Low and Dave Glover the presentation material is now available at http://www.microsoft.com/australia/msdn/training/aprilupdate.aspx

 

 

SQL Server to Oracle using SSIS

Donald Farmer has produced a great article regarding migration, integration and loading from and to an Oracle Database using SQL Server 2005 Integration Services (the new DTS).

http://sqljunkies.com/WebLog/donald%20farmer/archive/2005/03/13/8819.aspx

 

 

Knowledge Base RSS Feed

Microsoft Knowledge Base articles are now available as an RSS feed so that as new KB articles are published you will receive notification.  For the complete list of feeds available refer to

http://support.microsoft.com/selectindex/?target=rss

 

 

SQL Server POD Casts

If you are looking for something to load onto your digital music device to listen to in the background at work or whilst on the train then you can’t go past some of the great SQL Server resources available to help bring you up to speed.  http://www.dotnetrocks.com has recently had Kimberly Tripp, Brian Larson and Tony Bain on their weekly show to discuss SQL Server, whilst Bryan Von Axelson from Microsoft has just finished a 10 Part series on SQL Server 2005 available from

http://www.microsoft.com/events/series/technetsqlserver2005.mspx.

 

 

RegExp Function in SQL Server

For those who are used to working with other programming languages one of the things that often seems primitive about TSQL is the string searching and pattern matching functions available.  Regular Expressions (RegExp) are a special text string for describing a search pattern used for searchings strings, much like the LIKE statement only on steroids.  Ken Henderson has come up with a way to use the power of Regular Expressions by using a User Defined Function.

http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx

 

 

SQL Server 20005 MSDN Forum

Microsoft have added the option of support of the SQL Server 2005 CTP program via a Web Based forum to compliment the traditional News Groups.

http://forums.microsoft.com/msdn/default.aspx?ForumGroupID=19

 

 

Looping Through Routines

I am sure many of you have used the sp_MSforeachtable and sp_MSforeachdb procedures to loop over each non-Microsoft shipped table in the current database, or each database on the current instance and run a command against the object ie. EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'.  Adam Machanic has written a procedure based on these existing procedures to enable the ability to loop through each routine. http://sqljunkies.com/WebLog/amachanic/articles/sp_foreachroutine.aspx

 

 

 

DMO of the Month:

 

Often there is a need to present a spreadsheet containing the size and space available for each database in a SQL Server instance.  The following SQL-DMO script will assist with this by creating a comma delimited text file (which can be imported into Excel) containing the size and available space for the data and log files for each database.

 

Const OUTPUT = "c:\output.txt"

Const ForWriting = 2

 

Dim oFSO

Dim oFSOWrite

Dim oSQLServer

 

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

Set oFSO = CreateObject("Scripting.FileSystemObject")

 

Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True)

 

oSQLServer.LoginSecure = True

oSQLServer.Connect "(local)"

 

For Each DB In oSQLServer.Databases

      oFSOWrite.WriteLine DB.Name & ", data, " & DB.Size & ", " & _

            Round(DB.SpaceAvailableinMB, 2)

      oFSOWrite.WriteLine DB.Name & ", log, " & DB.TransactionLog.Size & ", " & _

            Round(DB.TransactionLog.SpaceAvailableInMB, 2)

Next

 

oFSOWrite.Close

 

oSQLServer.DisConnect

Set oSQLServer = Nothing

 

 

T-SQL of the Month:

 

As Database Administrators or Developers much of our day is spent within Query Analyser.  However unlike the Windows shortcut keys many people have not found the time to identify the shortcut keys in SQL Server and Query Analyser.  If you have ever wondered what the keyboard icon at the top of SQL Server Books online is, well it is a link to the SQL Server shortcut keys.  Although these shortcuts will not help your queries run any quicker hopefully the time these shortcuts save can provide more time to spend on additional performance optimisation and testing.  For a list of the Shortcuts Keys is Query Analyser refer to

http://www.extremeexperts.com/SQL/Tips/ShortCutKeys.aspx

 

 

For those who had the opportunity to attend Itzik Ben-Gan’s SQL Server logic session you may have noticed Itzik creating and populating a numbers table  (1, 2, 3….n) very quickly.  Typically to populate a numbers table you would use code similar to this:

 

WHILE @i < @bignumber

    BEGIN

       INSERT INTO sometable SELECT @i

       SET @i = @i +1

    END

 

Below is a code sample based upon the one used by Itzik that populate a numbers table with 10, 000 records with only 15 insert statements:

 

CREATE TABLE Nums

    (

      i INT NOT NULL PRIMARY KEY

    )

 

SET NOCOUNT OFF

 

DECLARE @maxVal BIGINT

DECLARE @i BIGINT

 

SET @maxVal = 10000

SET @i = 1

 

BEGIN TRAN

    INSERT INTO Nums VALUES(1)

   

    WHILE @i * 2 <= @maxVal

      BEGIN

          INSERT INTO nums

               SELECT i + @i FROM nums

          SET @i = @i * 2

    END

 

    INSERT INTO Nums

    SELECT i + @i FROM Nums

    WHERE i + @i <= @maxVal

COMMIT TRAN

 

 

SQL Server Tip of the Month:

 

If you have ever had the need to determine when the last backup occurred for each database, and the location where the backup was performed then the script below will allow you to do this.  You could potentially schedule this script to run as part of a job and alert you to any backups that have not been performed in the last 24 hours.

 

SELECT    s.name, bf.physical_device_name AS last_backup_path, bs2.last_backup

FROM       msdb.dbo.backupset bs JOIN msdb.dbo.backupmediaset ms

ON          bs.media_set_id = ms.media_set_id

JOIN       msdb.dbo.backupmediafamily bf

ON          ms.media_set_id = bf.media_set_id

JOIN       (SELECT database_name, MAX(backup_finish_date) AS last_backup

            FROM   msdb.dbo.backupset

            GROUP BY database_name) bs2

ON          bs.database_name = bs2.database_name AND bs.backup_finish_date = bs2.last_backup

RIGHT JOIN master.dbo.sysdatabases s

ON          bs.database_name = s.name

ORDER by   s.name

 

 

Reporting Services Tip of the Month:

 

A question that crops up from time to time with SQL Server 2000 Reporting Services is 'how do I dynamically change the data source used by a report - from within a report?'.  To do this is a straight forward task in SQL Server 2005 Reporting Services, but not so with SQL 2000 Reporting Services.  Below are few suggestions on how this can be achieved using Reporting Services  2005:

 

Using  a custom data processing extension

http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp

 

Use the SOAP API:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp

 

Using the linked server functionality of SQL Server:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us

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

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 relationship 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.  WARDY IT Solutions has also recently partnered with Web Central in order to add web hosting and DSL plans backed by guaranteed Service Levels to the extensive list of services that we already provide.

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.