January 2007 - Posts

Quick example I've just posted on the NNTP news groups.

It's an example of how to create a running (cumulative) total in both a SELECT and UPDATE statements..

declare @data table (

    some_date     datetime    not null,

    some_value    int         not null,

    running_total int         null  --  will populate later


insert @data ( some_date, some_value ) values( '1 jan 2006', 1 )

insert @data ( some_date, some_value ) values( '1 feb 2006', 1 )

insert @data ( some_date, some_value ) values( '1 mar 2006', 1 )

insert @data ( some_date, some_value ) values( '1 apr 2006', 1 )

insert @data ( some_date, some_value ) values( '1 may 2006', 1 )

insert @data ( some_date, some_value ) values( '1 jun 2006', 1 )


select some_date,

       actual = some_value,

       running_total = ( select sum( some_value )

                         from @data d2

                         where d2.some_date <= d1.some_date )

from @data d1

order by some_date


--  To update...

update d1

    set running_total = ( select sum( some_value )

                          from @data d2

                          where d2.some_date <= d1.some_date )


from @data d1


select *

from @data

order by some_date



I know, its a bit political but I'm on the side that says we pay too much as it is....

Anyway, this is a petition against the introduction of pay even more as you travel driving which quite frankly would cost be a small fortune and the fact that only a fraction of the tax collected on fuel and road tax goes back on roads makes me sick and angry.

The link: http://petitions.pm.gov.uk/traveltax/

The description:

The idea of tracking every vehicle at all times is sinister and wrong. Road pricing is already here with the high level of taxation on fuel. The more you travel - the more tax you pay.

It will be an unfair tax on those who live apart from families and poorer people who will not be able to afford the high monthly costs.

Please Mr Blair - forget about road pricing and concentrate on improving our roads to reduce congestion.

Got a very strange one on HP kick and its common across the 3 servers I have here.; might be useful for other kit - worth checking!

Windows Server 2003 x64 R2 running SQL 2005 Standard x64.

On building and progressing through a migration project for my client I noticed that the backup database was only reading and writing at just 10MBytes per second, this on a server that is more than capable of 50-180MBytes per second as measured through IOMeter.

Anyway, the fix - BLOCKSIZE=65536; the backup now runs at 50MBytes per second instead of just 10MBytes per second - not sure why; BOL mentions that if you don't use a BLOCKSIZE then it doesn't use buffered IO - when I've time I'll research it, but I thought a heads up on something that might make your backups go FIVE times quicker...

Perhaps its just me, but I've never come across the performance differential on the backup / restore before.

One of the big problems I'm going to need to overcome now is how do I incorporate this into log shipping and maintanence plans - there is no way for you to specify a BLOCKSIZE, perhaps I'll do a request for change on it.

From BOL...

BLOCKSIZE = { blocksize | @blocksize_variable }

Specifies the physical block size, in bytes. Generally, this option is not required, as BACKUP automatically selects a block size that is appropriate to the disk or tape device. Explicitly stating a block size overrides the automatic selection of block size. 65,536 (64 KB) is the maximum block size SQL Server supports.

This option can be helpful if you are taking a backup that you plan to store on CD-ROM. To transfer the resulting backup set to a CD-ROM, set BLOCKSIZE to 2048.

If you do not specify the physical block size, restore uses buffered I/O, which can be less efficient, especially for large backups.



To register and for more information: http://sqlserverfaq.com?eid=91

6:30pm - 6.45pm
A chance to chat and exchange ideas with fellow SQL Server professionals

6.45pm - 7:00
Round Table Discussion
Update on what's been going on and is going on in the SQL Server space.
Bring your SQL problems and ask the audience, bounce ideas - anything
related to SQL Server.

7.00pm - 7.30pm
"Customisable Reports for SSMS"
Martin Bell, SQL Server MVP
Martin will show how to create customizable reports for SSMS

7.30pm - 8.00pm
"Business Scorecard Manager and Data Mining"
Melville Thompson
Business Scorecard Manager can be used to highlight issues, Melville will
show how these may be viewed on SharePoint and analysed using the Excel
addin for Data Mining. Once you know what the issue is, it can then be
flagged to managers using the Scorecard.

8:00 - 8:15pm

8.15pm - 9.30pm
"SQL 2005 - XML"
Martin Bell, SQL Server MVP
This talk will show you how to use the new XML datatype in SQL 2005, changes
to the FOR XML statement, XML data manipulation and using XQuery.

It took the duration of a good bottle of white wine to work this little lot out; below is the list of stuff I'm going to grow this year in my garden and allotment, thats on top of the usual stuff already there - Asparagus and Rubarb.

I know it seems rather a lot, and it is for my space, but hey; I like to cram it in and try different stuff...




Organic Accent 3 kg


Organic Cosmos 3 kg


Organic Arran Victory 3 kg


Organic Belle De Fontenay 3 kg


Jermor 500 g


Kamal 250 g


Organic Setton 250 g


Mammoth Improved Onion (100 seeds) 1 pkt(s)


Market Express F1 Hybrid (500 seeds) 1 pkt(s)


Pink Brandy Wine (20 seeds) 1 pkt(s)


Pineapple (Ananas) (25 seeds) 1 pkt(s)


Organic Beefsteak (Red) (50 seeds) 1 pkt(s)


Organic San Marzano (Red) (50 seeds) 1 pkt(s)


Organic Golden Bantam (50 seeds) 1 pkt(s)


Organic Regiment F1 Hybrid (500 seeds) 1 pkt(s)


Cherry Belle (1250 seeds) 1 pkt(s)


Chivalry (20 seeds) 1 pkt(s)


Organic Hungarian Hot Wax (50 seeds) 1 pkt(s)


Organic Green (1000 seeds) 1 pkt(s)


Cubanelle (15 seeds) 1 pkt(s)


Fenix F1 Hybrid (10 seeds) 1 pkt(s)


Organic Norli (350 seeds) 1 pkt(s)


Organic Sugar Ann (400 seeds) 1 pkt(s)


Cavalier (500 seeds) 1 pkt(s)


Exhibition Long (100 seeds) 1 pkt(s)


Hollow Crown (1000 seeds) 1 pkt(s)


Organic Green Flesh Honeydew (20 seeds) 1 pkt(s)


Mira ( Cucumber Melon) Fi Hybrid (10 seeds) 1 pkt(s)


Organic Butternut Waltham (Winter Storage) (12 seeds) 1 pkt(s)


Sunburst F1 Hybrid (Summer) (10 seeds) 1 pkt(s)


Organic Montel (200 seeds) 1 pkt(s)


Ubriacona Frastagliata (Drunken Women) (400 seeds) 1 pkt(s)


Organic Pandora (250 seeds) 1 pkt(s)


Autum Mammoth Tornado (350 seeds) 1 pkt(s)


Oragnic Printanor 1 plant(s)


Organic Marketmore (30 seeds) 1 pkt(s)


Natsuhik F1 Hybrid (20 seeds) 1 pkt(s)


Organic Parthenon F1 Hybrid (8 seeds) 1 pkt(s)


Organic Medallion F1 Hybrid (50 seeds) 1 pkt(s)


Organic Amsterdam Forcing (1000 seeds) 1 pkt(s)


Flakkee 3 (1500 seeds) 1 pkt(s)


Primo F1 Hybrid (500 seeds) 1 pkt(s)


Organic Veronica (50 seeds) 1 pkt(s)


Organic Advantage F1 Hybrid (100 seeds) 1 pkt(s)


Organic Igor F1 Hybrid (50 seeds) 1 pkt(s)


Organic Red Spear (200 seeds) 1 pkt(s)


Organic Detroit Round (375 seeds) 1 pkt(s)


Organic Slenderette (200 seeds) 1 pkt(s)


Limka (50 seeds) 1 pkt(s)


Organic Streamline (100 seeds) 1 pkt(s)


Organic Witkiem (100 seeds) 1 pkt(s)


Ormskirk (500 seeds) 1


Water Cress (1000 seeds) 1


Perpetual Spinach (500 seeds) 1


Early Nantes 5 (1500 seeds) 1


Evening Fragrance (3000 seeds) 1


You can have multi-valued parameters in SQL Server Reporting Services, on selecting the mulitple options a CSV list is passed to the stored procedure or formed for the SQL.

When you choose (Select ALL), all the parameters in the list box are generated, so if you have 100 values in the list box then you get 100 on the CSV and passed to your proc.

On SP1 this behaviour isn't there, you code the Select ALL mechanism yourself and have full control.

Low and behold, we have a change in behaviour back to the original RTM (lot of good for people like me who have developed their stuff on SP1) as part of the move to Service Pack 2. This is crazy on so many fronts; anyway - I want an option in the RDL that allows THE DEVELOPER to choose if I want a Select All or not.

Vote with me here if you agree: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249227

Annoyingly Microsoft have 'postponed it for a future version' but personally I don't think that is good enough.