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 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.
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')
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.
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
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.
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".
There is an interesting MSDN evening comming up. Gunther Beersaerts, Senior Technical Specialist @ Microsoft will talk about Data Dude.
November 15, 2006: Introducing Visual Studio Team Edition for Database Professionals
Visual Studio Team Edition for Database Professionals is a database development product designed to manage database change and improve software quality through database testing. Additionally this product brings the benefits of Visual Studio Team System and life cycle development to the database professional. During this session you'll get an overview of this product, including the integration with source code control, the schema and data compare functionalities, rename refactoring, the new T-SQL editor and database testing.
iTunes meets BI!! I will be speaking at Teched : Developers in Barcelona. The session is called "biTunes - Building an end-to-end Business Intelligence (BI) application with SQL Server 2005 and … iTunes!"
This is the abstract.
SQL Servers 2005 gives you all the tools you need for building end-to-end BI applications. In this 100% demo driven session we walk through the complete SQL Server 2005 BI stack. We'll use SQL Server Integration Services to import the iTunes (yes, that's right) music library. We'll build an OLAP cube on top of our MP3 library with SQL Server Analysis Services. Finally we'll use SQL Server Reporting Services and Excel 2007 to reveal information you thought you could never retrieve from iTunes. Come and take a walk on the BI-side...
The session is planned for Wednesday, Nov 8 13:30 - 14:45 (UPDATED) . I am looking forward to meet you there.
I am planning a couple of dry runs during the next weeks. If you would like to attend one of these , drop me an e-mail.
I was playing around with MDX and calculated measures in AS 2005. One of the nice things in AS 2005 is the script view. The Script view gives you all the calculations in a "text editor". This is really great if you need to do bulk changes to your calculated members. E.g. change the format for all uour calculated measures.
Have you tried that in AS 2000? Well I did, it takes some DSO programming Been there, done that during the last weeks.
So I was playing around in the script view, copy/pasting, global replacing ...and all of a sudden my cube was empty... How come? Well here's what I did wrong.
Open the sample solution that ships with SQL Server 2005 Samples (C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Tutorials\Lesson4). Deploy the cube and play arround in the cube Browser. You should see some data.
Now, go to the Calculations tab, and choose the Script view. You will see something like this:
/* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */
CALCULATE;
If you create calculated measures (or other calculations) they will come in here. If you create a new dummy calculated measure the script view might look as follows:
/* The CALCULATE command controls the aggregation of leaf cells in the cube. If the CALCULATE command is deleted or modified, the data within the cube is affected. You should edit this command only if you manually specify how the cube is aggregated. */ CALCULATE;
CREATE MEMBER CURRENTCUBE.[MEASURES].[dummy] AS [Measures].[Reseller Sales-Discount Amount]*2, VISIBLE = 1 ;
After you deployed the cube you will see the dummy calculated measure in the cube browser.
Now let's go back to the Calculations tab and just delete the line "CALCUATE;"
Redeploy, go back to the browser and what will you see: NADA!!
One of the things I learned with Ingres was that "not supported" is not the same as "it does not work".
So, last week I spend some evenings/nights pushing my luck and managed to get Vista and SQL Server 2005 running on an intel based iMac. During those installations I saw quite some freaky eror messages, warnings, aborted installations, and more funny stuff. Which again reminded me of ... ;-)
At the end of the week I had SQL Server 2005 running on Vista on an intel based iMac. It's not supported ... but it seems to work.
Do you like those fancy reports in the SQL Server 2005 management studio? Well if you do, you probably searched your hard disk for the RDL files. Did you find them? I don't think so, neither did I.
It's funny ... Once you go Mac you want to go Vista. I have been following Hans Verbeeck's adventures trying to get Vista running on his Mac Book Pro. Last week Bart finally got OS X running on his standard PC. He posted some screenshots and videos.
Yesterday I installed Vista Build 5536 (Pre RC1) on an Intel iMac. I used the recently updated Boot Camp 1.1 to create a Windows partition. After that the installation was a snap. No more need to delete the EFI partition.
Next steps are the drivers and of course ... SQL Server.
We all know we should use schema_binding and always list all column names in SELECT statements and view definitions. We know we should ... but nobody does it ;-)
Here's al little scripts which demonstrates how funny a SELECT * FROM ...view behaves when you start altering the underlying tables.
Tip of the day ... don't use SELECT * FROM in a view.... ;-)
/* Created by free online sql formatter */ /* --Drop objects if they exists DROP TABLE t1 DROP VIEW v1 */
CREATETABLEt1( xINT, yVARCHAR(5))
INSERTINTOt1 (x, y) VALUES(1, 'abc')
GO
CREATEVIEWv1 AS SELECT* FROMt1 GO
--This shows columns a and b SELECT* FROMv1
--Add a third column to our table ALTERTABLEt1 ADDzDATETIME GO
UPDATEt1 SETz=GETDATE() --Third column is in table SELECT* FROMt1 --Third column is NOT in view SELECT* FROMv1
--Drop column x from table ALTERTABLEt1 DROPCOLUMNx
--Here's the freaky part --Column headers are x and y but data shown is from columns y and z SELECT* FROMv1
Earlier this week I moved my blog to the great guys of Fab4.be.
This move might cause some problems if you were reading my via an RSS reader. Just to put things straigth. These are the correct RSS feeds (both in RSS and ATOM format).
Fact 1: I really like iTunes. It is a cool music player and it's pretty good in managing a complete MP3 library. I am importing all my CDs and LPs and vinyl singles into iTunes on a Mac. I still have some work to do but I am heading towards to an LP and CD free living room.
Fact 2: I want an iPod. I don't have one right now. Here's why. First of all I think they are pretty expensive. Today a 4 GB iPOD nano costs 259 EUR (July, 2006 Belgian Price in Apple Store). A 4GB iPod can hold about 1000 songs. That's 0, 259 EUR per song. In real money that's more than 10 BEF per song.
Second reason ... if I had an iPOD ... when would I use it? Well, at work, at home and in my car. Using the iPod on my MTB or while running (Nike+iPod :-)) would be nice but if I want to cut prices I have to sacrifice some features.
So I came up with the cohPOD. The cohPod only works in my car (c), in the office (o) and at home (h).
The cohPod is available in two editions: a 700 MB model and a 4,7 GB model.
Here's how it works:
Step 1: choose your cohPod model
Pick a bank CD-RW (700 MB) or DVD+/-RW 4,7 GB), take a pen and label it cohPod.
Step 3: Create a cohPod playlist on your main iTunes computer
In my iTunes library on my Mac I created a cohPod playlist. I dragged all the songs I wanted on my cohPod to this playlist. Make sure the total size of the playlist does not surpass the capacity of your cohPod (700 MB/4,7 GB).
Step 3: Burn the cohPod playlist to your CD-ROM
To do this manually, right click on the cohPod playlist and choose Burn Playlist to disk. This method will also work if your main iTunes library is on a PC.
I am working on an Apple Script which will automate this step. However the script did not make it in the RTM version of the cohPod. It might be included in cohPod SP1 or in the long rumoured cohVideoPod.
Step 4: Listen to the cohPod in my car
My Car CD player plays MP3 CDs, unfortunately it does not support DVDs. In other words, the 700MB cohPOD works fine in my car without any cables. The 4,7GB model is currently not supported.
Step 5: Listen to the cohPod at work
My Office PC runs Windows XP (no SQL Server on Mac yet ;-(), so I wrote this little VB-script to import the cohPOD playlist. All I need to do is launch the ImportCohPod script (via Launchy) and I there you go ...
'Change to match your CD drive FolderToAdd = "e:\"
'Set objects Set objApp = CreateObject("iTunes.Application") Set colSources = objApp.Sources Set objLibrary = colSources.ItemByName("Library") Set colPlaylists = objLibrary.Playlists
'Are you sure? answer = MsgBox("Are you sure you want to import " & FolderToAdd & " into you cohPod Playlist?", vbYesNo, "cohPod") If answer = vbNo Then Return End If
'Delete old cohPod Library if exists Set objPlaylist = colPlaylists.ItemByName("cohPod") If Not (objPlaylist is Nothing) Then objPlaylist.Delete End if
'Create new cohPOD playlist Set objPlaylist = objApp.CreatePlaylist("cohPod")
'Add files to cohPod playlist objPlaylist.AddFile(FolderToAdd)
answer = MsgBox(Cstr(objPlaylist.Tracks.Count) & " songs added to your cohPod Playlist (" & CInt(Cstr(objPlaylist.Size/1024/1024)) & " MB)", vbYes, "cohPod")
Conclusion
The 700MB cohPod holds about 175 songs, the 4,7 GB model holds about 1200 songs. A rewritable DVD costs about 1 EUR these days. This means the cohPOD costs less than 0,001 EUR per song. Compared to the 0,259 EUR per song for the 4GB iPod Nano, this is really cheap.
Secondly the cohPod does not require any cables. Not for synchronizing, nor for playing it in the car. Which makes it a true wireless xPod.
Third plus: battery life. Have you ever seen a DVD run out of batteries?
The cohPod doesn't look as flashy as Apple's iPods. It doesn't have all the cool features of the iPod (color screen, Photo's/Video's, multiple playlist, ....). People always talk about features ... but all they do with an iPod is playing songs, clicking next and previous and I can do all this with the cohPod.
Two more pics of the cohPod connected to a Mac and to a PC.
Running a full process of a shared dimension will bring down all the cubes that use that shared dimension. I needed a mecanism to check the status of all my cubes in an Analysis Services 2000 installation.
So I came up with the following VBS script. It's quick and dirty but it works.
'Connect Analysis server dsoServer.Connect ServerName
'Walk through OLAP DBs For Each dsoDB In dsoServer.MDStores 'Walk through Cubes. For Each dsoCube In dsoDB.MDStores State = "Other" If dsoCube.State = 0 Then State = "OFFLINE" ElseIf dsoCube.State = 4 Then State = "Online" End if Wscript.Echo dsoDB.Name & Delimiter & dsoCube.Name & Delimiter & State Next Next
Earlier this week Microsoft announced Visual Studio 2005 Team Edition for Database Professionals. The product is expected to ship by the end of 2006, but there will be CTP release next week.
37 Signals, the guys behind Ruby on Rails, have create some nice (and free) webapps. My favourite is Ta Da Lists. Basically it's a small to do list manager. The thing I like most about Ta Da Lists is the abality to subscribe to your own to do lists via RSS.
A video about how it works can be found the URL below:
It is so far, the SQLUG.be-team is proud to announce the first User Group evening which will be organised on thursday, June 22. The location is in the Microsoft classrooms at Av. Culliganlaan 1A, 1831 Diegem.
The event is free, but registration is required. Register now, and get the opportunity to vote for the topics to be discussed during the event.
Tool of the week - Adium Adiums a free instant messaging application for Mac OS X that can connect to AIM, MSN, Jabber, Yahoo" http://www.adiumx.com/
Quote of the week I have been working on quite some BI related projects recently. A nice qoute from Nietzsche: "There are no facts, only interpretations" Song of the week - Jesse James Bruce Springsteen recorded a very nice version of Jesse James on his last album .
My first article (SQL Server 2005 Logon Triggers) was published by SQL Server Central today. In the article I describe how to mimic Oracle logon triggers using event notifications in SQL Server 2005.
The article can be found on the URL below (free registration required)
MacBooks comming soon "Apple has begun manufacturing its new MacBook and should have the laptop in consumer hands in the next 30-60 days, sources report." http://www.thinksecret.com/news/0604macbook.html
I was a happy Colibri user for the last couple of months. Colibri is a very nice alternative program launcher for Windows. Last week I uninstalled Colibri and switched completely to Launchy. Launchy is better, faster, and it is opensource. Since version 0.9.1 search results are also sorted by the frequency of use. Press ALT SPACE F ENTER to launch Firefox. Press ALT SPACE S ENTER to launch SQL Server Management Studio.
Launchy is the most efficient way to open files and programs on your computer. Nobody likes to hunt through the start menu to find an application, just to find that the application is hidden under some obscure folder named after a company you have never heard of! Instead, Launchy is a smart search program which tries to guess which program or file you are looking for and will launch it when you hit the enter key. It is only visible when you hit the alt+space key combination; otherwise it hides in the background. Once you have used it for a few days, it becomes an indispensable utility for your computer. And it’s free!
Firefox Extension of the week The Copy URL+ extension enables you to copy to the clipboard the current document's address along with additional information such as the document's title, the current selection or both. https://addons.mozilla.org/extensions/moreinfo.php?id=129
During the last months the scope of my blog narrowed more and more to SQL Server and IT stuff in general. This gives me a good reason to switch it to English. I will use Babel Fish to translate between Dutch and English.
From English to Dutch...
Tijdens de laatste maanden in het algemeen versmalde het werkingsgebied van mijn blog meer en meer aan SQL Server en het materiaal van IT. Dit geeft me een goede reden om het aan het Engels te schakelen. Ik zal de Vissen van Babel om tussen het Nederlands en het Engels gebruiken te vertalen.
... and back to English
During the last months in general commonly narrowed the functioning area of my blog to SQL server more and more and the material of IT. This gives me a good reason to change gear it to English. I the fish of Babel will translate between Dutch and English using.
Microsoft Agrees to Acquire ProClarity, Enhancing Business Intelligence Offering: Leader in advanced analytics adds to Microsoft’s comprehensive BI capabilities.
Virutal Server R2 is vanaf nu beschikbaar als gratis download. Bovendien supporteert Microsoft Linux nu ook (officiëel) als guest OS binnen Virtual Server.
Onder het motto I Can do everything with T-SQL.... Hier vind je een artikel hoe je een Sudoku kan oplossen met T-SQL. Solving Sudoku with SQL. Funny :-)
De Database Mirroring Monitor is leuk speelgoed in SQL Server 2005. Voor replicatie bestaat er een gelijkaardige tool, namelijk de Replication Monitor. Replication Monitor maakt het heel makkelijk om latency in je replicatie te traceren. Door tracer tokens weg te schrijven in de transactie logfile kan je heel makkelijk te weten komen wat de latency is tussen publisher en distributor, tussen distributor en subscriber, en de totale latency.
Het ziet er naar uit dat database mirroring met de komst van SQL Server 2005 Service Pack 1 officiëel gesupporteerd zal worden. Het al dan niet opzetten van trace flag 1400 heeft in de March CTP geen belang meer.
Nieuw en zeer leuk is de Database Mirroring Monitor. Hiermee kan je alles in het oog houden. Je kan ook tresholds gaan definiëren. Bij het overschrijven van die tresholds wordt er een boodschap geschreven in de event log en die kan je dan gaan opvangen met de SQL Server Agent of MOM (of een andere monitoring tool).
Ook nieuw is sp_dbmmonitorresults. Deze system stored procedure toont je snel de status van een mirrored database.
exec msdb..sp_dbmmonitorresults 'AdventureWorks'
Hieronder een paar screenshots van het nieuwe speelgoed.
Peter De Seranno werkt met vol enthousiasme aan de oprichting van een Belgische SQL Server User Group. Alle info en binnnkort ongetwijfeld veel meer vind je op http://www.sqlug.be
SchemSapy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships. The browsing through relationships can occur though HTML links and/or though the graphical representation of the relationships. It's also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints.
Als je ook dot van Graphviz installeert en in je PATH (C:\Program Files\ATT\Graphviz\bin\) zet genereert het tooltje ook grafish de relaties tussen de tabellen.
Zorg er ook voor dat je de SQL Server 2000 JDBC driver geïnstalleerd hebt (default locatie is C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib).
Het volgende commando genereert de documentatie voor het Sales schema in de SQL 2005 AdventureWorks database.
Bij SQL Server 2000 Reporting Services zit er een DTS pakketje waarmee je de data uit de ExecutionLog tabel kan overzetten naar een meer gestructureerde en bruikbare structuur. Kijk eens in c:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\ExecutionLog.
In 2005 is dit DTS pakketje een SSIS pakketje geworden en hebben ze er nog eens 3 interessante sample rapportjes bijgestoken.
Al dit speelgoed is te vinden in c:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports.
Regelmatig post Microsoft een hele boel Technet presentaties en demo's op hun download site. Er zitten veel interessante zaken tussen, rond SQL Server maar ook over andere technologieën.
Naming conventions zijn goed. Alleen jammer als je achteraf alles moet aanpassen. Hieronder vind je een paar scripts om kolommen, tabellen en constraints te renamen.
--Rename primary keys --Remove Dim from column name SELECT 'EXEC sp_rename ''HR.' + TABLE_NAME + '.' + COLUMN_NAME + ''', ''' + SUBSTRING(COLUMN_NAME,4, 64) + ''', ''COLUMN''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'HR' AND TABLE_NAME LIKE 'Dim%' AND ORDINAL_POSITION = 1
--Rename tables --Remove Dim from table name SELECT 'EXEC sp_rename ''HR.' + TABLE_NAME + ''', ''' + SUBSTRING(TABLE_NAME,4, 64) + ''', ''OBJECT''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'HR' AND TABLE_NAME LIKE 'Dim%' AND ORDINAL_POSITION = 1
--Rename foreign keys --FK_ChildTable_ParentTable SELECT 'exec sp_rename ''HR.' + name + ' '',''FK_' + object_name(parent_object_id) + '_' + object_name(referenced_object_id) + '''' FROM sys.foreign_keys WHERE SCHEMA_ID = SCHEMA_ID('HR')
--Rename Primary keys --PK_Table SELECT 'exec sp_rename ''HR.' + name + ' '',''PK_' + object_name(parent_object_id) + '''' FROM sys.key_constraints WHERE SCHEMA_ID = SCHEMA_ID('HR') AND [TYPE] = 'PK'
--Rename default --DF_Table_Column SELECT 'exec sp_rename ''HR.' + d.name + ' '',''DF_' + + object_name(parent_object_id) + '_' + c.name +'''' FROM sys.default_constraints d INNER JOIN sys.columns c ON d.parent_object_id = c.object_id AND d.parent_column_id = c.column_id WHERE SCHEMA_ID = SCHEMA_ID('HR')
Colibri is een fantastisch alternatief voor het gewone Start Menu.
Colibri combines the raw power of the command line with the intuitive nature of a modern graphical user interface to define a revolutionary new way of managing your computer.
Een fantastisch tooltje voor de kings of the keyboard.
In its continued commitment to interoperability, Microsoft has released a new Java Database Connectivity (JDBC) driver with SQL Server 2005. The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge, and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in J2EE (Java2 Enterprise Edition).
The SQL Server 2005 JDBC Driver is JDBC 3.0 compliant and runs on the Java Development Kit (JDK) version 1.4 and higher. It has been tested against all major application servers including BEA WebLogic, IBM WebSphere, JBoss, and Sun.
Weer zo'n tooltje dat doet wat het moet doen, niets meer en uiteraard niets minder: kbAlertz.com
kbAlertz.com is an e-mail notification system that scans the entire Microsoft Knowledge Base every night, and e-mails you when updates or additions are made to the technologies you subscribe to.
Meer moet dat niet zijn: tooltje van de week dus!!
