It is shaping up to be a very interesting year in the SQL Server space.  SQL Server 2000 Service Pack 4, Reporting Services Service Pack 2 and SQL Server 2005 Express are all currently in Beta 2.  For those who have not signed up already you can still nominate to join the Beta program for each of these products at http://www.microsoft.com/sql/evaluation/betanominations.asp.  If this wasn’t enough activity it is also rumoured that SQL Server 2005 Beta 3 will be released at TechEd in May and will be available for public nominations.

 

For those of you running Reporting Services I would strongly recommend that you start to look at the Service Pack 2 Beta, even if its is simply for the ability to print directly from the Report Page instead of having to export to another application.  Also, if you are looking at investing time to workaround some of the existing RS 2000 limitations you should get your hands on the Beta 2 of SQL Server 2005, as the self service and ad hoc reports along with additional controls will greatly assist with report creation.

 

Microsoft has recently teamed up with Imation in order to provide a catalogue update for the Disk Stakka each month for each MSDN subscription package.  In association with this we have for a limited time the Imation Disk Stakka available for only $175.00.  For more details regarding this offer do not hesitate to contact us or refer to http://www.imation.com.au/products/disc_stakka/index.htm

 

As a result of increased interest in SQL Server Reporting Services we have added a Reporting Service Tip of the Month to compliment the existing DMO, TSQL and SQL Server tips.

 

 

Just a reminder to all, as a result of our office relocation our new contact details are:

 

Phone:     +61 7 3856 5411

Fax:       +61 7 3856 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, 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:

 

DBCC Commands

SQL Server Nuggets

DTS Backup

SQL Server 2005 Whitepapers

SQL Server 2000 I/O Basic

Analysis Services and MapPoint

VB.NET SQL Server Stress Tool

Code Camp Oz(I)

SQL Server  Enterprise Edition (64-bit) Seminar

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

Reporting Services Tip of the Month

DBCC Commands

The following link is a list of DBCC (Database Console Commands) commands, some documented and some undocumented that can assist in optimising SQL Server and identify what is going on under the hood

http://www.sql-server-performance.com/dbcc_commands.asp.

 

 

SQL Server Nuggets

The folks in Microsoft Developer Evangelism in the UK have come up with some short, sharp, and to the point demos and called them nuggets.  Basically a Nugget is of 5-10 minutes of on-demand technical content similar to a webcast but shorter.  Currently there are two good topics on developing for SQL Server 2005 using .Net managed code.  http://blogs.msdn.com/tims/archive/2005/02/02/365838.aspx

 

 

DTS Backup

There is often a need to migrate a number of DTS Packages from one server to another.  This can be achieved by opening each Package and changing the server location or scripting the migration.  The people behind SQLDTS.com have created a tool that automates this process by allowing you to select multiple source packages and copy them to a destination server.  To download the application and documentation refer to http://www.sqldts.com/default.aspx?242.

 

 

SQL Server 2005 Whitepapers

Over the last couple of months a wealth of information has become available regarding SQL Server 2005.  Microsoft has dedicated a page to Whitepapers for SQL Server 2005 ranging from articles on XML support to Snapshot isolation and everything in between.

http://www.microsoft.com/sql/2005/techinfo/default.asp

 

 

SQL Server 2000 I/O Basic

Bob Dorr, a Microsoft SQL Server Escalation Engineer has written an excellent whitepaper on the I/O requirements for SQL Server file operations.  The article explains the basics of SQL Server I/O and identifies how to improve system performance and avoiding I/O environment errors.  http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqliobasics.mspx

 

 

Analysis Services and MapPoint

There is a little publicized free OLAP Add-In for Microsoft MapPoint which allows you to get data from your Analysis Services cube into a geographic map.  The Add-In contains a wizard which builds MDX queries against cubes and then uses standard MapPoint mechanisms to map a tabular resultset into a map.  As MapPoint has been expanded form North America and Europe to include the Asia Pacific region it may be a new way to display your dimensions. http://www.microsoft.com/downloads/details.aspx?familyid=e22fe181-1d5f-4bbd-9e39-825a74fb1788&displaylang=en

 

 

