December 2009 - Posts

Starting/Stopping SQL Server using Powershell
31 December 09 07:58 PM | MartinBell | 2 comment(s)

One thing I would like to see is more examples of using Powershell in Books Online! For instance the topic Managing Services and Network Settings by Using WMI Provider doesn’t have an example using WMI. So here is one!

# Get an instance of the ManagedComputer object that represents the WMI Provider services.
CD SQLSERVER:\SQL\LOCALHOST
$mc = (get-item .).ManagedComputer
# Reference the Microsoft SQL Server service.
$svc = $mc.Services['MSSQLSERVER']
# Display the state of the service.
'Service status is:' + $svc.ServiceState
# Stop the service if it is running and report on the status
$svc.Stop();
# Wait until the service has time to stop. Then refresh the status
wait-Event -Timeout 10
$svc.Refresh();
# Display the state of the service.
'Service status is:' + $svc.ServiceState
# Start the service again.
$svc.Start();
# Wait until the service has time to start, refresh and display the state of the service.
wait-event -Timeout 10
$svc.Refresh();
'Service status is:' + $svc.ServiceState


The wait-event the service status is incorrectly reported. This is achieved in the books online example by looping until the status changes:

Do Until String.Format("{0}", svc.ServiceState) = "Stopped"
          Console.WriteLine(String.Format("{0}", svc.ServiceState))
          svc.Refresh()
Loop


This uses the SQL Server Provider to get the Managed Computer object, although this is not absolutely necessary, the following script does not use the SQL Server Provider:

# Reference the Microsoft SQL Server service.
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
# Display the state of the service.
'Service status is: ' + $svc.State;
# Stop the service if it is running and report on the status
$svc.StopService();
# Wait until the service has time to stop. Then refresh the status
wait-Event -Timeout 10
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
# Display the state of the service.
'Service status is: ' + $svc.State;
# Start the service again.
$svc.StartService();
# Wait until the service has time to start, refresh and display the state of the service.
wait-Event -Timeout 10
$svc=(get-wmiobject win32_service -filter "name='MSSQLSERVER'");
'Service status is: ' + $svc.State;

Although the SQL Server Provider makes it very easy to navigate around a SQL Server instance, it may not be available to everyone; therefore for portable scripts you may wish to write without using it. In my next post I will explain why.

Filed under: ,
Developer Day Eight
16 December 09 06:57 PM | MartinBell | with no comments

The DDD organisers have announced the the next community Developer Day Event at Microsoft’s UK Campus in Reading on Saturday 30th January 2010!  

The call for speakers has opened so anyone wishing to submit a session, can do so at http://developerdeveloperdeveloper.com/ddd8/

DDD is always a great event and I hope to see you there.

Filed under:
Powershell 2.0 has slipped out!
12 December 09 01:31 PM | MartinBell | with no comments

Thanks to Anthony for point this out at Thursday’s User Group meeting. Windows Powershell 2.0 was released as part of the the Windows Management Framework release at the end of October. If you are not a Windows admin you may have missed it! See here for more details.

Filed under:
A nugget!
08 December 09 08:56 PM | MartinBell | with no comments
Here is something I thought was very nugget worthy!

Q: If you have a delimited string how can you easily find the number of elements without splitting the string?

A: This is one way!

Example:

A comma separated string with 4 elements -

DECLARE @str varchar(max) = 'F7C52CB0,980CADCC,1C4195F2,7881934F'

SELECT LEN(@str), LEN(REPLACE(@str,',','')), LEN(@str) - LEN(REPLACE(@str,',',''))+1

 

Filed under:

This Blog

SQL Blogs

Syndication