Want to run SQL Server Express on Vista?

"If you're looking to run SQL Server Express on Windows Vista, you'll need to have the service pack installed. The service pack to enable the balance of SQL Server 2005 will be released Q1 of 2007. It's unfortunate to have to wait, but if you need to run it on your early installations of Vista, make sure you get the service packs."

Find out more Here

Use this extended learning offering from Microsoft

Following the VMWare lead of providing pre-configured virtual machines, Microsoft have gone one step further by offering a set of virtual learning platforms from which you can select an environment to further your knowledge of Microsoft product combinations. Thanks go to Scott Swigart’s for providing a location to which I can offer you a link to further your knowledge.

Go get one and switch off your need for excess

Standards come and standards go, but things like these intelligent power switches are worth their weight in copper. Fantastic little pieces of plug in technology that may give the planet a few more years to breath! What we now need is for one of these to be connected to every computer that is made so that the developer can stop worrying about making money and go and invent something else!

Take a Microsoft Test at a 15% reduction

As Microsoft Certified Trainers we have been given a code to enable students to take Microsoft certification tests at a reduced rate.

You can use the code 'MSAU418D7F5B' to shave 15% off from the cost of a Microsoft certification test when tested with Pearson Vue.

To find out about certification options and get access to further resources to help you pass the exams take a detour to Available Microsoft Certifications

To book the test when you are ready go to PV Exam Booking Service. As well as booking the test here you can also set up an account to manage other test taker services offered by Vue.

Can you get help building XML documents in MS SQL Server Management Studio

During my run of the MS2734 course last week, a question came up which required me to extend my current knowledge about the Visual Studio IDE environment, before I could answer it properly. This led to the identification of a feature that I think a lot of DBA's will find useful that some may not know about. Further information is available here.

Post Course Evaluation

You know what happens, everyone is having so much fun that time ran out and you forgot to fill in the online evaluation before it was time to leave for the train on the last day of the course, or whatever. Well now you can just go to Evaluate your course, here and enter the evaluation there instead. I hope you made a note of the login ID and Password (Also know as the course ID and Pin) The instructor would have put them up on the whiteboard at the beginning of the week for you but if you don't have them then just ring and the super guys in admin will help you out, the details to contact should be on your joining instructions.

Business Intelligence - What of it?

Well it has become big in the eyes of the Microsoft marketing engine.

However, it has been big within the IT industry for a lot of years under various other labels. People have tried Enterprise information Systems, Management Information Systems, Statistical and Forecasting Systems, Data Warehouses, Data Marts, Corporate information Systems ..... etc, etc.

What it all boils down to is that millions have been spent on hardware and software but often the business is still running blind. Helped along by the culling of process management positions by ill defined projects which end up leading to the throwing away of a lot of the knowledge required to grow the business in a managed way.

Well when something is missing then you need someone to fill the gap. There is enough knowledge out there it is just a matter of collating it and presenting it in a useful fashion. Microsoft's' BI team feel that they have a valuable contribution to make in putting some of this knowledge back for our use.

It can be garnered here, where you will find all kinds of helpful information that it would normally take you many years to assimilate, while making common mistakes which later become known as experience.

Write and see what you get

I have started creating links to my MS Space because I wanted to try out the new Live Writer. This is an editor which formats blog entries nicely and then submits them to a blog site without one having to use different tools RE-produce the look and feel of an existing document in blog displayable format.

If you would like to try it also then get it from here, but remember it is Beta software so you may not want to connect it to an existing blog. If you need a safe place to point to from both your blog and the Live Writer then setup a space for yourself at http://spaces.live.com/. I am not going to repeat here what you can find adequately communicated elsewhere so start here to learn more about this product!

A template towards automation of Event Monitoring

On Further to the Google Blog you will find another template that I created to help students learn about templates and how they can make a DBA's life easier. It is the skeleton of a template that can be extended to enhance ones learning about event monitoring and how the service broker service is used to set it up, along with the relevant supporting objects. I hope you find it of value. If I directed you to it then please extend it as I have described for you in class.

