Latest support Information for Microsoft SQL Server

You can go directly to a URL to pick up the latest Microsoft SQL Server support information by clicking on the link in this paragraph

SQL 2000 BUG Report and Workaround

Issue:- Login names that contain leading zero characters are not visible when you use SQL Profiler to audit connections to SQL Server 2000

SQL Server Developer Center: Using CLR Integration in SQL Server 2005

This is Microsoft's latest take on CLR and SQL Server T-SQL Integration and the appropriate times to apply the technologies. A nice article that has been rated 8 out of 9 by readers which is best and when?

What is held in MSSQLSYSTEMRESOURCE.MDF ?

This came up as a question this morning and I found a great answer+info Here.

There are answers to other questions you may have as well if you click on the title within the page !-)

SQL Server 2005 Online Training FREE.................

Those nice people at Microsoft have gone and provided poor IT students (are there such?) with access to free learning materials, at least for a time, I would book up and learn as soon as possible before they start charging!

This is great to see, it brings the same kind of dedication to education of the IT professional that MS first demonstrated on the home front, for the private individual. Their strides into the development of tools like the Encarta Dictionary made resources available to the modern child that wouldn't have even been a dream in my childhood! Now we hear of clockwork PC's to be made available throughout the world for less than a hundred pounds each!

I have tried these courses and they are rather good, although the questions are a bit light in the current versions, and there are a couple of areas that could have done with a bit more QA.

If you are here, then bookmark this now

More learning resources for SQL Server 2005

There is a site called SearchTechTarget.com which contains a very useful resource guide for anybody interested in learning about the latest release of SQL Server Here.

MS releases SQL Server 2005 and aids learning

Now that SQL Server 2005 has been released to manufacturing it is time to ramp up the learning. It and the associated tools that Microsoft have released over the recent days are going to cause a lot of flurry in the training world. Because the products have been tried and tested so much by potential customers before the launch, they will add value straight out of the box. This means reduced business risk of using them and less of a need to wait for the products to stabalise. And an increased risk of waiting to implement them, because the features offer business advantage that was hard to deliver with previous incarnations of the products.

To smooth the learning curve microsoft have produced online learning material covering "SQL Server 2005 New Features for Developers".

You can get access to the virtual environment where this material is available at http://www.msreadiness.com/IL_abstract.asp?eid=5007360.

Kill that errant xp_cmdshell initiated process

Sometimes you end up with a Windows process that really should be terminated but you don't have power to terminate it, even as the administrator on the machine.

This can cause major problems if a curious individual decides to try out something that you mention that should be avoided.

Today I had one of the instructors from another technical area in the class. When I mentioned the xp_cmdshell command and how it can be really troublesome if someone leaves a client application on the server and it is inadvertently activated, he went ahead and tried it.

Now running Notepad.exe via xp_cmdshell is not a good idea. However, I am glad he did it because I learnt something new.

If this happens again I will know that you can use the AT command as follows:-

AT time /INTERACTIVE cmd

time is replaced by the time that you wish to run the command

/INTERACTIVE means that you want to use the outcome of the command and then terminate it yourself

cmd is the command you want to activate.

Set the time to a couple of minutes into the future and submit the line at the command prompt. In a couple of minutes a new window will open up and you can run taskmgr.exe which will bring up the taskpad. Now you can delete the errant notepad process without complaint from the system. However, be careful not to delete anything else and close down the command window straight away.

The good thing about teaching is that you learn something new each time you run a course, it may appear to be peripheral information, but it usually isn't.

Thanks Brian for your contribution to this knowledge byte ;-)

Number of working days between 2 dates in TSQL

I had no idea that this piece of code would be generally useful, but apparently there are a lot of requests for this kind of thing so here it is, for anybody else that finds it of value, please use it freely in your own stored procedures or functions, with appropriate testing of course.













PS If there are other such routines that you know are required, then let me know. If I cannot resolve them I am sure I know a person who can!

What is your view of SQL Server 2005

