WARDY IT Solutions Apr 2006 Newsletter

As I was on leave from the 30th of March to the 26th of April there was unfortunately no WARDY IT Solutions SQL Server newsletter produced for March. However, I hope that you and your families had a safe and enjoyable Easter and have had an opportunity to take a break at some stage for a little bit of rest and relaxation.

Over the last two months there has been a lot of activity with SQL Server, culminating in the release of Service Pack 1 for SQL Server 2005 on the 19th of April 2006. Service Pack 1 for SQL Server 2005 can be downloaded from http://shrinkster.com/ehi. With the release of Service Pack 1, an update to SQL Server 2005 Books Online has also been release which can be downloaded from http://shrinkster.com/ehj.

WARDY IT Solutions is putting together a Database Management service named Virtual DBA. Virtual DBA is a cost effective solution designed to assist small to medium sized organisations who may not have a dedicated Database Administrator to meet the day-to-day challenges of availability, performance and administration of critical SQL Server systems. The Virtual DBA service not only provides regular remote monitoring and reporting but also provides on-call access 365/year if the services of a Database Administrator are required. If you would like more details on how a Virtual DBA could assist your organisation then please do not hesitate to contact me.

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.

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:

SMO Presentation

SQL Server 2000 Post-SP4 Cumulative Hotfix

Tech-Ed Australia 2006

Kent Tegels Podcast

SSIS Package Templates

Troubleshooting Performance in SQL Server 2005

SQL Server 2005 NewSequentialID() Function

SQL Server 2005 Trial Edition

sqlcmd Utility

SMO of the Month

SQL Server Tip of the Month

Reporting Services Tip of the Month

SMO Presentation

IN March I presented at the Adelaide SQL Server User Group on the SQL 2005 Server Management Objects (SMO) and also at the Queensland SQL Server User Group (QSSUG) on Thursday the 27th April 2006. SMO is designed for programmatic access to SQL Server and supersedes the Distributed Management Objects (SQL-DMO) object model. The presentation was recorded using Camtasia and the wmv which was produced recorded the entire presentation including the audio. The wmv can be downloaded from http://www.wardyit.com/presentations/WARDYITSolutionsAdelaideSQLSMO.wmv. An mp3 of just the audio was also produced which can be downloaded from http://www.wardyit.com/presentations/WARDYITSolutionsAdelaideSQLSMO.mp3.

SQL Server 2000 Post-SP4 Cumulative Hotfix

On the 24th of March 2006 Microsoft released a Cumulative Hotifx for SQL Server 2000 Service Pack 4. The hotfix is a rollup of the hotfixes that were released after Service Pack 4 was locked down for Beta testing and can be downloaded from http://support.microsoft.com/default.aspx?scid=KB;%5bLN%5d;916287.

Tech-Ed Australia 2006

Registration for Tech-Ed Australia 2006 in Sydney has opened. Tech-Ed Australia 2006 is being held from the 22nd to the 25th of August 2006 at Sydney’s Darling harbor. For those who wish to take advantage of the early bird discount then you will need to book prior to the 9th of June 2006. The preliminary SQL Data and Business Intelligence tracks have been released and can be reviewed on Dave Glover’s blog http://blogs.msdn.com/dglover/archive/2006/04/27/584614.aspx.

Kent Tegels Podcast

