WARDY IT Solutions Jan 2005 Newsletter

Thanks to all of you who provided such positive feedback regarding the inaugural newsletter, as it was great to hear that so many of you found the information valuable. As a result of WARDY IT moving offices, the December issue needed to be postponed until January. However, I would still like to take this opportunity to thank all of our clients for their continued business and all of our associates for their support throughout 2004. We look forward to continuing to forge these relationships this year.

As a result of our move the new contact details for WARDY IT Solutions are:

Phone: +61 7 3856 5411

Fax: +61 7 3114 5422

Postal: Unit 4/70 Fifth Avenue, Windsor QLD, Australia 4051.

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:

Generate Inserts

Database Daily

SQL Server 2005 Beta 2 Resource Kit

Difference between SELECT and SET

Stored Procedure Error Handling

SQL Server 2005 Express Manager - CTP

SQL Server 2000 Report Pack for Exchange

Microsoft Security Summit 2005

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

Generate Inserts

Many of you have found the sp_generate_inserts Stored Procedure created by Narayana Vyas Kondreddi (http://vyaskn.tripod.com/) an invaluable tool in order to script data out of a table as a series of insert statements. John Bates from CodeHQ.net (www.codehq.net) has ported the script as a VBScript and improved on some of the limitations of the Stored Procedure, most notably the handling of binary, varbinary and image datatypes. Meaning that BLOB data can now easily be scripted as an insert statement. The script can be downloaded from http://codehq.net/files/GenerateSqlInserts.zip.

Database Daily

If you are looking for a daily roundup of articles related to SQL server then www.databasedaily.com is the site for you. The site collates articles from various sources on a daily basis summarising them in one convenient location. They also have a weekly e-mail newsletter that you can subscribe to which summarises all the key articles in the previous week.

SQL Server 2005 Beta 2 Resource Kit

Terry Clancy, the SQL Server Product Manager in Australia has advised that the SQL Server 2005 Beta 2 Resource Kit is available in Australia. The kit contains SQL Server 2005 Beta 2 Developer Edition, Beta 2 Resource DVD and Beta 2 Hands-On Labs DVD. You can order a free copy of the kit from

http://msstore.datacom.com.au/sqlbeta.

Difference between SELECT and SET

Most SQL Server developers are aware of the ability to assign a variable using either SET or SELECT since SQL Server version 7.0. This article highlights the major differences between each of these statements as well as a few caveats you should be aware of when using either SET or SELECT. The article concludes with an example of when each statement should be used for variable assignment and how performance is effected by the choice of assignment statement. http://vyaskn.tripod.com/differences_between_set_and_select.htm.

Stored Procedure Error Handling

One question that is often asked is how to error handle inside a Stored Procedure and does it really have to be such a tedious task. Unfortunately TSQL does not have the ability to use a Global branch to a line label statement such as the Visual Basic On Error Goto. Although this will be improved by the ability to use a Try…Catch statement in SQL Server 2005, this article is still worth reading http://www.sommarskog.se/error-handling-II.html.

SQL Server 2005 Express Manager - CTP

The December Community Technology Preview (CTP) of Microsoft SQL Server 2005 Express Manager has been released and is available for download from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=8F92556A-6C3B-47D2-9929-ECDC5A4D25AE&displaylang=en. SQL Server 2005 Express Manager is the management tool for SQL Server Express.

SQL Server 2000 Report Pack for Exchange

SSW (http://www.ssw.com.au) has produced a set of Microsoft Exchange-based reports using Reporting Services that allows you to author managed reports for e-mail administration. Using these reports you can easily report against your Exchange Servers on things such as who has a mailbox over a certain size and who is sending the most mail internally The Report pack are available for download from

http://www.microsoft.com/downloads/details.aspx?familyid=108c2b01-2cc9-4a84-a669-eb22533fa5e2&displaylang=en

Microsoft Security Summit 2005

This summit is the third in what has become a yearly event to provide an overview of the latest technologies available to address the complex problem of security. On the agenda for this summit is several tracks, but of particular interest will be the following sessions: SQL Server 2005 Overview, SQL Server 2005 Administration, Scalability and Reliability, Introduction to Programming with SQL Server 2005. The following link can be used to register for this event in early 2005 http://www.microsoft.com/australia/security/summit/.

DMO of the Month:

To Disable/Enable a trigger using T-SQL the following statements can be used:

ALTER TABLE TableName DISABLE TRIGGER TriggerName

ALTER TABLE TableName ENABLE TRIGGER TriggerName

To check if a Trigger is Enabled or Disabled the OBJECTPROPERTY can be used as illustrated below:

SELECT TRIGGER_STATE =

CASE OBJECTPROPERTY(OBJECT_ID('TriggerName'), 'ExecIsTriggerDisabled')

WHEN 1 THEN 'DISBALED'

WHEN 0 THEN 'ENABLED'

ELSE 'UNKNOWN'

END

The VBScript code snippet below builds on the TSQL above to disable and enable individual Triggers to allow all triggers in a database to be disabled/enabled. The code connects to a specified SQL Server Instance and enables/disables all Triggers in the specified Database based on the Value of the STATE constant.

Const SQLSERVER = "WDHXJGC1S\TEST" ' SQL Server Instance

Const DATABASE = "pubs" ' The database to alter the trigger status of

Const STATE = 0 ' - Disabled = 0 (False)

' - Enabled = -1 (True)

Dim oSQLServer

Dim oDatabase

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

Set oTable = CreateObject("SQLDMO.Table")

Set oTrigger = CreateObject("SQLDMO.Trigger")

oSQLServer.LoginSecure = True ' True = use Windows authentication

oSQLServer.Connect SQLSERVER

' Loop through each trigger on each table and set whether the Trigger ' is enabled or disabled

For Each oTable in oSQLServer.Databases(DATABASE).Tables

For Each oTrigger in oTable.Triggers

oTrigger.Enabled = STATE

oSQLServer.DisConnect

Set oDatabase = Nothing

Set oSQLServer = Nothing

T-SQL of the Month:

The humble ORDER BY clause specifies the sort for the result set. In the ORDER BY clause a sort column can be specified as a name, column alias or a nonnegative integer representing the position of the name. But you may not be aware that the ORDER BY clause can also contain an expression to define the organization of the sorted result set.

eg.

-- Order the last name column by the third character in lastname

SELECT lastname

FROM northwind.dbo.employees

ORDER BY SUBSTRING(lastname, 3, 1)

lastname

--------------------

Peacock

Buchanan

Dodsworth

Fuller

Callahan

King

Leverling

Davolio

Suyama

(9 row(s) affected)

-- Sort the ReportsTo column in ascending order with NULL values last in the result set

-- as apposed to the default of a Null values being treated as the lowest possible value

-- 2,147,483,647 (2^31 - 1) is the largest value for a datatype of integer

SELECT reportsto

FROM northwind.dbo.employees

ORDER BY ISNULL(reportsto, 2^31)

reportsto

-----------

NULL

(9 row(s) affected)

Another feature of the ORDER BY clause is the ability for the sort direction to be defined in different directions for each sort column

ie.

SELECT title, titleofcourtesy

FROM northwind.dbo.employees

ORDER BY title DESC, titleofcourtesy ASC

title titleofcourtesy

------------------------------ -------------------------

Vice President, Sales Dr.

Sales Representative Mr.

Sales Representative Mrs.

Sales Representative Ms.

Sales Manager Mr.

Inside Sales Coordinator Ms.

(9 row(s) affected)

SQL Server Tip of the Month:

In SQL Server 2005 you will have the ability to capture trace events as notifications to identify when a database has grown as a result of auto-grow being enabled. However SQL Server 7 and 2000 do not have any built-in functionality to identify when a database has grown as a result of auto-grow being enabled. This information can easily be determined by setting up a baseline for each file in each database.

The following TSQL query below will give you the number of 8K data pages in the database where the command is executed. To convert pages to MB modify SUM(size) to SUM(size) / 128.00.

SELECT GETDATE(), SUM(size)

FROM dbo.sysfiles

/* To Exclude Logfiles uncommented out the WHERE clause */

-- WHERE status & 0x40 <> 0x40

If this query is run on a regular basis and the results captured in a baseline table you then have the ability to compare the current values with historical values to track growth over time. In order to track individual file growth as apposed to database growth you can modify the query to include the filename as illustrated below.

SELECT GETDATE(), name, size

FROM dbo.sysfiles

/* To Exclude Logfiles uncommented out the WHERE clause */

-- WHERE status & 0x40 <> 0x40

For a limited time we have the HP / Compaq Presario Notebook - Model PN560PA which normally retails for $1800 available for the price of $1499 (inc. GST). Should you require any further details regarding this notebook refer to

http://h50025.www5.hp.com/hpcom/nz_en/11_29_64_2772_PN560PA.html or do not hesitate to contact WARDY IT.

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/jan2005.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 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.