Jumping To SQL Server 2005 - 3161

Microsoft released yet another cummulative patch for SP2 . After applying this patch you will be running on build 3161. Full details and downloads are available here.

SQL Server 2005 SP2 - Wrap-ups

Confused by the various post fixes and re-rereales of SP2? Bob Ward from Microsoft PSS SQL Support wrote an excellent blog post: "SQL Server 2005 Service Pack 2 (SP2) Re-release and post fixes explained". Steve Jones from SQLServerCentral compiled a nice list of all SQL 2005 build numbers and corresponding KB articles.

Next SQLUG evening: Replication with SQL Mobile

With the use of mobile devices becoming more and more mainstream, it is likely that we will see an increased need to integrate the data stored on these devices with other databases. Replicating data from and to mobile devices is probably one of the more technically challenging parts of mobile database development and management. On Thursday, April 19th Erik Bollen will be walking us through replication with SQL Server Mobile and point out some of the common pitfalls.

This event will take place in the offices of Info Shelf  in Asse-Zellik.

Driving directions can be found here.

Registration is open.

One more year of SQL Server 2000

Still running on SQL Server 2000? Time to move on!! Exactly over 365 days Microsoft will end the main stream support for SQL Server 2000. Extended (payed) support stops in 2013. Check out SQL Server 2000 Support Lifecycle.

SQL Server 2005 SP2 - To Continue, Click Next

There are quite a few issues with SQL Server SP2. Many people have blogged about it and MS is releasing post SP2 hotfixes every now and than ;-)

One of my colleagues encountered this funny dialog box while installing SP2.

Estimating backup/restore duration

How many times have you been watching the progress bar during a SQL Server Backup or restore operation? And how times have you answered "Wel, I guess ... " when people asked you "Hey mister DBA, how much more time will it take before the restore has completed?".

I have been doing quite some backup/restore operations during recent upgrading and migration projects. The sys.dm_exec_requests DMV contains one row for every running command on a SQL Server instance. Two interesting columns (estimated_completion_time, percent_complete) help you answering the "when" and "how much longer" questions.

The SELECT statement below will give you a time estimation for every running "BACKUP DATABASE" or "RESTORE DATABASE" statement. Play around with the WHERE clause to monitor other long running operations.

/* Created by free online sql formatter */


SELECT command, 'EstimatedEndTime' = Dateadd(ms,estimated_completion_time,Getdate()),
'EstimatedSecondsToEnd' = estimated_completion_time / 1000,
'EstimatedMinutesToEnd' = estimated_completion_time / 1000 / 60,
'BackupStartTime' = start_time,
'PercentComplete' = percent_complete
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','RESTORE DATABASE')


KHM BI Event follow up

Last Thursday KHM Mechelen organized an event on Business Intelligence. Together with 5 other colleagues from the Cronos Group we gave 6 presentations. This was the agenda:

• Positioning of BI tools in the market – Bart Van Mulders
• The Buzz Words – Nick Wuyts
• Skills required for a successful BI project – Bart Gorremans
• Competitive comparison of some important Business Intelligence Suites
• BusinessObjects – Jorn Vandendriessche
• Microsoft Business Intelligence – Frederik Vandeputte
• Oracle BI – Stijn Vandereycken

Given the audience of about 100 (ex) students I decided to do a rerun of my biTunes presentation. I got pretty positive feedback from a lot students, Thanks for that. Here's a wrap up of the Q&A:

• What's that nifty tool you used to zoom in and out?
  It's called ZoomIt and is available as a free download from the MS Sysinternals website.
  
• Can I download the biTunes source code?
  Yes, I posted it here.
  
• Why is the Undo feature missing from Integration Services.
  This is known issue, and feature request on many people's which list.
  
• Can you load the iTunes Library with plain T-SQL code?
  Yes, you can check out Peter Debetta's post.
• Cronos, Datamotive, I4BI, Kohera ... Who's Who?
• Datamotive is the BI competence center in the Cronos Group using tools like BO, Cognos ...
• I4BI is the competence center with full focus on Oracle BI
  
• Kohera is dedicated to implementing Microsoft's Business Intelligence suite and delivering services on the MS SQL Server platform.

We really liked the event, and are planning more BI roadshows comparing Microsoft BI with other vendors. So, if you are interested ... drop me a line.

Adding and removing Custom Reports in SQL Server Management Studio

