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: Using the linked server functionality of SQL Server: |
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. |