Saving Changes is Not Permitted?

In order to test drive the data modeling features of SS2008, I decided to use the engine for a real-world application.  I’m now attempting to use SQL Server to manage the Bateman household.  My first table in the design is a simple list of chores that we need to make sure get done around the house.

As I was putting together some of the details about how I would use this data, I decided to add a new column in order to specify the frequency (in days) which each chore should ideally be performed.  I was quite surprised to get the following error message when tying to add a nullable column to my existing table.

image

Saving changes is not permitted.  The changes you have made require the following tables 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. 

Are you kidding me?  It wouldn’t let me add an “Allow Nulls” column?  That just seemed absurd so I did a bit more digging.

Apparently, this is now the default behavior for any of the following changes to a table:

  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column

In order to prevent this default behavior, you simply need to uncheck a box in the table designer options using the Tools -> Options menu item

image

Expand the Designers section to display the Table and Database Designers options.

image

To change this behavior, just uncheck the “Prevent saving changes that require table re-creation” checkbox.

Although this is a bit frustrating, at least it is easy to fix and there is plenty of advice out there.  Here are some other people that found and posted this useful tidbit before me:

http://mirceacimpoi.spaces.live.com/blog/cns!98EC8584C8BBED54!849.trak

http://mtgcsharpguy.blogspot.com/2008/06/ms-sql-server-2008-new-features_13.html

http://www.danrigsby.com/blog/index.php/2008/09/26/sql-server-2008-error-saving-changes-is-not-permitted/

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

http://geekswithblogs.net/frankw/archive/2008/05/29/saving-table-changes-is-safer-in-sql-server-2008.aspx

Advertisements

Cool New Features in SSMS – Recent Expensive Queries

Depending on whether you are more of a developer or more of a DBA you may care more or less about this next feature.  However, anyone can benefit from it.  SSMS 2008 makes it very easy to crack open the Activity Monitor as there is a noticeable button right on the toolbar.

image

Once inside you get a few charts, as well as some tables of performance related data below.  This is clearly not as exciting on my local Vista machine as it would be on a pounded production database, but you can see where the power of a tool like this could come in handy.

Each of the charts represents these metrics:

  • % Processor Time
  • Waiting Tasks
  • Database I/O
  • Batch Requests/Sec

Probably the most interesting feature to me is the Recent Expensive Queries table.  This allows you to dynamically analyze the most impacting queries against your server.  Since the impact can be determined by a number of factors, it allows you to sort by:

  • Executions
  • CPU
  • Physical Reads
  • Logical Writes
  • Logical Reads
  • Average Duration
  • Plan Count

Even cooler, you can drill into the actual query itself with a right-click option:

image

Cool New Features in SSMS – Select Top 100 Rows

As I have been playing with the newest version of Microsoft’s SQL Server Managements Studio (SSMS), I wanted to share a few of the cool new features.  It seemed that the jump from Query Analyzer in SQL Server 2000 to SSMS in 2005 was not necessarily a smooth one and many people complained about SSMS.  This new version, however, appears to build on the solid framework of 2005 and add lots of cool features that will make our lives much easier.

Select Top 1000 Rows

Just grabbing a sample of data out of a table has been made much easier in 2008:

image

It is also good to see that this has been made configurable in the Tools->Options menu item:

image

You’ll notice that there is also an “Edit Top n Rows” option.  This is arguably less useful as I haven’t found anywhere to specify which 200 rows, but still a noble effort.  I don’t often find myself thinking, “Hmmmm, I think I would like to edit some random data.  Let me just pull up any old record and start changing the data.”  However, once in edit mode, you can always use the “SQL” button (in green box below) to change your query:

image

Missing Web Site Templates in Visual Web Developer 2008 Express Edition

Continuing in my quest to experience the goodness of the new .NET 3.5 SP1 stuff from the ground up, I installed Visual Web Developer 2008 Express Edition.  After all, now that I have my AdventureWorks database up and running, I want to experiment with ASP.NET data scaffolding (ASP.NET Dynamic Data), JavaScript Intellisense, ADO.NET Entity Framework, ADO.NET Data Services, and many of the other great new features in this release.  As Scott Hanselman and Carl Franklin recently discussed on Hanselminutes, this framework update really should have been .NET 3.6 there is so much new stuff in there!

 

The Problem

So I was sorely disappointed to start my first web site and was prompted with this confusing dialog box after doing a File -> New -> Web Site:

image

Selecting a different version of the framework didn’t fix anything, I had no web site templates!  How was I going to follow all of these simple step-by-step tutorials if I couldn’t get past step 1?  From the scores of other people having the same issue, I could tell I was not alone in my frustration.