SQL Server 2005 SP2 contains quite a few interesting new features. A few months ago I blogged about my old time feature requests: logon triggers. Another nice feature in SP2 are the Custom Reports in SQL Server Management Studio which allow you to add your own Reporting Services reports to the Management Studio.

Just right click anywhere in the Object Explorer, Choose Custom Reports and point to your favourite .RDL file. From now on this report will be just a mouse click away.


So, adding reports is easy, but how about removing them? All data about custom reports is stored in the a file called reports.xml which resides well hidden in your profile folder (%userprofile%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\reports.xml).

If you want to remove a report from the Context menu follow these steps:

• Close all running SQL Server Management Studios
• Open %UserProfile%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\reports.xml
• Remove the corresponding section from the XML
• Save reports.xml
• Restart SQL Server Management Studio
• The report should be gone

By default the Management Studio gives a warning message when you start a custom report. You can suppress these warnings via the GUI. However if want to unsuppress these warnings you need to edit the same reports.xml file. Check out : "How to: Unsuppress Run Custom Report Warnings" from Books Online.

One more note: Microsoft recently released a set of reports called "SQL Server 2005 Performance Dashboard Reports". They are pretty cool and can be download here.

SQL 2005 SP2 - Second try

Microsoft quietly released an updated version of SQL Server 2005 SP2. The new build number is 9.00.3042.01. The previous build number was 9.00.3042.00. The update corrects an issue with the cleanup task in maintenance plans. Here is the download link for SQL Server 2005 SP2.

Kinda reminds me of the SQL Server SP3a story ;-)

SQL Server presentations on Dev IT Pro Days

The Belgian Developer and IT Pro days are comming soon. If you want to take advantage of the early bird offer, hurry up, because the offer expires tomorrow.

There are quite some interesting SQL Server/BI presentations scheduled on Wednesday and Thursday. Jake Zborowski and Ashvini Sharma from Microsoft Corp will be delivering a complete BI track on Wednesday. On Thursday Gunter Staes from MS Belgium will give a session entitled "MOSS 2007 Configuration of Excel Services, SSO & Data Conections - The Mac Guyver Techniques !". Also on Thursday, one my favourite SQL Server authors and speakers, Bob Beauchemin will give 4 SQL Server sessions. Check out the full agenda.

Keep an eye on the SQLUG.BE homepage because we are working on something special for our members.

SQL 2005 SP2 Ramblings

CTPs of Service Pack 2 for SQL Server have been around for a little while now. Rumor has it that SP2 will finally be released on Monday. That's a nice coincidence, since on Thursday we are celebrating the first anniversary of the Belgian SQL Server User Group . Gunter Staes, Partner Technology & Solution Advisor at Microsoft will highlight the top features of SP2. We will conclude the evening with a nice buffet. We still have a few seats available, so if you wanna join us, click here.

[UPDATE 19-FEB-06] SP2 is indeed released and can be downloaded here.

Belgian Dev IT Pro Days

On March 27th, 28th and 29th Microsoft is organizing the Belgian Developer and IT Pro Days in Ghent. Registration is open since last week. Eearly birds will get a 15% discount.

Microsoft published a preliminary agenda. On March 28th Jake Zborowski and Grant Dickinson from Microsoft Corp will present a full track on SQL Server Business Intelligence.

Keep an eye on the Dev IT Pro Days website because there are more SQL Server related topics comming soon ...

SQL Server 2005 SP2 December CTP

Microsoft released a new December CTP of SP2 for SQL Server 2005. Along with this refresh came some other nice downloads:

Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 - Community Technology Preview (CTP) December 2006
Microsoft SQL Server 2005 Data Mining Add-ins for Office 2007 (Data Mining Add-ins) allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes the following components:

• Table Analysis Tools for Excel: This add-in provides you with easy-to-use tasks that leverage SQL Server 2005 Data Mining under the covers to perform powerful analytics on your spreadsheet data.
• Data Mining Client for Excel: This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
• Data Mining Templates for Visio: This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings."

SQL Server Reporting Services Add-in for SharePoint Technologies CTP
Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies (Reporting Services Add-in) allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities within Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. The download provides the following functionality

SQL Server 2005 Books Online SP2 CTP (December 2006)
Updated BOL.

SQL Server Database Publishing Wizard 1.0 RC
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider."

And there is even more .... A full list of recent SQL related downloads can be found here.

The Cowboy Way of upgrading to SQL Server 2005