A nice feature of the MS SS Management Studio is made available within the Tools Menu. A sub-menu item within here is External Tools ... From within here you can create your own links to external programs.

If we put this together with the fact that the OS is composed of a set of independent components, and that each of these components is serviced by a Management Console Snap-in, we can start seeing things in a way that is comfortable to us.

This option came in very useful at one of the demonstrations that we did at SQLPASS in Dallas last week. The demo was designed to show the automatic escalation of the scope of a transaction from a single phase commit to a two phase commit, based purely on the system identifying evidence for the need within the code.

Well we could have just told the attendees at the conference that this was happening in the background and asked them to believe us. But we decided to teach outside the intended scope, to show how the OS could be combined with SQL Server using an OS Tool from within the Management Studio.

As you can see in the image. Setting the thing up is very simple. You simply specify the program that you want to run and the arguments that you want to pass to it. A lot of flexibility is included as you can see from the listbox, but we just used a previously saved snap-in called 'DTCMonitor.msc' located at "C:\Documents and Settings\Administrator\Start Menu\Programs\Administrative Tools\" as the argument.

What does it do, well when you run a distributed transaction it shows you the transaction getting listed and de-listed as it is processed.

Doesn't seem like much, maybe, but it does give you a comfortable feeling that you can see the magic happening! And the best bit, we have just extended the MS SS Management Studio to enable it bring up the snap-in window when someone chooses Distributed Components Monitor from the Tools menu.



This offers a very flexible way of extending the Management Studio to help people to use the environment in a powerful way, so go play!

To find out more about how to create your own Microsoft Management Console, with your choice of snap-ins, got Here

Easy share

For those that have not yet realised, Microsoft are heavily into knowledge sharing. One of their best resources is their set of webcasts. If you click on Webcasts (below in blue) you can search for appropriate webcasts by product and language. You can extend your knowledge even further by trying out their products online, under guidance. You don't even need to buy a machine to use these resources. You can access them, via any public library. Basically, If you are here then you can go to either the Webcasts or the Hands on Labs by clicking on the blue words.

What a Nugget

Have you ever wished that you could create a macro with TSQL that you use quite frequently, while working interactively via the query editor, well I have. So I was very pleased to see a nice feature within the new MS Management Studio. This is how it works.

You create a procedure with your TSQL. You create an association with the keyboard keys of your choice, from those available to you, via the dialog accessed via Tools\Options\Keyboard.

That's it, job done.

Watch those Schema's

One of the new features of SQL 2005 is the use of Schemas. These can be perceived of as adding value to the life of a DBA in various ways including the following two:-

One view of Schemas

One of the benefits derived from them is the ability to group (securable) objects together to facilitate ease of security management (allocation of permissions). The other end of the security management link is the use of principles (pre-authorised groupings of resources).

For example

A principle (Salesperson1) who belongs to a higher principle (SalesRole) can have select permission on all tables within the schema (SalesSchema), one of which is 'Sale' by the one entry in the system tables corresponding to the instruction

GRANT SELECT ON SCHEMA::SalesSchema TO SalesRole

This security permission can be exercised either by referring to the object name using the full schema path for the object

SELECT * FROM SalesSchema.Sale

or by ensuring that the principle is associated with the schema as part of its specification.


View two of schemas

Another benefit of them is that within a schema we don't have to worry about the order in which objects are created, they are checked as a group after their creation rather than individually. Do I hear a cheer from all those case tool users who have been affected by the errors caused by bad ordering of automatically generated TSQL, no, then maybe you should find out about case tools, they have many good aspects as well.

However, be careful about this one feature within SQL Server. Because although it is the case that all objects created within a schema at the time of schema creation, are checked as a group. There is no concept of dropping a group of objects belonging to a schema by dropping the schema itself. You must drop all objects within the schema first. Otherwise you will get an error message.

So ensure that you create the tables in the order Parent -> Child -> NextChild -> etc in your scripts, even if they are being created within a schema. Then if you need to remove them at a later date, using a dynamically generated script, you can order them correctly using their id.