Friday, 29 July 2011

Calling SCOM in C#

Written by David Postlethwaite

I’m sure there are many out there using System Centre Operations Manager (or SCOM for short) it’s a great tool for monitoring the Windows estate.

One area where it is lacking is updating the subscribers list for a Notification Group – in other words changing the on call person for a department.

Ideally this should be done by each department but the only person who can do this a SCOM Administrator . You don’t really want to be giving everyone full admin access to the SCOM console just for this purpose.

One way round this is to create a stand alone app or web site that updates the subscribers. It is possible to do this using the .NET Microsoft.EnterpriseManagement class.

In my case I created two tables one with a list of the Notification Groups that have been defined in SCOM and another with a list of staff and their cell phones. On the web site these are held in dropdownLists. You select the Notification Group, the staff member, click OK. and it updates SCOM. Simple.

The web page also interrogates SCOM to show who is currently on call.

In these examples there is only one subscriber for each Notification Group and we simply overwrite that subscriber. It wouldn’t be too difficult to loop through a list of names.

Here’s code to retrieve a subscriber from a Notification Group called DeptName.

        public static string FindSCOM_Subscriber(string DeptName)
string SCOMServer = "ServerName";
string returnString = "";
// make connection to SCOM Server
ManagementGroup mg = new ManagementGroup(SCOMServer);
// connect to the Notification Group
Microsoft.EnterpriseManagement.Administration.NotificationRecipient connector = new
//retrieve the subscribers
Microsoft.EnterpriseManagement.Administration.NotificationRecipient name =
//return the Name and Address (i.e. the cell phone number)
returnString = name.Devices[0].Address.ToString() + "; " + name.Devices[0].Name.ToString();
catch (Exception objException)
// if no values are found then an error is reported so we'll just return a suitable text string
returnString = " No Value";
return returnString;


Here is code to update a notification group called DeptName with the subscriber called OnCallName and a phone number of PagerNo for

In this case it overwrites the only subscriber.

public static string UpdateSCOM_Subscriber(string DeptName, string PagerNo, string OnCallName)
string SCOMServer = "ServerName";
string returnString = "";
// make connection to SCOM Server
ManagementGroup mg = new ManagementGroup(SCOMServer);
// connect to the Notification Group
Microsoft.EnterpriseManagement.Administration.NotificationRecipient connector = new
//retrieve the the subscribers
Microsoft.EnterpriseManagement.Administration.NotificationRecipient name = connector.ManagementGroup.GetNotificationRecipient(DeptName);
//Get the current recipient pager no
string RecipientAddress = name.Devices[0].Address.ToString();
string RecipientName = name.Devices[0].Name.ToString();
name.Devices[0].Name = OnCallName;
name.Devices[0].Address = PagerNo;
returnString = DeptName + " successfully changed from " + RecipientName + " to " + OnCallName;
catch (Exception objException)
returnString = "An Error occurred: " + objException.Message.ToString();
return returnString;

In conclusion this code looks quit straight forward now but at the time it was quite hard work to work out how to use the class and get the syntax correct. There were few examples around for .NET and in the end I had to translate a powershell example.
I hope it helps someone else

Wednesday, 27 July 2011

Searching for Wild Card Characters

I got asked an interesting question recently, I was demonstrating how you can use wild cards in a WHERE clauses when someone asked ‘How do you search for a wild card character when using a LIKE based WHERE clause fro string comparisons. I must have looked stumped for a minute because my mate Jim happened to mention that I did,  add then asked ‘How I was going to ESCAPE from that’

That's how we search for a wildcard character when doing a string comparison, we escape the wild card characters. The code below shows how we do this:


--Create table
create table t2
(id int, Name varchar(35))

--Populat the data with some data
declare @i int
set @i =0
while @i <= 10
insert into t2
values (@i, REPLICATE('A',10))
set @i = @i + 1

--Add a Wild Card to one of our records

update t2
set Name = 'gethyn % Ellis'
where id = 5

We have created a table added some rows to our table and we have added some wild cards to our varchar column. Next we’ll do a string comparison looking for our % sign in the name column.
 --Search for the % sign by escaping the character
-- There are a couple of ways to this

--In this first query the [] brackets tell the database engine to treat % as a normal character and not a wildcard
select * from t2
where Name like '%[%]%' --escape '%'

--In this second query we have defined a '\' as they escape character which means the next character will be treated as literal
select * from t2
where Name
LIKE '%\%%' ESCAPE '\'

--both queries return the same results