One of the scenarios I showed during my presentation on upgrading to SQL Sever 2005 was "The Cowboy Way of upgrading". Here's the situation. A SQL 2000 database containing a user called sys, cannot be upgraded to SQL Server 2005. If you run the Upgrade Advisor, you will get a warning on this show stopper. So, the best practice here is to rename the user on SQL Server 2000 and afterwards upgrade to SQL 2005.

In "The Cowboy Way of upgrading" I restored the SQL Server 2000 database with the sys user on my SQL 2005. The restore generated an error because of the sys users and the database was marked suspect.

Remember that is definitely not the best way of upgrading. But since quite a few people asked about the script I decided to post it. Have fun but be careful.

Connecting to localhost\sql2005...
15 percent processed.
23 percent processed.
30 percent processed.
46 percent processed.
53 percent processed.
61 percent processed.
76 percent processed.
84 percent processed.
92 percent processed.
100 percent processed.
Processed 104 pages for database 'sys_database', file 'sys_database_Data' on file 1.
Processed 1 pages for database 'sys_database', file 'sys_database_Log' on file 1.
Converting database 'sys_database' from version 539 to the current version 611.
Database 'sys_database' running the upgrade step from version 539 to version 551.
Warning: User "sys" (principal_id = 5) in database "sys_database" has been renamed
 to "_ID_0x00000005_RENAMED_FROM_sys". "sys" is a reserved user or schema name
in this version of SQL Server.
Warning: Database "sys_database" was marked suspect because of actions taken during
 upgrade. See errorlog or eventlog for more information. 
Use ALTER DATABASE to bring the database online. The database will come online in
restricted_user state.
Database 'sys_database' running the upgrade step from version 551 to version 552.
Database 'sys_database' running the upgrade step from version 552 to version 553.
Database 'sys_database' running the upgrade step from version 553 to version 554.
Database 'sys_database' running the upgrade step from version 554 to version 589.
Database 'sys_database' running the upgrade step from version 589 to version 590.
Database 'sys_database' running the upgrade step from version 590 to version 593.
Database 'sys_database' running the upgrade step from version 593 to version 597.
Database 'sys_database' running the upgrade step from version 597 to version 604.
Database 'sys_database' running the upgrade step from version 604 to version 605.
Database 'sys_database' running the upgrade step from version 605 to version 606.
Database 'sys_database' running the upgrade step from version 606 to version 607.
Database 'sys_database' running the upgrade step from version 607 to version 608.
Database 'sys_database' running the upgrade step from version 608 to version 609.
Database 'sys_database' running the upgrade step from version 609 to version 610.
Database 'sys_database' running the upgrade step from version 610 to version 611.
Msg 3167, Level 16, State 3, Line 1
RESTORE could not start database 'sys_database'.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'sys_database'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Here's how I fixed this one. First of all I forced the database online (ALTER DATABASE sys_database SET ONLINE). SQL Server will put the database in single user. In the next step I checked the system catalogs to find out that SQL Server created a new schema called _ID_0x00000005_RENAMED_FROM_sys. My table was moved from the sys schema to this new schema.

In the next step I created a new schema and used ALTER SCHEMA to move the table from _ID_0x00000005_RENAMED_FROM_sys to my new schema.

CREATE SCHEMA system

ALTER SCHEMA system
TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

In the last step I put the database in multi user mode.

ALTER DATABASE sys_database
SET MULTI_USER

Here's the full script.

USE master

--Force online
ALTER DATABASE sys_database
SET ONLINE

--Now in single user, show object explorer first
USE sys_database


SELECT name, SCHEMA_NAME(schema_id) as schema_name, *
FROM sys.objects
WHERE NAME = 'myTable'

SELECT * FROM sys.schemas

SELECT * FROM sys.database_principals

SELECT * FROM sys.server_principals

CREATE SCHEMA system

ALTER SCHEMA system
TRANSFER _ID_0x00000005_RENAMED_FROM_sys.myTable

SELECT * FROM system.myTable

INSERT INTO system.mYTable (col1) VALUES ('Frederik')

ALTER DATABASE sys_database
SET MULTI_USER

Upgrading to SQL Server 2005

Microsoft Belgium launched an Upgrade to SQL Server 2005 campaign. One of the cornerstones of the campaign is the brand new website www.sqlserverupgrade.be. The website is loaded with links to documents, webcast, hand-on labs and much more.

