Tried to setup SQL 2000 today so it would email me when certain events occurred, I have done it before but it took me a bit to work out why it wouldn’t work this time. Couple of things I had to do:

• I needed to install Outlook mail client on the pc. (I suppose any mail client will do as long as it allows you to setup a mail profile)
• The account that I was running the sql server agent service as was a domain account not the default system one. To get it to see a mail profile I had to login to the server as the user I was running the service as, and setup outlook so it had a mail profile.
• I was using a mailbox we setup to receive log files from, so it was created under a different user. I had to give the user the SQL agent service was running as rights into this mailbox.
• After a restart of the Agent service I could then send mail.

I also come across something else today witch look quite cool. Its called the SQL Server Health and History tool, and can be downloaded here
It will log various things about SQL installs to a database, and there is a report pack you can download so you can look at the info.
I’ve not managed to get the performance counters working yet, and its only logging from one server but when I get it all working ill write it up on here.

Found quite a funky thing out today that i didn’t know about with SQL reporting services. You can download the .rdl file from the web based reporting interface.
This could be very useful if you have lost a copy of the report, or want to look at how someone else has done something

• Within the web browser interface into the reports, click on a report, then click its properties tab
• On the general section, just above the apply etc buttons is a little section called Report Definition
• Click on edit and this will then download a copy of the report as a .rdl file you can open in visual studio, easy!

Quite excited about Christmas this year, not sure why. Think it might help that the ashes are on over Christmas as well, so that’s a good excuse to stay up and drink more. Going of the first test so far its not looking very good though.

Going to have an ashes party next week when my peps are on holiday, maybe by then England might start playing a bit and looking like there might be a slight chance they can win at least one game.

Managed to lose at footie again this aft, cant remember score but wasn’t to bad to say we only had 4 of our normal team and we played a team who normally beat us anyway

Listening to: Dashboard Confessional, nice and chilled out for a Sunday afternoon

I found this function a couple of weeks ago and found it very usful. I had to add a filed to a report, that contained a list of other fileds from the report, each seperated by a comma. At first i thought i’d have to write some custom code, but then I found the COALESCE function.
You can use this in the following way, to create one output from x number of records, seperated by a comma.

DECLARE @aa varchar(100)

SELECT @aa = COALESCE (@aa + ‘, ‘, ”) + CAST(name AS varchar(5))
FROM tbl_Customer

WHERE (Customer_bal > 10)
SELECT @aa AS Names

I then added this sql to a sub report and passed the id of the line to it, so I could then have this showing for each record. The time it took to render the report increased, but the user got the information they needed.
I then had to write some custom code into the report so it would sort the results in a predictable way. You can read more about the function here