Friday, December 29, 2006

Sub reports embedded in a table

I've had a problem with inserting sub reports into a cell of a table. Every time I inserted the sub report into a cell. The size of the report ballooned out of control and i couldn't work out why.

Well i've found out, and its kind of obvious really, the size of the sub report was too large! I kept thinking it was the size of the filed on the sub report, but then i finally realized the entire report was being inserted not just the filed.

I cant believe I didn't sus that one out right away!

Monday, December 18, 2006

Creating PDF preview on the fly using

I was looking for a way of creating a preview of a pdf document on a web based app, and I came across PDFThumbnail.NET. (Details here).

Its a free control, that will take a pdf stream and output a jpeg preview onto a page. Its free, easy to use and fast. Also they answered a couple of questions I had on the product very quickly.
There doesn't seam to be many components kicking around for free like this, so i'd highly recommend it.

Sunday, December 17, 2006

Monitoring SQL Server using SQL Server Health and History Tool (SQLH2)

A couple of weeks ago my boss came across this tool called SQLH2. Its purpose is to monitor Microsoft SQL server, by collecting data about how the machine is performing and optionally what the SQL server services are doing.

The main part is one executable file that you schedule to run every day or so. This collects data about the server, how it is performing and what the state of the sql servers are running on that machine. It logs all this info into a sql server database.

The second part is a service that runs every few mins, and collects SQL server performance counter data. These get put into a local file and when the afore mentioned exe runs this data gets put into the database as well.

Lastly there is a suite of SQL reporting services reports ready made to look at the database, and provide you with all the information it has collected.

I did run into a couple of problems when installing it, the documentation is quite good but the interface to configure it is a bit clumsy you might be better of editing the xml config files by hand.
First thing is if you want to collect perf data from more than one machine at a central point, make sure you share the SQLH2\data directory's on the remote servers out as SQLH2PerfCollector$, and make sure the user running the exe has access to this folder.
Also I found it helped to run the perf collector service as a domain user.

Then, on the server that is collecting the data, make sure you add a new target section the the H2PerfConfigFile.xml file, with the relevant counters. There doesn't seam to be anyway to do this using the GUI config application.

Once you are up and running it seams to create some very useful stats, that might help in your next push for a server upgrade!

The only thing I couldn't get working (and I'm not sure if you can do it) was monitoring a SQL server instance on a cluster. When running the app, even on the server that was currently running the database it didn't find the instance. If any one has got this working can you let drop me and email on how to get it working.

Download the SQLH2 here

Update 28/12 - I emailed Microsoft about using it on a cluster, and the responded saying that it was'nt cluster aware. They did say though that you should be able to use the performance counters section by adding it to the xml config file on another machine. I haven't managed to get this working yet though. If anyone does please let me know

Tuesday, December 05, 2006

File locking probelm sending email from .Net

I've been having fun today trying to update some of our internal advert proofing software. I have some code that attaches an pdf file to an email that I was trying to update.

I was trying to move the pdf file to an archive when it had been sent, but was constantly getting errors along the lines of 'File is locked', 'File is in use' etc that I had never had before.

After lots of head scratching, and looking around the class, I found a dispose method for the mailmessage and the mailmessage.attachments. As soon as I called this method after sending the mail, everything worked fine!

While looking round the class's i've also found some stuff on Delivery Notification options, sound like it might be worth looking into. Ill post on her when i've sussed that out. If anyone knows of any good articles about doing this let me know.

Sunday, December 03, 2006

Getting SQL Report Server usage

A couple of days ago some of our users were complaining about the speed at witch the reports were running.

I suspected that they were running some huge reports to get some year end data, but of course they denide that. I had seen a couple of tables that reporting services uses, and started poking about in those.

After a bit i managed to come up with the following sql that gave me a list of what reports had been run, who ran them, what parameters they used and how long they took to run:

SELECT TOP (100) PERCENT dbo.Catalog.Name, dbo.ExecutionLog.UserName, DATEDIFF(mi, dbo.ExecutionLog.TimeStart, dbo.ExecutionLog.TimeEnd)
AS Proc_time, dbo.ExecutionLog.Parameters, dbo.ExecutionLog.TimeStart
dbo.Catalog ON dbo.ExecutionLog.ReportID = dbo.Catalog.ItemID
WHERE (dbo.ExecutionLog.TimeStart > CONVERT(DATETIME, '2006-12-01 00:00:01', 102))
ORDER BY Proc_time

Please note that this works in SQL 2005 reporting services, i'm not sure if it will in previous versions. To run for different dates, just change TimeStart's parameter 

Converting RTF files to PDF using OpenOffice

Something really cool I found a while ago, and have started to use again recently is the ability to programmatically control OpenOffice (

I have used this in a VB.NET application I wrote that will look into a drop folder for a .RTF file, and convert it to a .PDF file. It took me a long while to work out how to (for free) convert a variety of office type documents to a pdf, this was by far the best.

If you would like the main source code for the function I wrote I can post it up here if anyone wants, alternatively check out this article on the code project website This was the article the originally helped me out, there is also quite good developer documentation on the OpenOffice web site.