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)