SQL Server 2008

You are currently browsing articles tagged SQL Server 2008.

Here’s another silly one but it catches me every time.  So I decided to write a quick post about it, to never forget about it again!

Ever seen this warning message while using the Management Studio?

Warning: Saving changes is not permitted

All I did was open the AdventureWorks.Production.Product table in the designer, moved the ListPrice column above the StandardCost column and hit CTRL+S to save changes.  Instead of changing the table, it tells me:

Saving changes is not permitted.  The changes you have made require the following tabled to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

What a message huh?!  It needs to re-create the table, so what, go ahead!  Hang on, even though this is just a Warning, there’s no Continue button!  Ow come on, are you kidding?

Like any good developer that wants to get some work done, you stop reading such a long message after the second line and try to find a solution for your problem.  Too bad, you should have just continued reading the warning message because the solution is actually hidden in there!

If you open up the Options screen from the Tools menu in SSMS, and then drill down to the Designers > Table and Database Designers node, you’ll see an option called Prevent saving changes that require table re-creation.  And by default, that option is checked.  Remove the check from that box and you’ll be able to use the table designer as it was intended.

The "Prevent saving changes that require table re-creation" setting

So, here’s your ADSOTD (Annoying Default Setting Of The Day)!

Small note: be careful when you want to use this feature on a large table.  The script that gets generated in the background to make the changes needs to copy all records from the old table into the new one and that will obviously require some time!

Have fun!

Valentino.

Share

Tags: , ,

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!

Share

Tags: , ,

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.

Purple status bar connected to SQL Server 2008 R2

Green status bar connected to SQL Server 2008

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

Change Connection buttonBe 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.

Share

Tags: , , ,

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.

Share

Tags: , , ,

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.

Share

Tags: , ,

« Older entries

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