DavidWimbush

Strange gotcha with column names

2011-12-09T11:35:00Z

Here's a funny thing I discovered while working with a table I created by importing data from Excel. (I know, I only have myself to blame.) A number of the column names ended up with a trailing space on the end. They were Y/N type columns where the value was either Y or null. I decided to update all the nulls to Ns so the nulls wouldn't trip me up later. So I typed a query like this: update <table> set <column> = isnull(<column>, 'N'). I noticed Intellisense had underlined the column name but I knew what I was doing and assumed it was still refreshing its cache. The update ran fine but when I tried to select that column I got a 'no such column' error. WTF?

I can only assume this is a side-effect of the way you can update things like rowset functions and not just tables. I've done a bit of playing around and found that (surprise surprise) some variants will work and some will fail. Try it for yourself:

create table dbo.TestTable
(
[TestColumn ] varchar(20) null
);
go

--This update will give the result: (0 row(s) affected)
update dbo.TestTable
set TestColumn = 'hello';
go

--But this update will fail:
--Msg 207, Level 16, State 1, Line 2
--Invalid column name 'TestColumn2'.
update dbo.TestTable
set TestColumn2 = 'hello';
go

drop table dbo.TestTable;
go

I suppose the moral of the story is: concentrate and check that updates affect the number of rows you expect. (Damn, that's not going to work!)

SQL Server 2012 edition comparison details are published

2011-11-07T08:24:00Z

Interesting stuff, particularly if you're doing BI. BISM tabular and Power View will not be in Standard Edition, only in the new - presumably more expensive - Business Intelligence Edition. That kind of makes sense as you need a fairly pricey edition of SharePoint to really get all the benefits, but it's a shame there won't be some kind of limited version in Standard Edition. And Always On will be in Standard Edition but limited to 2 nodes. I really expected Always On to be Enterprise-only so this is a great decision. It allows those of us working at a more modest scale to benefit and raises the fault tolerance of SQL Server as a product to a new level.

Read all about it here: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Denali Side-by-Side Gotcha

2011-10-10T08:13:00Z

Don't try this at home. I just got my new (new to me anyway) laptop set up nicely and then installed an instance of Denali CTP3 to start exploring. RTFM? Not me! The first thing I thought I'd play with was the tabular model and Project Crescent as this has huge potential for Reporting & BI people. So I click New Project and it says you can only do this on a default instance. Erm, but my default instance is 2008R2. Oh well, back to the (un)installer.

An annoying problem with Nulls

2011-08-25T07:44:00Z

I just lost half an hour of my life which I'll never get back on this. Don't make the same mistake. I extracted some customer details for a mailing (it's OK, they ticked the box that they want to hear about our special offers) but Marketing complained that some of the address data contained nulls. I checked the spreadsheet I'd given them and they were right. So I checked my query and followed the trail back to the source record for an example. The value in AddressLine2 was Null. But my select statement said isnull(AddressLine2, '') as AddressLine2 and it was returning Null. WTF?! The sharp-eyed reader has probably already noticed that the value was the string 'Null', not the empty value NULL. I got there in the end but it took a while. Good job it wasn't upper case or I might have had to ask one of the developers for help!

If you get this problem and it's upper case you could try using the lower() function which will change the case of 'NULL' but not NULL:

declare @a varchar(10) = null;
declare @b varchar(10) = 'NULL';
select lower(@a) as a, lower(@b) as b;

Failing report subscriptions

2011-06-06T10:48:00Z

We had an interesting problem while I was on holiday. (Why doesn't this stuff ever happen when I'm there?) The sysadmin upgraded our Exchange server to Exchange 2010 and everone's subscriptions stopped. My Subscriptions showed an error message saying that the email address of one of the recipients is invalid. When you create a subscription, Reporting puts your Windows user name into the To field and most users have no permissions to edit it. By default, Reporting leaves it up to exchange to resolve that into an email address. This only works if Exchange is set up to translate aliases or 'short names' into email addresses. It turns out this leaves Exchange open to being used as a relay so it is disabled out of the box. You now have three options:

Open up Exchange. That would be bad.
Give all Reporting users the ability to edit the To field in a subscription. a) They shouldn't have to, it should just work. b) They don't really have any business subscribing anyone but themselves.
Fix the report server to add the domain. This looks like the right choice and it works for us. See below for details.

Pre-requisites:

A single email domain name.
A clear relationship between the Windows user name and the email address. eg. If the user name is joebloggs, then joebloggs@domainname needs to be the email address or an alias of it.

Warning: Saving changes to the rsreportserver.config file will restart the Report Server service which effectively takes Reporting down for around 30 seconds. Time your action accordingly.

Edit the file rsreportserver.config (most probably in the folder ..\Program Files[ (x86)]\Microsoft SQL Server\MSRS10_50[.instancename]\Reporting Services\ReportServer). There's a setting called DefaultHostName which is empty by default. Enter your email domain name without the leading '@'. Save the file. This domain name will be appended to any destination addresses that don't have a domain name of their own.

How cool was SQL Bits 8?

2011-04-20T07:07:00Z

The simple answer: very! Thanks very much to everyone who was involved in making it happen - both organisers and speakers. For a bargain price I got a deep dive day on performance monitoring and troubleshooting and two days of a wide variety of subjects. And it's not just people regurgitating Books Online. This is practical, 'street smarts' know-how from people who really do this stuff for a living. If you're not sure how to pay for it, here's one approach. Last time I went to the free day and my boss was so impressed with all the good stuff I brought back that, when I asked if he would pay for the full monty this time, he didn't even hesitate.

You get much more than just information though: there are other, less tangible benefits. It's as good as a holiday to just geek out on SQL stuff and be exposed to aspects you haven't used much. I came back full of new ideas and raring to make a difference. You also shouldn't underestimate the boost you get from just mingling with others of our kind. I don't know what it's like where you work but at my company I am the DBA team and it gets a bit lonely sometimes.

Then there's the chance to meet the rock stars and learn from them. We're lucky to work in a largely fact-based field where just having a loud voice doesn't get you very far. These people are prominent because they're good at their thing. I've now met quite a few and, without exception, they've been very approachable and helpful. This time I got to chat with Aaron Bertrand and Kevin Kline and, apart from being great SQL guys, they were both really good company.

Half the SQL Server CAT Team were over from Redmond and available all three days to talk about anything. There are sponsor companies showing you there products. And not just sales staff. At one stand I was looking into a monitoring product and it turned out the guy showing me round it was the lead developer. You can find out more in five minutes like this than you can in an hour of reading specs and documentation on their website.

Last but not least, rest assured that there is swag. A year's subscription to SQL Server Magazine, 6 months' access to an on-line library of nearly 700 technical books, serious prize draws galore, and so on. And I won second prize in the SQL trivia bingo!

I'm sure I'm missing plenty of other stuff. SQL Bits is not a trade convention. It's SQL Server people getting together, sharing what they know, and learning from each other.

Dates & times: it's worse than I thought

2011-03-26T06:34:00Z

The BBC has a rather sobering article about time zones here: http://www.bbc.co.uk/news/world-12849630. Fascinating stuff that explains how time zones work and how dates and times came to be so complicated.

Next time somebody gives you an earful about how your query gave them the wrong data because of some subtlety about the time portion of a datetime, a time zone difference, or daylight saving time, you could always point them to this. It might make you look less bad. (I'm just saying.)

Transaction log growth in the model database

2011-03-22T08:13:00Z

I had an odd one yesterday. I was sorting out internal fragmentation in our transaction logs (see Kim Tripp's blog http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx) and I noticed that on our core production server the model database transaction log was over 200MB and had 163 virtual log files (VLFs). Clearly something was logging transactions in there and causing repeated log file growth. But what? Stumped, I asked Ask SQL Server Central and found out, thanks to 'meltondba'. (I suspect that's not his real name.) The model database defaults to the full recovery model and we're backing up the system databases every hour on this server. As explained by CSS here http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx, a full backup is recorded in the transaction log. And of course if you stay in full recovery and never clear out the transaction log it will keep on growing.

What's the problem with this? Well, there are several. The model database is the template from which new databases are created. I created a new database on this server (create database [dw]) and it had a 3MB data file and a 200MB log file with 3 VLFs. The disk space for the log file and backups, and the time required for backups. OK it's not major but it all adds up. If enough 'small stuff' like this accumulates it can really dent the performance of your system.

What should you do? I'd suggest changing the model database to the simple recovery model. You don't want new databases to be in full recovery unless you're going to actively manage the logs, and you don't want to bother with that unless you need point in time recovery. Backup the log, shrink it and set it to a small size, say 3MB. Kim Tripp's blog post shows all the code for this. When you create a new database you'll want to set the file sizes and growth characteristics anyway.

Denali in shock code name change

2011-03-09T14:01:00Z

Did I miss an announcement? I've had an email about a conference where one of the sessions is about a version of SQL Server I'm not familiar with:

I checked the conference site and it says Denali there so this isn't a play on words.

Sometimes the spellchecker just isn't enough!

Microsoft Business Intelligence Seminar 2011

2011-03-03T16:35:00Z

I was lucky enough to attend the maiden presentation of this at Microsoft Reading yesterday. It was pretty gripping stuff not only because of what was said but also because of what could only be hinted at. Here's what I took away from the day. (Disclaimer: I'm not a BI guru, just a reasonably experienced BI developer, so I may have misunderstood or misinterpreted a few things. Particularly when so much of the talk was about the vision and subtle hints of what is coming. Please comment if you think I've got anything wrong. I'm also not going to even try to cover Master Data Services as I struggled to imagine how you would actually use it.)

I was a bit worried when I learned that the whole day was going to be presented by one guy but Rafal Lukawiecki is a very engaging speaker. He's going to be presenting this about 20 times around the world over the coming months. If you get a chance to hear him speak, I say go for it. No doubt some of the hints will become clearer as Denali gets closer to RTM.

Firstly, things are definitely happening in the SQL Server Reporting and BI world. Traditionally IT would build a data warehouse, then cubes on top of that, and then publish them in a structured and controlled way. But, just as with many IT projects in general, by the time it's finished the business has moved on and the system no longer meets their requirements. This not sustainable and something more agile is needed but there has to be some control. Apparently we're going to be hearing the catchphrase 'Balancing agility with control' a lot.

More users want more access to more data. Can they define what they want? Of course not, but they'll recognise it when they see it. It's estimated that only 28% of potential BI users have meaningful access to the data they need, so there is a real pent-up demand. The answer looks like: give them some self-service tools so they can experiment and see what works, and then IT can help to support the results. It's estimated that 32% of Excel users are comfortable with its analysis tools such as pivot tables. It's the power user's preferred tool. Why fight it? That's why PowerPivot is an Excel add-in and that's why they released a Data Mining add-in for it as well.

It does appear that the strategy is going to be to use Reporting Services (in SharePoint mode), PowerPivot, and possibly something new (smiles and hints but no details) to create reports and explore data. Everything will be published and managed in SharePoint which gives users the ability to mash-up, share and socialise what they've found out. SharePoint also gives IT tools to understand what people are looking at and where to concentrate effort. If PowerPivot report X becomes widely used, it's time to check that it shows what they think it does and perhaps get it a bit more under central control. There was more SharePoint detail that went slightly over my head regarding where Excel Services and Excel Web Application fit in, the differences between them, and the suggestion that it is likely they will one day become one (but not in the immediate future).

That basic pattern is set to be expanded upon by further exploiting Vertipaq (the columnar indexing engine that enables PowerPivot to store and process a lot of data fast and in a small memory footprint) to provide scalability 'from the desktop to the data centre', and some yet to be detailed advances in 'frictionless deployment' (part of which is about making the difference between local and the cloud pretty much irrelevant).

Excel looks like becoming Microsoft's primary BI client. It already has:

the ability to consume cubes
strong visualisation tools
slicers (which are part of Excel not PowerPivot)
a data mining add-in
PowerPivot

A major hurdle for self-service BI is presenting the data in a consumable format. You can't just give users PowerPivot and a server with a copy of the OLTP database(s). Building cubes is labour intensive and doesn't always give the user what they need. This is where the BI Semantic Model (BISM) comes in. I gather it's a layer of metadata you define that can combine multiple data sources (and types of data source) into a clear 'interface' that users can work with. It comes with a new query language called DAX. SSAS cubes are unlikely to go away overnight because, with their pre-calculated results, they are still the most efficient way to work with really big data sets.

A few other random titbits that came up:

Reporting Services is going to get some good new stuff in Denali.
Keep an eye on www.projectbotticelli.com for the slides. You can also view last year's seminar sessions which covered a lot of the same ground as far as the overall strategy is concerned. They plan to add more material as Denali's features are publicly exposed.
Check out the PASS keynote address for a showing of Yahoo's SQL BI servers. Apparently they wheeled the rack out on stage still plugged in and running!
Check out the Excel 2010 Data Mining Add-Ins. 32 bit only at present but 64 bit is on the way.
There are lots of data sets, many of them free, at the Windows Azure Marketplace Data Market (where you can also get ESRI shape files).
If you haven't already seen it, have a look at the Silverlight Pivot Viewer (http://weblogs.asp.net/scottgu/archive/2010/06/29/silverlight-pivotviewer-now-available.aspx).
The Bing Maps Data Connector is worth a look if you're into spatial stuff (http://www.bing.com/community/site_blogs/b/maps/archive/2010/07/13/data-connector-sql-server-2008-spatial-amp-bing-maps.aspx).