Kent Tegels who has the blog Enjoy Every Sandwich (http://www.sqljunkies.com/WebLog/ktegels/) has started a SQL Server Podcaset. The first episode can be downloaded from

http://www.sqljunkies.com/WebLog/ktegels/archive/2006/03/13/TOAE002.aspx. In this episode Kent discusses some of the gotcha’s with deploying CLR projects in SQL Server 2005 and reviews some of the recent SQL Server 2005 books.

SSIS Package Templates

The following article details how a template can be created for SQL Server Integration Services (SSIS) Packages. By creating a template, it allows you to reuse items when you create a new package. For example, you may want to reuse connection managers and log providers so that these are consistent between each of the packages developed. For more details refer to http://support.microsoft.com/kb/908018/en-us.

Troubleshooting Performance in SQL Server 2005

If you are looking for resources on how to troubleshoot performance issues with SQL Server 2005 the following whitepaper is a great resource. The whitepaper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using tools such as SQL Server Profiler, System Monitor and Dynamic Management Views. The whitepaper can be downloaded from http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx.

SQL Server 2005 NewSequentialID() Function

There was recently a thread on the SQLDownUnder (http://www.sqldownunder.com/) mailing-list regarding the NewSequentialID() Function is SQL Server 2005. Greg Low (http://msmvps.com/blogs/greglow/default.aspx) has put together a blog post that describes some of the caveat's to be aware of when using this function to create GUID's that are greater then the previously generated GUID. The blog post can be read at http://msmvps.com/blogs/greglow/archive/2006/03/26/87935.aspx.

SQL Server 2005 Trial Edition

If you want to explore SQL Server 2005 or are looking at testing some of the new features of SQL Server 2005 with you existing applications (without shelling out for a license) then this is for you. There is a now a 180 Day Trial Version of SQL Server 2005 which is available for downloaded from http://www.microsoft.com/sql/eval/trial-dvd.mspx?wt.mc_id=SQL.AD.01038.

sqlcmd Utility

SQL Server 2005 has introduced a new command line utility called sqlcmd that supersedes isql. The sqlcmd utility is used to run adhoc queries interactively from a command prompt window or execute a script containing T-SQL statements. Gregory A. Larsen has written an excellent article which introduces the sqlcmd utility http://www.databasejournal.com/features/mssql/article.php/3566401.

SQL Server Tip of the Month

Determining the SQL Statements that are being issued and cause a deadlock is not the simplest operation. However if you have a SQL Server instance where deadlocks are commonly occurring that you wish to analyse, you can use the Trace Flag 1204 in order to record additional details regarding the deadlock including the statements that 'caused' the deadlock(s) in the SQL Server Error Log. To enable this trace flag the following DBCC command can be issued.

DBCC TRACEON (1204, 3605, -1)

1204 = record deadlock graph reports when deadlocks occur

3605 = log the DBCC Output to the SQL Server errorlog

-1 = log events for ALL user connections

TSQL Tip of the Month

SQL Server 2005 has introduced the ability to easily bulk load an XML document as a binary byte stream using TSQL. The following example illustrates how to insert an XML instance in a single column table:

CREATE TABLE dbo.XML_SINGLE_BLOB (XMLCol XML)

INSERT INTO dbo.XML_SINGLE_BLOB (XMLCol)

SELECT *

FROM OPENROWSET(BULK 'c:\test.xml', SINGLE_BLOB) AS x

The example is based on the test.xml document which is listed below:

<Root>

</Orders>

</Orders>

</Root>

Reporting Services Tip of the Month

If you need to identify the Parameters that are been used for any scheduled SQL Server 2000 Reporting Services reports then the following query can be used to return the report name and each parameter and the value it is set to. The query extracts the values from the XML document which is stored in the paramters column of the Subscriptions table.

CREATE TABLE #ReportParameters

(

ReportName SYSNAME,

ReportDesc NVARCHAR(1024),

Paramter VARCHAR(30),

Value VARCHAR(30)

)

SET NOCOUNT ON

— The paramters column in the Subscriptions table is a Text field

— It is converted to VARCHAR(800) to avoid using READTEXT

DECLARE cur_Subscriptions CURSOR READ_ONLY FOR

SELECT c.[name], s.[Description], CONVERT(VARCHAR(8000), s.Parameters) Parameters

FROM ReportServer.dbo.Subscriptions s JOIN ReportServer.dbo.Catalog c

ON s.Report_OID = c.ItemID

DECLARE @ReportName SYSNAME

DECLARE @ReportParameter VARCHAR(8000)

DECLARE @ReportDesc NVARCHAR(1024)

OPEN cur_Subscriptions

FETCH NEXT FROM cur_Subscriptions INTO @ReportName, @ReportDesc, @ReportParameter

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @ReportParameter

INSERT #ReportParameters (ReportName, ReportDesc, Paramter, Value)

SELECT @ReportName, @ReportDesc, Name AS Paramter, Value

FROM OPENXML (@idoc, '/ParameterValues/ParameterValue',3)

WITH (Name VARCHAR(30),

Value VARCHAR(30))

— No Paramters

IF @@ROWCOUNT < 1

BEGIN

INSERT #ReportParameters (ReportName, ReportDesc, Paramter, Value)

SELECT @ReportName, @ReportDesc, NULL, NULL

END

FETCH NEXT FROM cur_Subscriptions INTO @ReportName, @ReportDesc, @ReportParameter

END

CLOSE cur_Subscriptions

DEALLOCATE cur_Subscriptions

SELECT ReportName, ReportDesc, Paramter, Value

FROM #ReportParameters

DROP TABLE #ReportParameters

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