The Bit Bucket

SQL Server Upgrade Advisor should check Database db_compat levels, not server version

We have a very common customer scenario where the customer decides to upgrade to a new version (in this case let’s say SQL Server 2012). They run upgrade advisor and note that there are a whole lot of problems. They think “Hey that’s way too much to deal with right now. I don’t have time”.

So they don’t deal with them. They upgrade the server to 2012 but leave their db_compat level at a lower level with the intention of dealing with the problems later.

2011-12-24

SSIS: The package failed to load due to error 0xC0010014

Over the years, I’ve seen several causes of this error in SQL Server Integration Services but today I came across another one.

You can get this error if you’ve used 3rd party components (particularly data sources) and the licensing for those components has expired.

Hope that helps someone sometime.

2011-12-13

Avoiding connection timeouts on first connection to LocalDB edition of SQL Server Express

When you first make a connection to the new LocalDB edition of SQL Server Express, the system files, etc. that are required for a new version are spun up. (The system files such as the master database files, etc. end up in

C:\\Users\\<username>\\AppData\\Local\\Microsoft\\Microsoft SQL Server Local DB\\Instances\\LocalDBApp1) 

That can take a while on a slower machine, so this means that the default connection timeout of 30 seconds (in most client libraries) could be exceeded.

2011-11-30

FIX: A network error occurred during SQL Server Native Client installation

One of the things that I have been pestering the SQL team to do is to name their updates according to what is contained in them. For example, instead of just:

sqlncli.msi 

What I’d prefer is that the file was called something like:

SQLServerNativeClient2k8SP1CU3x64.msi

So I normally rename them as soon as I receive them, to avoid confusion in future. However, today I found that doing so caused me a problem. After renaming the file, and installing it, the installation failed with the error:

2011-11-26

MVP Deep Dives volume 2 is now published!

It’s great to see that volume 2 of MVP Deep Dives is now available and will be distributed at the PASS summit next week. I’m really sad that I won’t be at the book signing next week but I’d encourage you all to get along, order a copy and have it signed.

A huge thanks has to go to Kalen Delaney for her management of this project and a big thanks to my fellow editors Louis Davidson, Brad McGehee, Paul Nielsen, Paul Randal, and Kimberly Tripp for their efforts. A special mention for Paul Nielsen whose ideas and spirit around volume 1 that have continued into this new volume.

2011-10-06

Denali: Improved T-SQL Query Optimization

Part of the value in the ongoing evolution of the T-SQL language is that we are moving further and further towards being declarative rather than prescriptive ie: we are able to tell SQL Server what we want, rather than how to do it. Over time, that raises more and more possibilities for the optimizer to work with us to achieve a better outcome.

For example, note the following query against the AdventureWorksDW database:

2011-09-11

Denali: Note the small but important things in SSIS

With SQL Server, there often seems to be an over-emphasis on the items that provide bullet points for the marketing brochure. Yet, small but useful changes to the product can make a more profound impact on DBAs/developers than the new items that are highlighted by those bullet points.

One of the things I loved about Reporting Services in SQL Server 2008 R2 is the amount of focus they provided on ease of use. In particular, the addition of data bars, lookup functions, pagination control, rendering targets in expressions, domain scope, etc. made a huge difference to anyone that really needs to build reports.

2011-09-11

Do you still sharpen your knives?

We end up staying in Sydney several times per year, either for training or mentoring/consulting work. When we do, one of the hotels that we really like is the Westin. I would find it hard though, to list all the reasons why I like it. (There are some things I don’t like about it too but that’s a topic for another day).

But one of the things that has always surprised me, each and every time I eat breakfast there, is how sharp the knives are. Clearly, someone must be assigned to sharpen them, or they replace them very regularly (unlikely). Alternately, one of the staff members might have been passionate about it and it was an individual thing but we stayed about 160 nights in hotels last year and I can tell you that most hotels (big or small) don’t bother doing this.

2011-09-08