Allow me to back up a bit and describe how I got into this predicament — it didn’t take long…

image

 

After installing, I did the natural thing and clicked on the item in my start menu labeled “Microsoft Visual Studio 2008” as it seemed to be the only logical choice.  Verifying the properties shows that this shortcut is pointing to C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\Devenv.exe, which is exactly what I would have expected from experience with other versions of Visual Studio (insert snide remark about seemingly old version number in the “Common7” part of the path).

 

The Solution

No telling if this will be the same problem that everyone else seemed to be having, but it appears I just opened the wrong .exe!?!  I can’t really blame myself though, since the installation process seems to be a bit misleading.  There is no shortcut anywhere to the correct application which is actually called VWDExpress.exe:

image

Launching this executable manually gives me the much anticipated web site templates, and what I need to move on .  One subtle note is that my menu path to get to this is actually File -> New Web Site.  This path is one level shallower than the full blown studio version, but they both share the same keyboard shortcut of Shift + Alt + Nimage

So, if you read the whole post above and you’re still not quite sure what I am getting at, just look at the log in your development environment:

No web site templates (bad):

image

Web site templates (good):

image

The Details

To clarify whether or not this should apply to you, I am running all free versions of the tools provided my Microsoft.  If you have a full-blown version of Visual Studio, you may not experience this problem when using the standard IDE.  I first installed SQL Server 2008 Express, then Visual Web Developer 2008 Express.

This means that I now have the following versions of these files:

devenv.exe 9.0.30729.1
VWDExpress.exe 9.0.30729.1

 

Stay tuned for more blog posts as I continue on this journey…

https://codeslammer.wordpress.com/rss

SQL Server 2008 Sample Databases

If you remember good ol’ NorthWind, you’ll know that having a sample SQL Server database can be great for sampling new technologies and performing RDBMS experiments and tests.  As much as detail-oriented presenters and trainers like to use NorthWind, it is an old sample from SQL Server 2000 that doesn’t have many of the new features in SQL Server 2008.

I set out to get the sample databases, and I was amazed at the complexity of it.  Not that it is in general a difficult task, but it could probably be made much more simple by Microsoft.  Since I figured out which hoops you need to jump through,  I thought I would summarize it for you here:

1) Read Up

If you want to have a successful installation of the sample databases, take 20-30 minutes to become familiar with the SQL Server Database Product Samples.  Contrary to some of the complaints people have posted in the comments, there is lots of good documentation that helps you understand the contents and process.image

Two important points you might miss are that you need to have full-text search installed and FILESTREAM enabled to ensure a problem free install of the samples.  This can be done after the fact if you did not specify these settings during the initial installation.

It should also be noted that attempting to change the FILESTREAM setting through the SQL Server Configuration Manager did not seem to work even after a restart of the engine.  Instead I had to run this SQL

exec sp_configure ‘filestream_access_level’, ‘2’
RECONFIGURE

 

2) Download & Run the Installer

Once you’ve familiarized yourself with the process and read the pre-requisites, download the .msi installer file:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040

For my Vista machine running SQL Server 2008, I picked this file: SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msi

 

imageOne interesting point is that there appears to be an option to have the installer restore the database for you.  Unfortunately, when I attempted to use this option to restore it in my local SQLExpress instance, it bombed out on me requiring me to run the installer again without the option selected.  The option is not selected by default which leads me to believe that Microsoft is aware this is a potential problem area.

3) Restore the DB Manually

This is the part where people with little or no DBA knowledge probably begin to curse.  If you are running the same platform as me and accept all of the defaults, you would think this should be as simple as running this script in a new query window (based on step 6 in the instructions):

RESTORE DATABASE AdventureWorks2008
FROM DISK ‘C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks2008.bak’

Unfortunately, this gave me a bunch of errors based on the way the RESTORE was attempting to use the default settings.

image

You can get around this by using the restore script (RestoreAdventureWorks2008.sql) in the same folder as the .bak file.  If you look at how the path is being used, you’ll notice that you have to substitute the path as follows in the top section of the script:

SET @source_path = ‘C:\Program Files\Microsoft SQL Server\100\’ 

 

Summary

So after a bit of plugging away, I was able to the the sample successfully restored.  Yea!

image

I truly empathize with Microsoft as streamlining this installation can be a daunting task.  There are an endless number of settings that have to be accounted for, security, versions, etc.  However, this installation process is much too difficult for the average developer in my view.

Now that I have some data to play around with, I expect to post some experiences about using the new 2008 features such as intellisense and the other goodies I expect to find in the newest version of this tool.  All things considered, SQL Server is still my database platform of preference and I appreciate everything Microsoft does to make this free version available to the public.