On December, 6th I will be presenting a TechNet Evening on Upgrading to SQL Server 2005. I will demonstrate different upgrade scenarios and show you the pros and cons of each upgrade path. I will pinpoint potential pitfalls and show you the tools which can help you during the upgrade process.

Full details and free registration can be found here.

As a Sinterklaas present all attendees will receive a copy of the SQL Server Upgrade Resource kit for free.

See you next week.

30 minutes of fun with Bart

Make sure to catch Bart's funcast on Wednesday.

During this 30min demo session we will show and explain you how to create your own blog and wiki site with Sharepoint 2007.

http://www.sharepointblogs.com/bart/archive/2006/11/21/16292.aspx

SQL Server Logon triggers part 2

In April I wrote an article entitled "SQL Server 2005 logon triggers". The article will be published in the forthcomming book "The best of SQL Server Central - Volume 4".

In the article I described how to mimic Oracle logon triggers using event notifications in SQL Server 2005.

That was part one. Now comes part 2. Last week Microsoft released the CTP of SQL Server 2005 SP2. Here's a list of all new features in SP2. And one of them is logon triggers. Great, finally we have them.

So can we do everything with logon triggers? No, there are some differences with the method I descibed in April using event notifications.

• Logon triggers are part of the "logon transaction", this means you can rollback or cancel a logon. Event notifications go on the queue, after the logon. So, no easy way to cancel a logon.
• triggers are raised synchronously with events, whereas event notifications are asynchronous.
• Logon triggers only fire if authentication succeeds, event notifications can also capture failed logons and logouts.
  

Anyway it is a nice new feature. I rewrote my logon trigger and it's now a lot easier to understand (keeping in mind the differences mentioned above). You can find the script at the end of this post.

Ever wanted to prevent those Excel or Access users from reading your SQL tables directly? Well these 7 lines of codes will do the trick:

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END

Here's a complete sample script:

-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
-- PARTICULAR PURPOSE.
-- Frederik Vandeputte - www.vandeputte.org


--TESTED ON SQL 2005 SP2 CTP

USE MASTER
GO
--Clean up old logging db
IF DB_ID ('Logging_demoSP2') IS NOT NULL
BEGIN
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
END
GO
--Create Logging DB and table
CREATE DATABASE Logging_demoSP2
GO

USE Logging_demoSP2

CREATE TABLE Logging (

LoggingID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
FullLog XML
)
GO


--The LOGON Trigger
CREATE TRIGGER logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @message_body XML

SET @message_body = eventdata()

INSERT INTO Logging_demoSP2.dbo.Logging (
EventTime,
EventType,
LoginName,
HostName,
AppName,
FullLog )
VALUES
(
CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
CAST(@message_body.query('/EVENT_INSTANCE/ClientHost/text()') AS VARCHAR(100)),
APP_NAME(),
@message_body)


END


GO

--Open demo connections

select * from logging


--Prevent acces from Excel
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END


--Try to connect from Excel

--Clean DB
USE MASTER
GO
ALTER DATABASE Logging_demoSP2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Logging_demoSP2
DROP TRIGGER logon_trigger ON ALL SERVER
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER

biTunes - The Source code

As promised last week on Teched... the source code for biTunes.

This zip files contains all the scripts, database backup, and BIDS solution for running your own biTunes BI solution.

Scripts were created with SQL Server 2005 SP1.

I included a small sample iTunes music library file. Copy your own iTunes Music Library.xml file to c:\biTunes\iTunes Music Library.xml to analyze your own MP3 collection.

Have fun!!

(Update, June 29h 2007 - new download location)

iTunes 7 vs. Mediaplayer 11

During the dry-runs of my biTunes presentation people often asked me why iTunes? iTunes happened to be the MP3 player I used on Mac and Windows. So I answered: "It doesn't matter, it's just an MP3 player and it's very similar to Media Player, Winamp or other players".

Today, Microsoft released Windows Mediaplayer 11. I decided to give it a try, and found out there are indeed some similarities. Have a look at the screenshots below or this little movie comparing both.






So again, why iTunes? Well biTunes sounds better than biMediaPlayer ;-) See you next week!

Por Dentro SQL

At last .... Peter - the president of SQLUG.BE - started his blog. His first post is an interesting one on clustering Integration Services.

Success with your blog, Peter !!!

His blog can be found on the following URL:
http://sqlug.be/blogs/por_dentro_sql/

Other interesting blogs on SQLUG.BE are Geoffrey blog called DrivenBySQL and the SQLUG.BE Resource Blog.