Personal Tech Blog documenting problems and solutions, mainly for C# programming in .NET and Blogger Template Customizations.

SQL : How to solve Northwind Database Install Error in SQL Server 2012

Recently I was going through a database programming tutorial that used Northwind Database in code examples, so I thought “No problem, I’ll quickly add that database to my SQL Server 2012″. I assumed this would be easy but to my surprise that wasn’t the case.

In this post I will show you what problems I encountered while trying to install Northwind database and how I finally managed to make it work.

Note:Solution below was tested with the following configuration:

  • Windows 7 Professional 64 bit
  • SQL Server 2012 Express Edition

Step 1 – Getting Northwind Database and examining its content

First we need to download the Database from Microsoft web page. Save the file in your computer and run the installer (SQL2000SampleDb.msi). Installation Wizard will appear. Go through all the steps and in the end it should install the files in the following folder:

c:\SQL Server 2000 Sample Databases

The folder will contain files for two databases:

  1. Pubs sample database
    (contains information about made up book publishing company)
  2. Northwind sample database
    (contains sales data from made up trading company)

Each sample database will have 3 files each with different extensions:

  • .sql
    This one contains script for generating the database. After database is created, the data is stored in MDF and LDF files.
  • .MDF
    This is a primary data file where schema and data of the database is stored.
  • .LDF
    This is a log file where a transaction log information is stored. It keeps track of everything done to the database and can be used to rollback or restore the database.
Note: With SQL Server 2005, Northwind database got replaced with  AdventureWorks database. If you would like to give AdventureWorks a try, you can download it from CodePlex.

Step 2 – Installing it on a SQL Server

As already mentioned in previous step, MDF file is where data of the database is located and this file can be used to attach the database to the server, so this is the first thing I tried.

Error while attaching Northwind Database in SQL Server Management Studio
Error while attaching Northwind Database using SSMS

Failed Attempt #1 – attaching database using SQL Server Management Studio 2012 (SSMS)

This tool is available when you install SQL Server 2012. Using Windows 7 you can easily run it by typing sql management in the Start Menu search field.

Tip:If you are using SQL Server 2008, you might not have this tool. You can obtain it from Microsoft Download Center.

When I tried to attach NORTHWND.MDF file, Error Window appeared with following error:

An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
Error while attaching Northwind Database using Visual Studio Express
Error while attaching Northwind Database using VS Express

Failed Attempt #2 – attaching database using Visual Studio Express

Next I tried to attach it using VS Express. This time I received this warning:

The database file that you are attempting to connect to is not compatible with the current instance of SQL Server. To continue, you must upgrade this database file….Do you want to upgrade the database file now?

I clicked Yes hoping the upgrade will work, but I received another error instead described next.

Errors received with attempt #1 and #2

In both cases, I got the following error when attempting to attach the NORTHWND.MDF file:

Unable to open the physical file C:\SQL Server 2000 Sample Databases\NORTHWND.MDF … Operating system error 5 (Access is denied), Microsoft SQL Server Error 5120.

I tried to solve this problem by running those applications as an administrator (right-clicking on the application and selecting Run as administrator) but it didn’t do much except giving me another error:

Database C:\SQL Server 2000 Sample Databases\NORTHWND.MDF cannot be upgraded because its non-release version (539) is not supported by this version of SQL server…CREATE DATABASE is aborted.

So instead of attaching the database I decided to try to execute instnwnd.sql file. This attempt was successful and is described in the next section.

What finally worked – executing SQL script in instnwnd.sql file

To execute script I used SQL Server Management Studio 2012 (SSMS) tool that got installed with SQL Server Express. Original script also gave me errors but after a little search on the web I found few different suggestions what to modify in the script, one of which worked.

To successfully execute instnwnd.sql script, run the SQL Server Management Studio and try these steps:

  • When the SSMS is loaded, Connect to Server window will appear. Click on Connect button. If you are unable to connect to the SQL Server, maybe you are not using Windows Authentication. If that’s the case, select SQL Server Authentication  for Authentication and provide the necessary username and password. After successful connection, your SQL Server instance should be listed in the Object Explorer.
  • Now under File Menu select Open > File.
  • Find the instnwnd.sql in your computer and select it. Click Open
  • SQL script should open in the main window.
  • Do not click on Execute yet. If you do, you will get the following error:
    Could not find stored procedure ‘sp_dboption’.
  • Around the line 20, remove the following two lines:

  • Replace them with this line as shown below:

    SSMS - Fixing NorthWind script file instnwnd.sql for SQL Server 2012

  • All that is left is to execute the script, so click on Execute.

After following above steps you should now have Northwind database on your SQL Server.

Conclusion

With Northwind database you cannot simply attach it or execute its original .sql script on newer versions of SQL servers like 2012 or even 2008. This article demonstrated how to successfully execute instnwnd.sql file with just a small modification in the script.

I hope you have found this post useful. Feel free to drop a comment or share this article on a social networks.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge

Back to Top