|
I hope that you have all had a great festive season and that you had an opportunity at some stage to put your feet up and relax, even if it was only for a few days, as 2006 is looking to be a ballistic year! For those followers of SQL Server Central, you may have noticed a familiar name on two recent SQL Server articles. I have written two articles that have been published on the SQL Server Central website (http://www.sqlservercentral.com) and in the SQL Server Central newsletter. Male, Female and The Other One (NULL) details the concept of three-valued logical expressions in SQL Server http://www.sqlservercentral.com/columnists/pward/malefemaleandtheotheronenull.asp. SED and the Big Bad UNIX File details a scenario I recently encountered when importing a UNIX file using the Import/Export Data Transformation Wizard http://www.sqlservercentral.com/columnists/pward/sedandthebigbadunixfile.asp. Subsequent to this article I have also written a UnixToDos utility to convert the newline character for a single file or all files in a directory. The utility can be downloaded from the development page on the WARDY IT solutions website http://www.wardyit.com. On the 20th of March 2006, we will be running the one day course SQL Server Administration Essentials in Brisbane. This course is designed to assist individuals to develop the confidence and skills required to administer a SQL Server environment to meet the constant demands for reduced application downtime and tighter security controls. This course is perfect for those who have recently started working with SQL Server or are looking to brush up their administration skills. If you require any additional details regarding this course then do not hesitate to contact us. If you are looking for additional SQL Server information between the WARDY IT newsletters then don’t forget to subscribe to the RSS feed for our SQL Server Blog http://www.wardyit.com/blog. Happy Reading Peter Ward WARDY IT Solutions Chief Technical Architect Phone: +61 7 3856 5411 Mobile: +61 0 403 177 761 |
|
In this Issue: SQL Agent Mail SQL Serve 2005 Upgrade Advisor Sorting SQL Project Files in SQL Server Management Studio PSS Service Centre Labs—2005 SQL Server 2005 System Views Map SQL Server 2005 Sample Chapters SQL Server 2005 Database Mirroring SQL Server 2005 Australian Product Launch Comparing Tables in SQL Server 2005 T-SQL of the Month SQL Server Tip of the Month |
|
SQL Agent Mail SQL Agent Mail can be used to send alert notification to an operator when a job fails or when a certain error occurs. However, a lot of organisations have favoured xp_smtp_sendmail as a replacement for the MAPI based xp_sendmail which ships with SQL Server 7 and 2000. One of the issues with using xp_smtp_sendmail is that although it can send SMTP email, it does not alert an operator on the failure of a job without adding additional Job Steps. Tibor Karaszi has developed a free utility which can be used to send mail messages to an operator utilising xp_smtp_sendmail. To download this utility refer to http://www.karaszi.com/SQLServer/util_smtp_alerter.asp. SQL Server 2005 Upgrade Advisor A new version (January 2006) of the SQL Server 2005 Upgrade Advisor has been released. The upgrade advisor can analyse a SQL Server 7 or 2000 instance in preparation for upgrading to SQL Server 2005. The Upgrade Advisor will identify any feature or configuration changes that might affect the upgrade to SQL Server 2005. The Upgrade Advisor also provides links to documentation detailing how each of the identified issues can be resolved. The SQL Server 2005 Upgrade Advisor can be downloaded from Sorting SQL Project Files in SQL Server Management Studio Last month we mentioned the update that Jasper Smith had made to Reporting Scripter (http://www.sqldbatips.com/showarticle.asp?ID=62) and this month we are highlighting another one of the great utilities that Jasper has developed as a solution to the way that files are sorted in a project when using SQL Server Management Studio. The files in a project are displayed in the Solution Explorer in the order they were added to the project. The utility that Jasper has developed allows you to add menu items to sort the SQL files in a project so that they are easier to locate. The utility can be downloaded from http://www.sqldbatips.com/showarticle.asp?ID=78. PSS Service Centre Labs - 2005 Microsoft SQL Product Support Services (PSS) has developed a series of self-paced labs for the SQL PASS 2005 Conference. The labs are designed to illustrate troubleshooting methods and techniques that can be used to solve common problems with SQL Server 2005. The labs can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=9b8098a7-e75f-462a-b296-e80199c9f323&displaylang=en. SQL Server 2005 System Views Map A common question that has been posted in the SQL server Newsgroups of late asks ‘is there a System View map for all of the new Dynamic Management Views?’ Microsoft has recently released a PDF that shows not only the Dynamic Management Views (DMV) but also the relationships between the system tables and DMV’s. The PDF can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en. SQL Server 2005 Sample Chapters If you walk into a bookshop at the moment there are only a handful of books available on SQL Server 2005 and most of these are based on pre-release versions of the product. However, there is a flood of new books about to be released. The following Blog article contains a list of many of the books that will be released shortly as well as sample chapters from each of the books http://blog.pinpub.com/archives/sqlblog/2006/01/more_sample_cha.html. SQL Server 2005 Database Mirroring Although Database Mirroring is disabled by default in SQL Server 2005 and Microsoft’s support policies do not apply to the database mirroring feature in SQL Server 2005, there is a great deal of interest in the feature. If you would like to evaluate this high availability feature in a test environment the following article documents the setup steps http://msdn2.microsoft.com/en-us/library/ms190941.aspx. SQL Server 2005 Australian Product Launch If you missed the SQL Server 2005 Product launch events that were conducted around Australia in November and December last year then you missed some great SQL Server presentations. The following SQL Server launch presentations were recorded and are now available as webcasts: Building Mission-Critical Systems, Architecting Scalable, Flexible and Secure Database Systems, Delivering Business Insight and Managing and Monitoring the New Data Platform Created by SQL Server 2005 and Visual Studio Team System 2005. The webcasts can be downloaded from http://go.microsoft.com/?linkid=4421083. Comparing Tables in SQL Server 2005 Tablediff is a new utility that has been introduced with SQL Server 2005 which has been created to assist with troubleshooting replication by comparing the data in two tables for non-convergence. Tablediff has several modes of operation including full data comparison and the ability to perform a fast comparison by only comparing row counts and the schema between two tables. The Tablediff utility can be found in the Program Files\Microsoft SQL Server\90\COM directory. For more details refer to http://msdn2.microsoft.com/en-us/library/ms162843.aspx T-SQL of the Month: This months T-SQL of the month continues on from last months article by illustrating another common query structure that can easily be modified to improve the performance of the query. A common scenario is the need to perform a DATE function in a WHERE clause that limits the orders to those that have been placed in the last week. When a function is used in a WHERE clause, the optimizer cannot always select an index that is used on columns in a WHERE clause that are inside a function. As these columns are seen as expressions rather than a column by the optimizer. In order for the optimizer to be able to select an index and improve the performance of the query, the DATE function can be re-written using a literal expression. As can be seen in the example below an Index Scan is performed as a result of the DATEADD function. However, by moving the function to the other side of the WHERE equation, an index can be used on the datetime column: -- 1) A scan is perfromed even though there is an index on the OrderDate column SELECT orderid FROM northwind.dbo.orders WHERE DATEADD(DAY, 7, orderdate) = '23 july 1996' Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0 -- 2) By moving the function to the otherside of the WHERE equation an index can be used -- on the OrderDate column. The use of the function is replaced by a literal -- expression. SELECT orderid FROM northwind.dbo.orders WHERE orderdate = DATEADD(DAY, -7, '23 july 1996') Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0 SQL Server Tip of the Month If you have upgraded to SQL Server 2005 and have been pulling your hair out trying to locate where the Import/Export wizard is then you are not alone. As you have probably already noticed, the Import/Export wizard is no longer reached from the Start Menu. Instead an Integration Services Project must be started from SQL Server Business Intelligence Development Studio. You are then able to right click the Integration Services Packages node in Solution Explorer to launch the Import/Export wizard. A far more direct approach is to simply run dtswizard.exe from Start|Run or the command prompt. |
|
If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/jan2006.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. |