Who am I

I just came across information about http://www.whatismyip.com/default.asp while reading through some newsgroup entries. It does exactly what the site says. It tells me what my IP address is. Useful if you are connecting through an ISP with a temporary leased Ip address amongst other things. The site also has reminders of commonly required prompt commands for dealing with IP addresses and even describes how an address is made up.

Dynamic SQL Templates

I am sure that you have heard about SQL Templates by now, after all they have been around for a while. However, the scripting tasks have become a little more challenging with all the lovely new features of the latest version of SQL SERVER (2005). Therefore, the availability of the templates, and their effective exploitation, is even more important.

I sometimes give my students tasks to automate and allow them to use any features that exist on the server to aid them. Then I make sure they have the scripts to achieve the tasks by the end of the course, or soon after if it has been a particularly challenging class ;-)

I have found that taking the existing scripts and showing the students how you can make them dynamic adds a lot of value for a lot of the students. It also gives them lots of ideas of things they can do to help others back at work.

I will be including examples of some of these dynamic templates in future blogs as well. This is why:-

Firstly, as a value added element for the students that would have liked to have completed the extra exercises but didn't have time.

Secondly, to suggest further ways for the scripts to be extended, for the students that managed to complete the tasks during the course.

Lastingly, to ensure that both sets continue their learning process back at work and to feed the enthusiasm they often demonstrate back into their work environment.

Dynamic Snapshot Template

The purpose of this script is to create a dynamic snapshot of a given database. It is presented as a starting point to progress your learning about the way that Templates can be used to ease your administration tasks!

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

USE master

GO

-- Drop snapshot database if it already exists

IF EXISTS

(

SELECT name

FROM sys.databases

WHERE name = N'<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

)

DROP DATABASE <Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>

GO

-- Create the snapshot database

USE <Database_Name, sysname, Database_Name>

GO

DECLARE @table TABLE (

name VARCHAR(20)

, fileid INT

, filename VARCHAR(200)

, filegroup VARCHAR (20)

, size VARCHAR(20)

, maxsize VARCHAR(20)

, growth VARCHAR(20)

, usage VARCHAR(10)

)

INSERT INTO @table EXEC SP_HELPFILE

DECLARE

@DynamicSQL VARCHAR(8000)

SELECT

@DynamicSQL = 'CREATE DATABASE <Database_Name, sysname,

Database_Name>_<Snapshot_Id,,Snapshot_ID>ON' + CHAR(10)

SELECT @DynamicSQL = @DynamicSQL

+ '( NAME ='

+ name

+ ', FILENAME = '

+ ''''+filename

+ '.ss'''+')'

+ + char(13)

+ ',' FROM @Table WHERE name NOT LIKE '%log%'

SELECT @DynamicSQL = LEFT(@DynamicSQL,len(@DynamicSQL)-1) + ' AS SNAPSHOT OF <Database_Name, sysname, Database_Name>'

EXECUTE (@DynamicSQL)

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

When you encounter <A,B,C> within the script

<>contains the whole section that will be replaced within the script when you choose the option shown below from the Query options

Screen clipping 1

A is the prompt for the user

B is the Data Type of the value being expected

C is the default value that will be used if a value is not provided

So given

SELECT name

FROM sys.databases

WHERE name = '<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

There are two parameters expected.

The first request is for Database_Name which is to be of type sysname and a value of Adventureworks has been entered for it.

The second parameter expected is Snapshot_id, it will default to text type and has not yet been provided. If it is not provided then the value Snapshot_ID will be used in the script.

Screen clipping 2

If OK was to be pressed in the current state then the inserted line would look as follows:-

SELECT name

FROM sys.databases

WHERE name = 'Adventureworks_Snapshot_ID'

Note: The Data Type is not checked and is informational only, it can be left blank if required.

If alternatively the values below were entered then the same query would read

