SQL Server 2008

You are currently browsing articles tagged SQL Server 2008.

SQL Server 2008 SP1 CU7

April 14, 2010 in SQL Server | No comments

I usually don’t post about a Cumulative Update being released but this time I went over the list of fixes and noted some as being interesting to keep in mind. So this post is more a "reminder to self" note than anything else.

Since a couple of weeks, CU7 for SQL Server 2008 SP1 is available for request on this Microsoft Support page.

Here’s the list of fixes that caught my attention:

978839 (http://support.microsoft.com/kb/978839/ )
FIX: A backup operation on a SQL Server 2008 database fails if you enable change tracking on this database

979777 (http://support.microsoft.com/kb/979777/ )
FIX: You experience some problems when you perform a grouping members operation on an Excel pivot table whose data source is an SSAS 2005 cube

976412 (http://support.microsoft.com/kb/976412/ )
FIX: An MDX query resets to a blank query when you click the Data tab in Business Intelligence Development Studio 2005 Report Designer

978930 (http://support.microsoft.com/kb/978930/ )
A parameter value is replaced by its default value when the parameter is hidden and in a snapshot report in SQL Server 2008 Reporting Services

979379 (http://support.microsoft.com/kb/979379/ )
FIX: Charts that are in a tablix of an SSRS 2008 report display incorrect data

979496 (http://support.microsoft.com/kb/979496/ )
FIX: Log entries are missing for the OnPreExecute event and the OnPostExecute event in SQL Server 2008 Integration Services

980925 (http://support.microsoft.com/kb/980925/ )
The result of a MDX query to query a calculated member on a attribute hierarchy lose the formatting you define on the calculated member in SQL Server 2008 Analysis Services

980949 (http://support.microsoft.com/kb/980949/ )
FIX: Header rows of a tablix do not always appear at the top of pages in an SSRS 2008 report

That’s it, have fun!

Tags: bugfix, Cumulative Update, SQL Server 2008

SSMS: Color The Status Bar

March 23, 2010 in Management Studio | No comments

One is never too old to learn, right? Here’s a Management Studio feature which has been introduced in SQL Server 2008. And I’ve discovered it about a month ago. Since then, I use it every day!

What am I talking about? Well, the Management Studio allows you to link a color to a connection. Each time when you open a window, the color of the status bar will change to the one linked to the connection to which your window is connected. Still with me? Alright, I’ll get the drawing board out.

To link a color to a connection, open up the Connect dropdown in the Object Explorer and choose Database Engine.

Object Explorer: Connect to Database Engine

That opens up the following familiar screen:

Connect to Server window

Do you see the Options button indicated with the red arrow? Click it to open up additional options that you can set on your connection.

Connection Properties: Use custom color

To link a color to the connection specified in the Login page, activate the Use custom color checkbox and select a color.

Now click the Connect button and open up a new query window. My favorite way of doing that is to open up the Databases tree node in the Object Explorer, select the database in which I’m interested, and hit CTRL+N.

With the new window open, did you notice the status bar?

Here you can see the status bars from two different query windows connected to two different instances on the same machine.

The way I use these colors is as follows:

Green for the servers in the Development environment
Orange for the servers in the Acceptance environment
Red for the servers in the Production environment

This gives you an extra safeguard to ensure that you’re executing that TRUNCATE TABLE statement on the right server.

However, as usual there are some things to take into consideration.

Things To Keep In Mind

localhost is not the same as <YOUR_MACHINE_NAME>

Be careful when you’re connecting to SQL Server instances on the local machine. As the title above indicates: “localhost” and “BigBlue” are not the same (assuming that your PC is called BigBlue). If you want to avoid trouble, set up the same color for both connections from the beginning.

Registered Servers

If you’ve got a habit of using the Registered Servers window, it’s important to know that the color specified here is completely separated from the color specified on the same connection through the Connect to Server window.

In fact, I believe all connection settings are set up separately when using this tool.

Change Connection On Open Window

Be careful when you use the Change Connection button on an open window: it messes up the coloring. More precisely, it will keep the color of the previous connection.

There’s a bug filed on Microsoft Connect related to this, current status is Won’t Fix. Which seems a bit weird: I noticed different behavior on SQL Server 2008 R2. When changing my connection from SQL Server 2008 to R2, it would update the color to the one linked to R2. In the other direction, disconnecting from R2 and connecting to SQL 2008, it would not change the color.

Update: I’ve found a couple additional bug reports on Connect related to this feature. If you’d like to see some consistent coloring behavior (and avoid the risk of executing a TRUNCATE TABLE on the wrong server), please take a moment to vote Yes at the following pages.

Connect: Update status bar colour when changing connections

Connect: [SSMS] Make color coding of query windows work all the time

Conclusion

Currently, to get consistent coloring all the time, you need to set up the colour three times for each connection: once in the Connect to Server window, secondly in the Connect to Database Engine window (this is the window that you get when clicking the Change Connection button), and thirdly in the Registered Servers pane (if you’re using this pane).

That’s it for now, have fun coloring those status bars!

Valentino.

Tags: data, Management Studio, SQL Server 2008, SSMS

Drawing In SQL Server 2008

February 26, 2010 in SQL Server | No comments

A while ago I had been playing a bit with the new spatial data types in SQL Server 2008. Not only can SQL Server store such data, it can also visualize it. So I had come up with the following query:

select geometry::STPolyFromText
('POLYGON((1 1, 1.5 1.85, 2.5 1.85, 3 1, 2.5 0.15, 1.5 0.15, 1 1),
(1.5 1.5, 1.5 0.5, 1.7 0.5, 1.7 0.9, 2.3 0.9, 2.3 0.5, 2.5 0.5, 2.5 1.5, 2.3 1.5,
2.3 1.1, 1.7 1.1, 1.7 1.5, 1.5 1.5))', 0);

Running that in the Management Studio produces something like this on the Spatial results tab:

Hoegaerden - the polygon

I’m using the STPolyFromText function to produce a six-sided polygon with a letter H in the center. To get to the result, I’m passing a bunch of XY coordinates to the function. A line gets drawn between two coordinates following each other.

The first list of coordinates (the first list of numbers enclosed in round brackets) creates the hexagon while the second list produces the letter H. Each list of coordinates thus represents one polygon. The starting XY coordinate must be equal to the ending coordinate to form a closed shape. The shape is then filled with a color. Overlapping shapes will be filled using different colors, as shown above.

Producing these coordinates was quite interesting: to be able to calculate the angled sides of the hexagon I used the following formulas.

With S being the length of one side:

Width = 2 * S

Height = S * SQRT(3)

Note: these formulas are only applicable when the hexagon is rotated as shown above.

I needed to know these distances to be able to calculate how much I should increase the X/Y coordinates to get to the next connector.

Okay, enough theory, back to the story now.

Q: “So, why are you creating a helipad platform?”

A: No, it’s not supposed to look like a helipad platform. The shape resembles the beer coasters for the beer called Hoegaarden. And those coasters always have a funny drawing or comment on them. Let’s see if I can find one that’s understandable for non-native Dutch-speakers…

(And half an hour later – turns out it’s really not easy to find one without text – luckily they’ve also created some in English!)

Hoegaarden beer coaster

Initially I was planning on writing a longer article about the spatial data types but hadn’t found the time to do it yet. And then a couple of days ago I came across a challenge that made me think of this query and so now I’m publishing it in this post.

The challenge to which I’m referring was started by Itzik Ben-Gan and is called Geekiest Sig Challenge. The point is to use the new SQL Server spatial data types to create a signature for yourself. Well, a perfect description for my query!

I was happy to just get an H on there, getting those coordinates right without first putting it on paper is really a challenge And on top of that, I was never any good at drawing. I remember in secondary school we got an assignment to draw a tree. Any tree. So at home I looked out the window and started drawing the tree. I ended up with the most atrocious thing I’d ever seen (well, that may be a bit exaggerated), it could have starred in a Hitchcock movie straight away, really spiky, and probably spooky when encountered in the dark and with the right background noises playing.

Anyway, what I wanted to say, some people are just talented: check out this submission by Michael J. Swart! No further words needed.

Right, enough geeky stuff to close the week, and remember: have fun!

(Hmm, now I’ve got something to build my favicon from…)

Valentino.

Tags: Fun, Hoegaarden, spatial data, SQL Server 2008

SQL Server 2008 SP2: Want It? Vote For It!

January 20, 2010 in SQL Server | No comments

If you’re interested in seeing the next Service Pack for SQL Server 2008 released, vote for it at this Microsoft Connect page! Now that the release date for R2 has been announced, hopefully the next one is for SQL 2008 Service Pack 2.

If you’re still on 2005 and thus more interested in an SP4 for that version, it has also been posted at Connect.

How did I find out? Through the following blog posts at SSQA.NET:

SQL Server 2008 SP2

SQL Server 2005 SP4

Update (27 Feb 2010):

SQL Server 2008 SP2 is scheduled for Q3 2010.

SQL Server 2005 SP4 is scheduled for Q4 2010.

See this post on the Microsoft SQL Server Release Services blog for details.

Tags: SQL Server, SQL Server 2005, SQL Server 2008

SSMS Showing Incorrect Version Numbers In Object Explorer

January 10, 2010 in Management Studio | No comments

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:

SELECT @@VERSION;

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: bug, data, Integration Services 2008, Management Studio, Reporting Services 2008, SQL Server 2008, SSIS, SSMS, SSRS

Report Builder 2.0: Cannot switch data source

September 12, 2009 in Reporting Services | 1 comment

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: bug, data, Report Builder 2.0, Report Builder 3.0, Reporting Services 2008, SQL Server 2008, SSRS

Want to link datasets? Please vote!

June 13, 2009 in Reporting Services | 3 comments

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.
Thanks,
Reporting Services Team

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

Tags: data, Reporting Services 2008, SQL Server 2008, SSRS

BUG: Foreach Loop Container

June 9, 2009 in Integration Services | 1 comment

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.

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: bug, data, Integration Services, SQL Server 2008, SSIS

FIX: Visual Studio crashes after playing around with its windows

May 14, 2009 in Integration Services, SQL Server, Visual Studio | 1 comment

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: BIDS, bugfix, Business Intelligence Development Studio, data, SQL Server, SQL Server 2008, SSIS, Visual Studio 2008

10.0.2531.0

April 12, 2009 in SQL Server | No comments

Right. 10.0.2531.0. That’s the version number that my SELECT @@VERSION statement is reporting when executed on my SQL Server 2008. How come? Well, I installed Service Pack 1.

In other words, SP1 for SQL Server 2008 is out and can be downloaded from the Microsoft Download Center!

Besides fixing several bugs there are some new features to the installation package itself:

Slipstream
You can now integrate the base installation together with service packs or with hotfixes. Therefore, you can install in a single step.
Service pack removal
You can now uninstall only the service pack without uninstalling the whole instance.
ClickOnce capability
You can now deploy Report Builder 2.0 by using the ClickOnce deployment technology.

I especially like the slipstream feature. This should wipe out a problem which I encountered with 2005. Some time ago I got a machine that had SQL Server 2005 installed, including SP2. However, the Management Studio was not installed. Ever tried getting it installed in such a scenario? The install DVD complains that there’s already a newer version on the machine and refuses to install SSMS. So I ended up uninstalling and reinstalling everything. This problem should now be avoidable, or at least, that’s what I hope. I haven’t actually tried it out but that’s how I understand slipstream.

Together with SQL Server, the Express Edition of 2008 has also gotten an update to SP1. And the same goes for the SQL Server 2008 Feature Pack which is now dated April 2009.

Other updated downloads include Report Builder 2.0, Data Mining add-ins for Office, Reporting Services add-in for SharePoint and the Upgrade Advisor.

Update: the BOL have gotten their update as well. Now available for download: Microsoft SQL Server 2008 Books Online (May 2009).

Tags: data, download, SQL Server, SQL Server 2008

« Older entries