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.

Note:
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
Registration
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
Break

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...

Code

Plant

3OE06

Organic Accent 3 kg

3OS14

Organic Cosmos 3 kg

3OM01

Organic Arran Victory 3 kg

3OE17

Organic Belle De Fontenay 3 kg

SHA09

Jermor 500 g

OSE24

Kamal 250 g

OSE98

Organic Setton 250 g

ONI28

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

TUR07

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

TOM17

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

TOM16

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

TOM66

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

TOM67

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

SWC90

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

SPI90

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

RAD01

Cherry Belle (1250 seeds) 1 pkt(s)

PEP22

Chivalry (20 seeds) 1 pkt(s)

PEP85

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

PUR98

Organic Green (1000 seeds) 1 pkt(s)

PEP25

Cubanelle (15 seeds) 1 pkt(s)

PEP20

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

PEA95

Organic Norli (350 seeds) 1 pkt(s)

PEA92

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

PEA21

Cavalier (500 seeds) 1 pkt(s)

PRN10

Exhibition Long (100 seeds) 1 pkt(s)

PRN02

Hollow Crown (1000 seeds) 1 pkt(s)

MEL94

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

MEL04

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

WSQ88

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

SSQ02

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

LET96

Organic Montel (200 seeds) 1 pkt(s)

LET50

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

LEE90

Organic Pandora (250 seeds) 1 pkt(s)

LEE17

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

GAR94

Oragnic Printanor 1 plant(s)

CUC93

Organic Marketmore (30 seeds) 1 pkt(s)

CUC14

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

COU94

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

CAU91

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

CAR80

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

CAR09

Flakkee 3 (1500 seeds) 1 pkt(s)

CAR15

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

CAL93

Organic Veronica (50 seeds) 1 pkt(s)

CAB90

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

BRU97

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

BRO97

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

BEE99

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

DFB94

Organic Slenderette (200 seeds) 1 pkt(s)

CFB93

Limka (50 seeds) 1 pkt(s)

RBE97

Organic Streamline (100 seeds) 1 pkt(s)

BBE99

Organic Witkiem (100 seeds) 1 pkt(s)

SAV04

Ormskirk (500 seeds) 1

CRE02

Water Cress (1000 seeds) 1

LBE01

Perpetual Spinach (500 seeds) 1

CAR03

Early Nantes 5 (1500 seeds) 1

NC01

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.

Tony.