VB.NET SQL Server Stress Tool

The SQL Server 2000 Resource Kit contains a VB6 utility named Database Hammer to test the performance of a SQL Server 2000 instance with a large number of concurrent users.  However the limitation is that this stress testing is performed on a generic database and not a specific database with all of the objects associated with the application.  DBstressUtil is a VB.net utility that fills this shortcoming by simulating multiple database users submitting T-SQL scripts taken from an input text file in order to determine SQL Server performance under the application load.  To download this utility and source code refer to http://www.codeproject.com/vb/net/DBstressUtil.asp.

 

 

Code Camp Oz(I)

Code Camp Oz is a free Microsoft community event to be held at Charles Sturt University in Wagga Wagga (NSW) over the Anzac Day long weekend in April.  There will be various sessions on Visual Studio 2005 (Whidbey) and SQL Server 2005 (Yukon) as well as a number of sessions relating to the current generation of technologies such as Visual Studio .NET 2003.  For more details refer to http://www.codecampoz.com/.

 

 

SQL Server  Enterprise Edition (64-bit) Seminar

Tony Bain and David Lean are anchoring an event in all of the Eastern Capital cities in late February and early March appropriately titled “Microsoft SQL Server Enterprise Edition (64-bit) is here. Now What?”. This event will cover how the existing 32-bit limitations of SQL Server are addressed in the 64-bit Edition and how applications benefit form this upgrade.  For more information and to register for this event refer to http://www.communique.com.au/rsvp/sqlseminars/add.asp.

 

 

 

DMO of the Month:

 

In order to display the database and log files associated with a database backup set the RESTORE FILELISTONLY can be executed.  This command is commonly executed prior to executing a RESTORE with MOVE in order to determine which database or log files associated with the backup set need to be moved.

 

ie.

 

RESTORE FILELISTONLY FROM DISK = ‘c:\northwind.bak’

 

Retruns:

 

LogicalNameA     PhysicalName                Type FileGroupName Size      MaxSize               

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

Northwind   C:\MSSQL\Data\northwnd.mdf  D    PRIMARY       2752512   10000000

Northwind_log     C:\MSSQL\Data\northwnd.ldf  L    NULL          1048576   10000000

 

 

The example below demonstrates how the RESTORE FILELISTONLY information can be returned from a SQL Server backup set using SQLDMO.

 

Dim oQueryResults

Dim oSQLServer

Dim oRestore

Dim str

Dim i

 

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

 

oSQLServer.LoginSecure = True

oSQLServer.Connect "(local)" ' SQL Server Instance to connect to

 

Set oRestore = CreateObject("SQLDMO.Restore")

 

' The SQL Server Backup to read the details from

oRestore.Files = "c:\northwind.bak"

 

Set oQueryResults = oRestore.ReadFileList(oSQLServer)

 

' Loop through each database and log file contained in the backup set

' and determine the properties as per RESTORE FILELISTONLY and display

' the results

For i = 1 To oQueryResults.Rows

      str = ""

      str = str & "LogicalName: " & oQueryResults.GetColumnString(i, 1) & vbCrLf

      str = str & "PhysicalName: " & oQueryResults.GetColumnString(i, 2) & vbCrLf

      str = str & "Type: " & oQueryResults.GetColumnString(i, 3) & vbCrLf

      str = str & "FileGroupName: " & oQueryResults.GetColumnString(i, 4) & vbCrLf

      str = str & "Size: " & oQueryResults.GetColumnString(i, 5) & vbCrLf

      str = str & "MaxSize: " & oQueryResults.GetColumnString(i, 6)

     

      Msgbox str

Next

 

oSQLServer.Close

 

Set oRestore = Nothing

Set oSQLServer = Nothing

 

 

 

T-SQL of the Month:

 

There are many times where there is the need to generate data in a report format where a Subtotal aggregate is displayed after each grouping and a Grand Total aggregate is displayed aggregating all of the grouped data.  This data representation is similar to the data grouping functionality of Reporting Services where aggregation is performed by the reporting engine after each grouping.  Below is an example of how this grouping can be performed using T-SQL using the GROUP BY WITH ROLLUP option.

 