Both queries return the same results. In the first query we used the [] brackets to tell the database engine to treat the % as a normal character and not a wild card. In the second query we have defined ‘/’ as the escape character which the next character following the escape character will be treated as literal and not a wild card

Now this example is a little contrived and probably would not be used in the real world, so where would it be used. I have seen it used in when looks for offers 25% off etc. So retailer, banks, anyone that offers a percentage discount may need to know how to search for wild card operators in a string comparision

Monday, 25 July 2011

IT Professionals, the Job Search and the Killer CV

It has been while since I wrote anything on my contracting blog but over the past month or so I have had several  questions from people who are thinking about their future and what they want from their career. This includes people thinking about both permanent and contract work.

Whether your looking for your next permanent job or your are considering contracting and hence going into business in your own right then one of the most important things you need, before you start, is a ‘killer CV’.

I’m not going to go into much detail on how to construct, write and layout your CV there are lots of other sites that have already covered this and there are some useful links at the bottom of the post that maybe useful.

I like to think of my CV as a sales brochure, especially if you are contractor in business for yourself then that is exactly what your CV is, it’s a document that is used to you in selling your services. For those of you who are sales people or if you are just like me  and have read a few sales books (The Sales Bible: The Ultimate Sales Resource) then you know that the key to making a sale is solving someone’s problem. If you think of a job description or contractor requirements documents  as someone going to the trouble of documenting their problem they need solving, then your CV must highlight how you can actually solve the problem documented. If your CV clearly states how and why  with evidence to back up your claims of how you can help solve the clients problems then you are more likely going get ‘follow up leads’ a phone call or face to face meeting to further discuss the clients problem and how you can help solve it. Some people call this an interview. For example if the client hiring a contractor or consultant and  needs a specialist in SQL Server replication, you know this because it will say ‘experience with transaction replication’ in the requirements document  and your CV right up front on the first page where nobody as to look very hard to find it, preferably in your expertise section, state ‘Experience in Transactional Replication’ and then further in your document you provide evidence of this experience then you will very likely go further in the recruitment process. It is the first page of the CV that is the ‘killer’ if you show on that page how you meet the requirements,  then you are likely to go further and have more success in the ‘Sales’ process.

Check out: if you would like some further information and writing and preparing you CV. It is a contractor site but some of the information can be useful for permanent job searches too .

If you want a book on the subject then I have bought this one, which I found quite good but only has 3* review on Amazon.

The best reviewed CV book that I could find on Amazon is this:

It’s up to you but I would start with the free articles on the website and build from there.

In future posts I intend to look at some common mistakes that I have seen in CV’s and I’ll discuss further the importance of tailoring your CV.

Friday, 22 July 2011

SSRS: Dynamic Data Sources

I have been building dashboard using Reporting Services (SSRS) for a client of mine. The dashboard is going to be used to display useful information on their SQL Server instance, the information contained in the reports will be retrieved from DMVs. Information like the SQL Server wait types, information on a SQL Server instance, along with other useful information such as page life expectancy (PLE) buffer cache hit ratio, Expensive IO queries and the like.  This can be useful when performance tuning and monitoring SQL Server. How I went about creating this report will follow in future posts but in order to get this information ‘real time’ from several different SQL Servers required me to build a dynamic data source.

In this post we will look at how we build this dynamic connection.

First up we need to create a database to hold information on the servers we want to connect to. There is likely to be a nice easy way of getting this information for you automatically using something like PowerShell but for the purposes of this post we will collect the data manually.

Create a database and a table to hold the SQL Server information:

/*create a database to house the server and instance information
--this can be any data source you like
--in my case it's SQL Server database*/
USE [master]


/*Create a simple table to store the SQL Server information*/

USE [ServerList]