Screen clipping 3

SELECT name

FROM sys.databases

WHERE name = 'MyDB_1'

Once you understand the way the script works then have a play with it. Some of the possible extensions of the script can include

Rename the snapshot if it already exists rather than dropping it

Introduction of formal error handling

Facilitate the capacity to database default the Snapshot ID to the current date and time

Created with Microsoft Office OneNote 2003One place for all your notes

A tool for the rest

Students that attend my courses are used to me going on about formatting SQL carefully for other people to read as well as themselves in a few months time and how the brain is a fantastic pattern matching machine tool which will aid them in their coding and enhance team work and simplify testing of code ... blah blah blah, I am sure some think! You also always get someone highlighting that even if they did do it the way suggested, most of the time they are maintaining other peoples code who didn't come on one of my courses etc. Well now there is a tool for you to sort out other peoples code that came before. Take a look at Online SQL and PL/SQL Formatter it is fantastic! So no more excuses for retaining the status quo at the level found.

A nice article on augmenting SQL2005 XML output with Namespaces

I think the internet is great. It means that if someone produces something that you think that someone you know would value from you can redirect them quite easily to the place in question. Case in point is this article by Mike Amundsen. Mike Amundsen's INETA Talks and Travels: XML Namespaces and SQL2005

How can I find which port a SQL Server 2005 Instance is listening on?

Sometimes it is useful to know which port a particular instance of SQL Server is running on, especially if you are using the default behavior where the SQL Browser automatically allocates a port to an instance. Now you can find out easily using the new dynamic management views that are provided within the SQL Server 2005 environment.

Here is a simple script which shows you where you can look

Now - Get your Intellisense at RedGate - Till September

One of the best features that Microsoft made available for the VB programmer was Intellisense. This name was first coined by Microsoft, when describing a feature used to make VB easier to code. It enables the computer to help the programmer by anticipating what will be typed next and offering a selection of options to speed up data input and environment familliarity. Now Red-Gate have produced an editor add-on which can be freely downloaded, at the moment, to apply this feature to the different editors that can be used to develop code in the SQL language. You can get it Here, along with further details about it!

UPDATE NOTE on 2007/01/24

There has been another revision to the software to address issues and include suggestions from current, and previous users!

'SQL Prompt now performs with complete consistency and is also much faster than the previous version. A range of time-saving newfeatures includes:-

"Expand wild-cards" tab option
- rapid tick-box column picker
- code completion for: * table, view and column names * stored procedure names * USE * JOIN/JOIN ON
- auto-uppercasing of keywords
- configurable auto-popup after keywords
- personalized snippet list'

Thanks for the update Nick!!!

A Nice Guide to Kerberos

Here you will find a nice article describing Kerberos. Which a method of securing communication on a computer environment.

SQL Server 2005: Set the Stage for a Smooth Upgrade

Here is a useful article from Technet which shows you how to do a successful upgrade to SQL Server 2005.

Who says being a Microsoft focused Techie is getting tougher

With the distinction of what is expected from given technical job roles being continuously impacted by Microsoft exams and courses, the expectations of the individuals performing these roles is also getting confusing.

Here is a nice little outline of some of the skills covered within the professional DBA stream of exams Exam 70443 - PRO: Designing a Database Server Infrastructure by Using SQL Server 2005 and links to some of the resources that are purported to give you these skills.

Wi-Fi Security Test

You can never be too sure what games others are playing so being able to check your wireless connection using a Wi-Fi Security Test is of great value. What is more, once you have checked the connection, if you find out it is insecure then you can secure it with a download from the same site.

VMware Virtualisation Kit

You must have heard about VMware. Some extremely powerful and useful software for creating multiple virtual machines with possibly different operating systems on one physical machine. Well now you can get this along with some helpful documentation including a white paper "Reducing Server Total Cost of Ownership" and case studies of organization units that have successfully deployed it at VMware Virtualisation Kit.

SQL Server 2005 Books Online SP1 CTP (March 2006)

