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.