CREATE TABLE [dbo].[ServerList](
[Severname] [varchar](50) NULL

/*Populate the table with some server names*/

INSERT INTO [ServerList].[dbo].[ServerList]

Create new Report Server Project

We have our list of servers that we want to connect too stored nicely in a database table, next we will create a Report Server Project and  create new report. Our report will show wait times for each server instance, the query for the report is courtesy of Glenn Berry’s (Blog | @GlennAlanBerry) Diagnostic queries. So we’ll create a new project


Once we have a new project we need create a new report, right click the reports folder in the “Solution Explorer” and select <New Report> this starts up the Report Wizard:



Create  a data source to an instance of your choice, we will change this later to connect to a server from a drop-down list.


Click next and plug in Glenn’s waits query:



Complete the report wizard and we should have report that connects to our server and displays information on what SQL Server is waiting on.

Create a Data Set for the server list

Next we need to create a data set that connects to our list of servers


We have a data set based on our server list data source. Next up we want to plug this dataset  into a parameter

Create a Parameter

Right-Click on the <Parameters> folder and select <Add Parameter> call it ServerName


Select <Available Values> and select <Get Values from a query>


The final thing for us to do is pass our parameter into our waits data source so we connect to the correct server as selected by the report viewer. To do this we need to change our waits data source to pass the parameter in as the server name. The code looks similar to this:

="Data Source=" & Parameters!ServerName.Value

We plug this code into the expression on the waits data source



When we preview our report, we will need to first select a server from our drop down list, and select <View Report> we will then get the waits information from the sever chosen.

Wednesday, 20 July 2011

DOS Batch File Date and Time

Written by David POstlethwaite

A non SQL blog for a change
Ever needed to create a batch file that wrote a time stamped log file that ensures the day and month number always have two digits?
Try this:
SET Now=%Time: =0%
Set YYYYMMDD=%DATE:~6,4%_%DATE:~3,2%_%DATE:~0,2%_%now:~0,2%%now:~3,2%
echo running Batch File.... >> %YYYYMMDD%.txt

I hope you found this helpful, please feel free to leave a comment.

Monday, 18 July 2011

Execute Permissions

Written by David Postlethwaite

Security of the SQL database is becoming more and more important these days yet I’m finding that vendors often overlook setting minimum required privileges on a database. Many database I’ve been given to install just assign db_owner to the user accounts that really only need read and write. This means that those accounts can do whatever they want to the database, even dropping it.

But if you try and fix this by limiting that account to datareader and datawriter you often find that the application no longer works because the account can’t execute any stored procedures or functions. The vendor has relied on the fact that db_owner can do anything so no explicit execute permissions have been given.

So how do we fix this?

In a previous article Gethyn showed how to grant implicit execute permissions to the whole database to a new role called db_executer.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

Then you just need to give the users datareader, datawriter and db_executer and hopefully everything will carry on working.
But from a support view , if another DBA looked at the properties of a stored procedure in SSMS he wouldn’t immediately see this as the explicit permissions screen would not show that the user had execute permissions for that particular stored procedure.

To assign explicit execute permissions to every stored procedure and function something slightly more complex is required. The code below will find all the stored procedures and functions in a database and create the appropriate T-SQL statement to grant the db_executer role execute permissions (or select permissions for a table value function)

For safety this will print the T-SQL, you will need to uncomment the exec line if you want it to run. Or you could put the output to text and copy and paste it into a query window and run it like that

CREATE ROLE db_executor

-- Declare Variables
DECLARE @cmd1 varchar(8000)
DECLARE @SchemaName varchar(128)
DECLARE @ProcName varchar(128)
DECLARE @ProcType varchar(128)
DECLARE @FuncType varchar(128)

-- Create Cursor
select routine_schema,routine_name,routine_type,data_type from information_schema.routines
where routine_type in('function','procedure')
order by routine_type desc, data_type

-- loop through cursor
OPEN temp_cursor
FETCH NEXT FROM temp_cursor
INTO @SchemaName ,@ProcName, @ProcType, @FuncType

-- Build the cmd string
IF @ProcType='Procedure' OR (@ProcType='function' AND @FuncType<>'table')
SELECT @cmd1 = 'GRANT EXEC ON ' + '[' + @SchemaName + ']' + '.' + '[' + @ProcName + ']' + ' TO db_executor'

IF @ProcType='function' AND @FuncType='table'
SELECT @cmd1 = 'GRANT SELECT ON ' + '[' + @SchemaName + ']' + '.' + '[' + @ProcName + ']' + ' TO db_executor'

-- Execute the string
print @cmd1
-- EXEC(@cmd1)

FETCH NEXT FROM temp_cursor
INTO @SchemaName ,@ProcName, @ProcType, @FuncType

CLOSE temp_cursor
DEALLOCATE temp_cursor

I hope you find this useful

Thursday, 14 July 2011

Effective Permissions on a Database

written by David Postlethwaite

Ever wondered who has what access to your database or a particular object within? With the ever increasing need to keep permissions to a minimum this is becoming more important.

If you are using Active Directory groups to assign permissions to your servers and your databases it’s not always obvious what the combined permissions might be for a particular user who may be a member of several groups.You can check the effective permissions to a database using the following command.  Thus far I’ve been unable to find out where this can be found in SQL Server Management Studio (SSMS)

use databaseName
EXECUTE AS USER = 'domain\fred';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

This will only work for accounts that have a specific database permission. If you try it with a sysadmin  account which has permission through the server role but has no specific database permissions you will get an error like this:

Msg 916, Level 14, State 1, Line 1
The server principal "domain\serviceAccount" is not able to access the database "databaseName" under the current security context.

It has been useful in finding accounts that have managed to get full permissions by virtue of being linked to the dbo account, something which isn’t immediately obvious in SSMS

Moving down to a more granular level it’s also possible to check the explicit permissions to a specific object such as a stored procedure in a database:

use databaseName
EXECUTE AS USER = 'domain\fred';
SELECT * FROM fn_my_permissions('storedproc1, 'OBJECT')
ORDER BY subentity_name, permission_name ;

This command will not show the effective permissions, this can be seen in the permission tab of the properties of an object in SSMS.

And if you need to grant permissions to an object here is the syntax

grant execute on dbo.storedProc1 to [domain\fred]

In my next article I will show an easy way to grant explicit permissions to every stored procedure and function in a database.

Wednesday, 13 July 2011

We have a new contributor…

In order to keep the content fresh and the posts flowing here on my blog  I have decided to publish some guest posts. My friend and colleague David Postlethwaite (@postledm | Linkedin) has agreed to write some posts for David has been a DBA for a few years but has many years experience as developer in particular with the technologies. David has some SQL Sever posts lined up but is also keen to write some about c# and .net so we will get a nice mix of content.


Look out for David’s posts and feel free to leave any comments you may have, I’m sure David will appreciate the feedback.

Monday, 11 July 2011

Can you delete from a view?

I have seen this question come up several times over the last few months. Can you delete from a view? So I thought I would put a short post together to explain the answer.

What is a View?

First up what is a View? To paraphrase from Books Online (BOL) a view is a virtual table, the contents of which are defined by a query. Unless the view is an indexed view the data does not exist in the view directly but remains in the underlying tables. The database stores only the views definition (unless its an indexed view) therefore if you drop a view you don’t lose any data, you just the definition of the view.

Can you delete from a view?

So can you delete data from a view. so by the very definition of the view above the answer is ‘No’. There is no data in the view to delete, the data remains in the underlying tables.  Let’s assume though that real the question people are asking  is:

Can you delete data from the underlying tables that make up a view?

The simple answer is yes you can but with some caveats. If the query that is defined in your view is comprised of more than one table,   then you won’t be able to delete from the underlying tables . For example, if we use the Adventureworks2008R2 database and the [HumanResources].[vEmployee] view, the TSQL used to create the view is:

USE [AdventureWorks2008R2]

/****** Object: View [HumanResources].[vEmployee] Script Date: 07/04/2011 22:23:37 ******/


CREATE VIEW [HumanResources].[vEmployee]
,pnt.[Name] AS [PhoneNumberType]
,sp.[Name] AS [StateProvinceName]
,cr.[Name] AS [CountryRegionName]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea
ON bea.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = bea.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON p.[BusinessEntityID] = ea.[BusinessEntityID];


EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee names and addresses.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'VIEW',@level1name=N'vEmployee'

This view returns information on employees such as name address and contact information which is retrieved  from several tables.

Lets try and delete data from the underlying tables using the view:

delete [HumanResources].[vEmployee]
where BusinessEntityID = 5

If we run this code we get the following error:

Msg 4405, Level 16, State 1, Line 3
View or function 'HumanResources.vEmployee' is not updatable because the modification affects multiple base tables.

Therefore as the [HumanResources].[vEmployees] view has multiple tables we cannot delete from the view.

So what about views whose definition is based only on one table. I’ll create a new table in the AdventureWork2008R2 for this little test:

select * 
into dbo.emptabview
from HumanResources.Employee

I’ll create a simple view called delview based on my new emptabview table:

create view delview
select * from dbo.emptabview

We’ll run a query against the table to make sure the row we want to delete exists:

select * from dbo.emptabview
where BusinessEntityID = 6


Now I will try and delete the same record from the underlying table using the view:

delete  delview 
where BusinessEntityID = 6

(1 row(s) affected)

I’ll run the same select query from the table  and as we can see the row has been deleted:

select * from dbo.emptabview
where BusinessEntityID = 6
 (0 row(s) affected)

There we have it one row deleted from the underlying table by running a delete against the view.

To conclude, we can delete data from the underlying table of the view as long as the view is only based on one table. If your view is made up of multiple tables then you won’t be able to use the view in the delete statement.

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...