You can get the latest version of SQL Server 2005 Books OnLine (BOL) from here now.

Microsoft SQL Server Management Studio irritation

One of the nice things about SQL Server 2005 tools is the integrated Application Development Environment that you get with the client tools. This Management Studio makes it so much nicer to work with script files, it groups them sensibly and allows you to tie connections to the script files automatically and all sorts of wonderful things.

However, it can become a nightmare to work with if you are in a hurry and you don't do things exactly the way that it wants you to do them.

This may occur because you are not given appropriate time to learn the environment properly, so you do things the best way you can rather than how the system expects you to do them.

Humans, generally, work in an object action style as opposed to action object, unless they have been programmed to do this with an environment that they have had to use for a long time.

With this style one expects all objects associated with a project to end up in the same directory, because all you are doing is file create each time, why should you care which menu option you used to create the file?

However, the environment is too 'clever' for the users good, if one creates a file and then moves it into the project then it moves the logical name in but doesn't change the physical location of the file. Then later, if you decide to clean the environment up in the operating system so that everything is nicely arranged you hit nightmare ally, because there is no relationship between the logical and physical names outside an INI, sorry I slipped, XML file with the extension ssmssqlproj in the project directory. And there is no link between this and the actual file locations so when you move the files into the project directory as well then the project breaks.

This wouldn't be an issue if the system looked within the current directory before consulting the XML file but it doesn't.

Therefore, my solution is to always finish off a project by putting all the files for the project in the same directory and then changing the XML file to set all the entries to just the filenames.

It works, who am I to question.

But if anyone out there can tell me where I can go to set this requirement as a permanent option then I would be very happy to hear from you. It is probably some hangover from Visual Studio and someone somewhere is having a laugh thinking oh all you have to do is set this flag in such and such a menu option and it is all fixed automatically.

Neil, Dave, Darren, James, Gordon, Thomas, Andy anyone. I am not fussy who comes back first, but there is a Belgian beer in it for the first one with a sensible response.

Using NTILE and a Function to facilitate batching

Often a customer rings up and asks which would be the best course for them, when this happens we can discuss the students and their project' requirements and suggest an appropriate course or courses for them. When we do this we have to keep in mind the kind of pitfalls students and thus the projects they work on can fall into.

A few simple omissions in knowledge can soon lead to the difference between an easily maintainable system and one, which is going to be a textbook project, which is going to have a 1:3 ratio between development and maintenance.

As an example, someone who is working on a SQL Server version 7 implementation may need to move onto SQL Server version 2005. If they were to choose just the upgrading courses then they may miss out on essential elements that were added in intermediate versions of the product which are not covered in detail on the upgrading courses. I put together a simple script, which shows two solutions to a commonly faced batching problem to demonstrate how it is not always the latest elements that are added to a product that make it powerful but sometimes, new elements married to existing elements.

This can often make the difference between whether we offer a MOC, QA or QA Custom designed course for the customer. We can even arrange specialised courses from partner companies like Solid Quality Learning; they are amongst the people that produce the MOC courses in the first place. It is all a matter of benefits and costs.

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

I am afraid I could not work out how to retain the formatting in a word document on this blog site. This is not a new thing when dealing with MS products, I remember many years ago when dealing with the likes of Lotus 123 and Excel, or Word and Ami Pro. It was often easier to move towards the Microsoft products than the other way round. I say nothing about the design of strategies to facilitate this, only that I have had to enrol a space on MS spaces. It is free as well ;-)

You will see the code for this entry there (My Space on MSN), mainly because it was easier to make it look good which means easier to read as well, which means easier to use!

Minimum cost maximum benefit what else can anyone ask for?

Trigger state manipulation - Handle with care

Occasionaly someone has a brainstorm and they think of a solution technique to a problem without thinking outside their problem to the surrounding environment. In other cases they consider the complete environment and decide to use the solution technique in a creative way knowing its limitations and its benefits. For the second situation it is of value to know that you can switch triggers off and on thus:-

