Friday, May 20, 2005

I saw this cool product called SSW SQL Audit.
As soon as I find some time I'll test it. I'll keep you updated.

The UPDLOCK locking hint is a very interesting one.
It prevents other connections from updating a record but allows other connections to still read the information. It assures you that your process will be the next in line for an exclusive lock. SQL Server actually uses update locks before acquiring an exclusive lock (eg. UPDATE with a WHERE clause). It happens so fast most of the times that they probably won't popup in the locking information you get to see.

To prevent conversion deadlocks (typical in a read and update transaction) it is a good idea to use this locking hint (you'll probably want a REPEATABLE READ or SERIALIZABLE isolation level for these kind of transactions too).

Thursday, May 19, 2005

Finally found some time to stress test our Reporting Services solution on the servers that will soon run in production.

With 1 DB server and 2 App servers we are generating 32 reports/sec. Quite a nice result as our target was 8/sec :-)

If you want more information or details feel free to contact me.

Wednesday, May 18, 2005

Reporting Services weirdness :s

In order to get a text from a database to display in the page footer you have to include a textbox in the data region and then reference this textbox in a second textbox which you create in the footer region.

No problem so far, a bit weird but not too much trouble. However, when exporting to PDF the footer would just display on the first page and not on all the following pages.

I have solved it by defining an extra parameter, clearing the prompt text and setting the default value to From Query. Select the correct Dataset and the field you would like to use.

Works like a charm.

There was one little issue left though. The report would fail if the stored procedure that retrieved the parameter value would not return any records. I have solved this with the following simple yet effective workaround:

DECLARE @Description varchar(1024)

SELECT @Description = [Description] FROM tbl_document_caption WHERE ...

SELECT @Description as [Description]
Check out this website.
It was created by a friend of mine and I promised I would promote it :-D

Tuesday, May 17, 2005

It appears that there is an issue in SP4 for SQL Server 2000 with AWE.
From the Microsoft website:

Warning: Microsoft has found an issue with the final build of SP4 that
impacts customers who run SQL Server with Address Windowing Extensions (AWE)
support enabled. This issue only impacts computers with more than two gigabytes
(2 GB) of memory where AWE has been explicitly enabled. If you have this
configuration, you should not install SP4. Microsoft is currently working on the
problem and will issue an update soon.