SQL Server 2005 BOL July Update

A new version of SQL server 2005 Books Online has been released and can be downloaded from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx.  For a list of all of the changes in this version refer to the “New and Updated Books Online Topics” topic.  The most significant change is that that from this release (July 2006) a separate Books Online for SQL Server Express will no longer be available, meaning that there will be just one version of SQL Server Books Online.

SSMS Script One File For Each Object

A common question in the sqlserver.programming and sqlserver.server newsgroups is “where is the option to generate one file for each object when scripting an object from SQL Server Management Studio (SSMS)?”. Unfortunately the option is not available in SSMS even though it was available in Enterprise Manager in SQL Server 2000.  I believe that the option to generate one file for each object will be re-added in SQL Server 2005 Service Pack 2.

In the meantime the VB.Net code snippet below illustrates how SQL Server Management Objects (SMO) can be used to script all tables in the Northwind database so that the script for each table is in an individual file named after the object:

Add Reference to Microsoft.SQLServer.ConnectionInfo
Add Reference to Microsoft.SQLServer.SMO

Imports System.IO
Imports Microsoft.SqlServer.Management.Smo

Dim SMOServer As Server = New Server(“BNEMOM”)
SMOServer.SetDefaultInitFields(GetType(Table), “IsSystemObject”)

Dim so As ScriptingOptions = New ScriptingOptions
so.Default = True

For Each tbl As Table In SMOServer.Databases(“northwind”).Tables
If Not tbl.IsSystemObject Then
Dim sw As StreamWriter = New StreamWriter(“c:\” & tbl.Name & “.sql”)
For Each s As String In tbl.Script(so)
End If

Data Dude CTP4

The Community Technology Preview (CTP) of Visual Studio 2005 Team Edition for Database Professionals (aka Data Dude) is available and can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=C2FFA0E9-B68E-4411-9C9014697AC8FB95&displaylang=en.

The CTP released in June with the public announcement of Data Dude was CTP3.

Below are links to several podcasts available with Gert Drapers on Data Dude:

Itzik Ben-Gan at SQL Down Under

Itzik Ben-Gan will be in Australia in early October to deliver the course Advanced Transact-SQL Programming and Query Tuning for SQL Server 2000 and 2005. Whilst Itzik is in Oz he will also be presenting at the SQL Down Under Code Camp in Wagga Wagga.  If you have not seen Itzik present before then it will be worth attending the SQL Down Under Code Camp just to see Itzik’s presentation.

August WARDY IT Community Dinner

The WARDY IT Community Dinner is held on the second Monday of the month and is a great opportunity to share knowledge, information and experiences with other IT professionals in South East Queensland. The August WARDY IT Community Dinner is being held on Monday the 14th of August.  To RSVP to be at this event send an email to peter @ wardyit.com.

WHERE Moray Café | 1/158 Moray Street | New Farm | http://shrinkster.com/gm8
WHEN Monday 14th August | 6:30PM
RSVP Friday 11th July
Please note that you are responsible for the cost of your own dining.

Add this event to your Outlook Calendar

Reporting Services database expects version C.0.8.43

When you upgrade SQL Server 2005 to SP1 you may receive the following error when you initialise Reporting Services:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ‘C.0.8.40’. The expected version is ‘C.0.8.43’. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)

In order to resolve this you can follow the steps below:
1. Connect to the report server using the Reporting Services Configuration
2. Select Database Setup from the navigation pane
3. Click the Upgrade button

Default Database Script

Often the sp_help_revlogin (http://support.microsoft.com/kb/246133/) Stored Procedure is used to transfer logins from one server to another. In the June WARDY IT Newsletter (http://www.wardyit.com/newsletters/jun2006.htm) we showed how to build on this to also script the Server Roles for each login.  One other thing that would be nice is if this script also generated the Default Databases for each login.  The TSQL script below can be used to build on sp_help_revlogin to generate a script to set the Default Database for each login.

SELECT ‘EXEC sp_defaultdb ”’ + name + ”’, ”’ + dbname + ””
FROM master.dbo.syslogins

SQL Down Under Code Camp

Greg Low has just announced the SQL Down Under Code Camp on the SQLDownUnder mailing list and also on his blog. The SQL Down Under Code Camp will be held over the weekend of the 7th and 8th of October at Charles Sturt university in Wagga Wagga.  More details can be found at http://www.sqldownunder.com/CodeCamp/tabid/53/Default.aspx.

Basically SQL Down Under Code Camp is a free (except for accommodation, food etc.) two day event that will focus on data access and all things SQL Server. CodeCampOz is the developer equivalent which has been held in Wagga Wagga for the past two years on the Anzac Day long weekend.

If you haven’t made the trek to Wagga Wagga for CodeCampOz and you are wondering what this event will be all about then basically think of it as an informal TechEd without the $1500 price tag and all of the glitz and swag.  It is a great opportunity not only to listen to some of the best presenters in the SQL Server and data access space but also a great opportunity just to catch up with other data dudes.  I have pencilled this weekend in, so hopefully I will see you there.