Tuesday, November 28, 2006

Mail Profile in SQL Server

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.

Monday, November 27, 2006

Downloading SQL Reporting Services RDL from website

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!

Sunday, November 26, 2006

Christmas is a comming

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

Usful SQL String Function (coalesce)

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

Wednesday, November 22, 2006

I'm Back (but not like a bad smell)

I've not used this blog for a while, decided to start again because I keep needing to go back to things I've done at work but can never remember what the hell I did.
So everytime I do something a bit complicated or something I think other people can use ill post it here. Everyone's a winner!

Since last time I used this blog, me and my girlfriend are now engaged! I happened in Dublin when were where there on the piss for a couple of days with our mates, it wasn't planned, it just kind of happened! Really need to get my finger out and get a house now!

Saw the Killers on Monday at Manchester Apollo, they were really good, the stage looked well cool. It was made out to be like somewhere you would hold a barn dance. They had a really crap supporting band though called the Brakes, I've heard a couple of there songs before and didn't think they'd be too bad, but they were bloody awful. The bass was up way to high and the singer (who looked like Michel Stipe from rem) had a dead annoying voice.

Seeing the mighty Tenacious D next month as well, should kick some serious ass!

Ashes starting tonight, in Aus though so not going to be able to watch it very much. Might be a good thing cos I think we are going to get tanked.

Listening To: Tenacious D Pick of destiny sound track

RIS Setup on Windows2003 Server

So, we currently have a RIS server setup on Windows2000, its now very out of date, the base image has only got XP SP1 on and I cant see anyway I can just upgrade it, we can only ris clients from the subnet the servers on, and we needed to run the DHCP service on the server.
So we decided to create a new Windows2003 based ris server. This is what I had to go through to get it working:

•First of all I needed a copy of Windows XP with service pack 2 on it, to use as my XP base image on the server. There's quite a good web site about it here

Basicly you copy the contents of the XP cd to your hard disk, extract the sp2 download to your hard disk (xpsp2.exe -x:c:\sp2), then run the update app in the i386\update folder of the extracted sp2 and point it to your original xp file. Then you'll have a nice and up-to-date copy of Windows XP. Copy it onto cd or a server assessable by the new ris server

• Now just install the RIS service through the control panel/Add remove programs/Windows Components. Then you should get a Remote Install service under admin tools. Before running that you need to Authorize the RIS server on the domain. You do this (for some reason) using DHCP manager. See the Microsoft article here. Once you have authorized the server, run the new RIS setup application, point it at your new xp setup files and you should have the RIS server ready to go.

• The next thing we needed to do was not run the DHCP server on the same server as RIS, and get ris working across subnet's. When we first installed ris I couldn't find any way of doing this, but I have this time!

There are 2 scope options you can set in DHCP:
066 - Boot Server Host Name (Basicly the TFTP server the client will use)
067 - Boot File name (the file to use on the server to boot the PC)

Set the boot server host name option to be the IP address of the ris sever (give the server a static address) , and set the boot file name to be OSChooser\i386\startrom.com

Add these options to each DHCP subnet you want to use this RIS server on and the PXE clients should be able to find the RIS server and its boot file.

• Now one of the most tricky parts. Adding 3rd party drivers to the base image so that a) the basic RIS setup program will run, and b) the Windows XP setup will run.
We did most of the hard work with this when we setup the old RIS server. The best article I found on it is here. This article basicly says copy certain driver files to certain parts of the folder structure, and update your .sif file to look for the files in these location. When we did this originally we had to manually hack the drivers inf files to get them working on Dell GX270's and 280's but I don?t think that's required anymore. If you need the drivers email me and I can send you them.

• At this point you should be able to now boot your client pc, and it should pick up the ris server and boot from it (as long as you have setup the DHCP options for the subnet your client is in).
Before doing this I copied in the existing ripref.sif file from the old server, as this contained all the options about joining the domain, the product key ect. Its probably worth sorting this file out before doing an install or the install will ask you questions and it still wont be unattended There are various sources on the web that will tell you about the settings, or use can use the RIPrep wizard, as described here

• Something that I never came across on the Windows2000 RIS was it failing to join a domain (if you set that option in the .sif file) If this happens you might have to run the delegate control wizard at the root of your domain in active directory users and computers. Just add the domain admin's and any other groups that will be RISing, and make sure they have the ability to add computer accounts to the domain.

• The final problem I came across was when I pushed an image back to the server. It went back no problems, so I copied into the templates folder of the new image what i thought was a good .sif file.
When I then tried to use the new image, i kept getting an error saying
'txtsetup.sif is missing or corrupt. Error 21'
I had a look around for this error and it turned out the problem was with the .sif file I had copied in. There were some new options in the [OSChooser] section, that the image needed to find the original mirror. So I re-created my new image, and instead of copying the good .sif file over the one it created, I just added all the sections I needed to this one.

That was it, took me a few days to get it working correctly but its definitely worth it in the long run, when you have 150 machines to setup!