SQL Server 2008

You are currently browsing articles tagged SQL Server 2008.

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services.  This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this.  It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug.  So recently I came across a post by Phil Brammer that mentioned this issue.  This post got a comment from Matt Masson, a developer on the SSIS team.  Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file!  And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English.  Its status is Fixed but it seems that it isn’t.  At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem.  So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed.  After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team.  In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer.  As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade.  But that may a bit of an overkill.  My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:



On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!


Tags: , , , , , , , ,

I came across an issue when playing around with Report Builder 2.0.  I had created a report using an embedded data source.  Once I’d published the report to the report server, I couldn’t get it to run anymore.  Instead it gave me the following error:

This report cannot be run in report builder because it contains one or more embedded data sources with credential options that are not supported.  Instead of embedded data sources use shared data sources or save and view the report on the server.

Okay, no problem I thought, let’s just create a shared data source and switch to that one then.  So I opened up the Data Source Properties in Report Builder and selected the Use a shared connection or report model radio button.

Unfortunately, when running the report it threw me that same error?!  And when I open the Data Source properties again, my change was undone!  It was still using the embedded data source.

As far as I’m concerned that should be a bug.

The only way that I could switch my data source to a shared connection was by creating a new data source, which means you also need to move all datasets connected to the original data source.

Quick tip: if you first rename the original data source and datasets to something like srcMyDataset_OLD, you can give the correct name to the new one straightaway.

So I guess that’s another workaround on my list :-)

This issue was encountered while using Report Builder 2.0 (10.0.2531.0).  I tried to reproduce it using Report Builder 3.0 (10.50.1092.20 – that’s the version of the SQL Server 2008 R2 August CTP) and I couldn’t.  Which means it has been fixed.  Good on you Microsoft!


Tags: , , , , , ,

Now that the European Elections are over once again, I’d like to draw your attention to another request for voting.

Using Reporting Services 2008, at this moment it is not possible to link two datasets.  Linking datasets would be really interesting in certain cases.  Imagine you’ve got two datasets, used in two different report data regions.  However, your second data region needs data from the first dataset as well.  As you probably know, a data region can only be bound to one dataset.  Both datasets contain an identical identifier so in theory they could be perfectly linked, if only the IDE would allow it.  Linking two datasets should result in a third dataset that behaves exactly the same as a regular one, which would allow us to bind that one to a data region.

Here’s a small example to clarify the above:

Dataset 1 consists of col1, col2.

Dataset 2 consists of col1, col3.

col1 is an identifier.

If you could tell SSRS to join dataset 1 and dataset 2 on col1, resulting in:

Dataset 3: col1, col2, col3

that would be really great!

This has several benefits:

  • the data is already available in another dataset, why load it twice ?
  • performance: in certain cases combining the two queries into one would result in a slower query (I’m thinking of situations where the database design is not optimal and you’ve got no control over it – sounds familiar ?)
  • developing reports would become an even nicer experience (no need for workarounds such as this one)
  • if Crystal Reports and others can do it, why shouldn’t SSRS be able to do it as well ?

If you Google around (or should I say Bing around ?), you can see that I am certainly not the only one with this question in mind.  In fact, someone has already posted a request on Microsoft Connect and a Microsoft representative said the following:

Thank you for your suggestion.
We are indeed considering adding this kind of functionality in a future release of Reporting Services. We are also monitoring the customer vote count on this particular suggestion to gauge the relative community demand compared to other suggestions.
Reporting Services Team

So if you’d like to see this feature implemented in Reporting Services, click this link and vote!


Tags: , , ,

If you’re using Integration Services 2008 and the Foreach Loop Container in the Control Flow, you’ll very likely encounter this bug.

The Foreach Loop has several enumerators available.  By default it selects the Foreach File Enumerator.  However, as the screenshot below shows, there’s no way to configure it – the Enumerator configuration group just shows blank space.

Foreach Loop Editor with empty Enumerator configuration

This phenomenon is caused by a bug which has been reported on Microsoft Connect.  The workaround, if you really need the File Enumerator, is to select another enumerator first and then switch back to the File Enumerator.  You’ll notice that the regular controls show up and on you go, define that folder name!


Tags: , , , ,

Lately I had been getting annoyed by Visual Studio crashing on me while working on ETL packages.  The crashes seemed somehow related to debugging a package because they usually occurred after I clicked the “Package execution completed. Click here to switch to design mode, or select Stop Debugging from the Debug menu.” link.  But I couldn’t really pinpoint why or when exactly they occurred.  Now I’ve got an explanation, and a solution!

How did I get to the solution?  Well, today I decided to click the “Send to Microsoft” button on the infamous crash pop-up window.  And from it I actually got a link to a fix for the issue.  Apparently the issue is not related to Integration Services in particular, it is a much broader Visual Studio shell issue that occurs in Visual Studio 2008 with SP1 when you’ve got a combination of undocking windows and changing window layout.  And that is indeed what I usually do when debugging a package!  When the package stops executing I often execute the following scenario:

  • double-click the title bar on the Output window to undock it
  • enlarge the undocked Output window to almost full-screen so I can have a good look at the errors
  • double-click the title bar to dock the window back to its original place
  • click the “Package execution completed.” link (or hit the Stop Debugging button)

And that’s exactly the second scenario described in this Microsoft Support article because when you stop debugging, the IDE switches the window layout back from debug to design.

I have now installed the hotfix and the issue is gone.  Beware though if you also use WPF, better read through the whole article and comments on the download page first.

Hmm, this may also explain some other unexplicable crashes I’ve seen lately.  Makes you wonder doesn’t it :-)


Tags: , , , , , , ,

« Older entries § Newer entries »

© 2008-2018 BI: Beer Intelligence? All Rights Reserved