CREATE TABLE TestTable
(
Col1 INT IDENTITY
)
GO

CREATE TRIGGER TestTrigger ON TestTable
FOR INSERT
AS
ROLLBACK TRANSACTION
GO

CREATE PROCEDURE InsertRow
AS
EXEC SwitchOffTrigger
INSERT INTO TestTable DEFAULT VALUES
EXEC SwitchOnTrigger
GO

CREATE PROCEDURE SwitchOffTrigger
AS
DISABLE TRIGGER DBO.TestTrigger ON DBO.TestTable
GO

CREATE PROCEDURE SwitchOnTrigger
AS
ENABLE TRIGGER DBO.TestTrigger ON DBO.TestTable
GO

INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
GO

EXEC InsertRow
EXEC InsertRow
EXEC InsertRow
GO

SELECT * FROM TestTable
GO

Of course I would never recommend using a rollback inside a trigger in a real world situation but it works for the demonstration here!

Using OPENROWSET in SQL 2005 for XML Schema Definition

In SQL 2005 you can associate a schema with a column to validate the domain values
that are inserted into a complex object represented by an XML document.

This is excellent because it means that you can validate xml that is inserted into
the new XML data type columns that are available.

However, one of my students didn't like the fact that you have to cut and paste the
Schema into a script used to create the collection, and I couldn't agree with him
more that this was not a very flexible approach if you were receiving the schema
from a third party. Ok so you can use BizTalk or other software to give a hand but
it would be better not to have to rely on anything else other than TSQL, unless you decide
this is appropriate in the given case.

I remebered you could use a linked server in 2000 and showed him how to do it using
this but then I had a look at the new features of 2005 and found that the OPENROWSET
Function has been enhanced for and will meet just this kind of requirement now as shown:-

This is the normal way of creating an XML Schema collection

CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS 'SomeSchemaLayout’

However, for flexibility we can do it like this

DECLARE @@Schema_collection XML
SELECT @@Schema_collection = Schema_Name
FROM OPENROWSET ( BULK 'C:\SomeSchemaLayout.TXT',SINGLE_CLOB)
AS Schema_File(Schema_Name)

Which can then be used as follows:-

EXECUTE ('CREATE XML SCHEMA COLLECTION myTestSchemaCollection AS '+ @@Schema_collection)

Unlocking files held by dead processes

That was close! Ever had a day when you have done a load of work on a virtual machine, documented onto a linked network drive for security and then the virtual machine dies. Then you may have come across a message that says you cannot access the file that you thought was safe from the host machine. Here is a life saver piece of software which you can use to see what is locking the file and to unlock it. Thank you Mr. Collomb UNLOCKER

SQL Server 2005 Data Encryption

Here is an exellent blahg entry for those who wish to know more about SQL Server 2005 Encryption but don't wish to plough through the books online Keith Combs' Blahg : SQL Server 2005 Data Encryption.

Free UK fuel prices. 10,020 petrol stations covered - PetrolPrices.com

For those of you that travel quite a bit during the year, and not via broadband, you have my sympathy. You may find the following site of value, it shows you the cheapest price of petrol available for a given region. Find the site here

ERD about the SQL Server 2005 System Views Map

A picture is worth a vast number of words as they say, and the words in this picture would be incomprehensible without it. Nice to see Microsoft setting a good example by sharing the internal design of the metat data tables for sql server 2005. Now all we need is for developers to do the same for the business portion of the internals and the users will be laghing! You can download a copy of the System Views Map here

The latest SQL Server 2005 Books Online

Instead of waiting for the next product release before getting a new version of online books for SQL Server, which was later changed to release via product updates. We now have a way of getting access to the latest information much more quickly. New versions are now placed here 'SQL Server 2005 Books Online' and are released independantly of software releases. This is probably due to the fast changing nature of them, via input from the customer, using the built in update links for direct feedback. Nice to see things developing naturally!