The example below illustrates how to display the total sales for each product by customer as well as a Subtotal for all sales by customer and a Grandtotal of all sales for all customers.

 

SELECT CASE WHEN (GROUPING(CustomerName) = 1) THEN 'ALL'

            ELSE ISNULL(CustomerName, 'UNKNOWN')

       END AS CustomerName,

       CASE WHEN (GROUPING(ProductName) = 1) THEN 'ALL'

            ELSE ISNULL(ProductName, 'UNKNOWN')

       END AS ProductName,

       SUM(ExtendedPrice) AS ExtendedPrice

FROM Invoices

GROUP BY CustomerName, ProductName WITH ROLLUP

 

 

Sub set of results retruned:

 

CustomerName                        ProductName                       ExtendedPrice        

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

Alfreds Futterkiste                 Aniseed Syrup                      60.0000

Alfreds Futterkiste                 Chartreuse verte                  283.5000

Alfreds Futterkiste                 Escargots de Bourgogne           503.5000

Alfreds Futterkiste                 Flotemysost                          430.0000

Alfreds Futterkiste                 Grandma's Boysenberry Spread   380.0000

Alfreds Futterkiste                 Lakkalikööri                       270.0000

Alfreds Futterkiste                 Original Frankfurter grüne     20.8000

Alfreds Futterkiste                 Raclette Courdavault              825.0000

Alfreds Futterkiste                 Rössle Sauerkraut                 604.2000

Alfreds Futterkiste                 Spegesild                            18.0000

Alfreds Futterkiste                 Vegie-spread                       878.0000

Alfreds Futterkiste                 ALL                                   4273.0000

Ana Trujillo Emparedados y helados  Camembert Pierrot                 340.000

…

Wolski  Zajazd                      Tarte au sucre                     591.6000

Wolski  Zajazd                      Tourtière                            22.3500

Wolski  Zajazd                      ALL                                   3531.9500

ALL                                 ALL                                   1265793.0600

 

(1775 row(s) affected)

 

 

SQL Server Tip of the Month:

 

When migrating data between different SQL Server collations there is a risk of data loss that can be mitigated by using the correct data pages.  This data loss can occur to extended or DBCS (Double Byte Characters) characters during the conversion between OEM and ANSI code pages.  By default when data is exported from SQL server it is performed using OEM code page characters, and when importing ANSI/Microsoft Windows code page characters are used. As a result there is a risk of loosing extended characters when using the default character code.

 

In order to prevent the loss of these characters during data export/import the bcp (Bulk Copy Command) has serval switches that can be used:

 

-N = Unicode native data format

-w = Unicode character data format

-C = A specific code page

 

Unicode native format and Unicode character format convert character data to Unicode during the bulk copy, resulting in no loss of extended characters.

 

Using the -C (code page) switch, the bcp utility can create or read data files using the code page specified.

 

This risk of losing extended characters also exists when using the BULK INSERT command and can be overcome by using the CODEPAGE option in order to set the code page instead of the default code page characters.

 

 

 

Reporting Services Tip of the Month:

 

The rsconfig utility is a little known utility that can be used to specify configuration file settings that use encrypted values.  Recently I came across the following error when attempting to try and access a Report Server “Login failed for user 'rs'”. rs was the user name used by the report server to log on to the SQL Server hosting the report server database.  As a result of a recent password change to this account Report Server was unable to be accessed.

 

This is where the rsconfig utility comes in, as it can be used to change the authentication mode, the database to use with the report server or the user name or password used by the report server to log on to the SQL Server hosting the report server database.

 

In order to change the password for the account used to access SQL Server hosting the report server database the following command can be executed

 

rsconfig -c –s sqlserver_instance –u username –p password –d reportserver_database –a authentication_type (sql or windows)

 

For full details on all the available switch run rsconfig /? From the command line.

 

 

 

For a limited time we have 1GB SanDisk Secured Digital (SD) Memory Cards available for the price of $147.00 (inc. GST), these Memory cards normally retail for over $170.  Should you require any further details regarding this offer than 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/feb2005.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.