Skip to main content


Showing posts from 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.…

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:--Demo

createtable t2
(id int, Name varchar(35))

--Populat the datawithsomedata
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 %…

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 …

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 Po…

DOS Batch File Date and Time

Written by David POstlethwaiteA 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%.txtI hope you found this helpful, please feel free to leave a comment.

Execute Permissions

Written by David PostlethwaiteSecurity 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.CREATEROLE db_executor

Then you just need to give the users datareader, datawriter and …

Effective Permissions on a Database

written by David PostlethwaiteEver 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
EXECUTEASUSER = '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

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.

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 cav…