SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2

I have mentioned the history of NorthWind, Pubs and AdventureWorks in my previous post SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases. I have been receiving very frequent request for NorthWind Database for SQL Server 2005 and installation method.

Follow the steps carefully and it will install Northwind and Pubs database in SQL Server without any issue. I have tested all the three method on my home computer and attached the screen shots as well.

Common Step for all the 3 methods

First, go to Microsoft Download Center and download SQL2000SampleDb.msi.
Run SQL2000SampleDb.msi it will create “C:\SQL Server 2000 Sample Databases” folder.
Now follow any of the following three method to install this databases. I have listed methods in order of my preference.

Method 1: Running SQLCMD
Navigate to this folder using command prompt. Start >> Run >> C:\SQL Server 2000 Sample Databases.
If you have installed SQL Server as named instance run following command.

c:\>sqlcmd -S .\InstanceName -i instpubs.sql
c:\>sqlcmd -S .\InstanceName -i instnwnd.sql

If you have installed SQL Server as default instance run following command.

c:\>sqlcmd -i instpubs.sql
c:\>sqlcmd -i instnwnd.sql

Verify that NorthWind and Pubs database are installed correctly.
Method 2: Running SQL Script
Open SQL Server Management Studio. Go to File >> Open >> Navigate to “C:\SQL Server 2000 Sample Databases\instpubs.sql” and “C:\SQL Server 2000 Sample Databases\instnwnd.sql” and run those sql file in order. This will install the database.

Verify that NorthWind and Pubs database are installed correctly.

Method3: Attaching the Database
Move Northwind and Pubs database files (MDF and LDF files) to your default database file location (usually – C:\Program Files\Microsoft SQL Server\MSSQL\Data).
Open SQL Server Management Studio. Expand the database node >> Right click on Databases >> Click on Attach >> Select NorthWind Database. It will automatically find MDF and LDF file. Click OK.

Repeat the process for Pubs and verify that NorthWind and Pubs database are installed correctly.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , Read-Me file in installation folder, BOL

About these ads

192 thoughts on “SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2

  1. Jo Buddy,

    This is good article, just wondering if you can elaborate why there is not change when you copy mdf, ldf file of MS 2000 in MS 2005 there is not effect.

  2. Dear Pinal Dave,
    I have purchased “The Guru’s Guide to Transact – SQL” by Ken Henderson at the advice of my supervisor. Ken recommended installation of the two databases, so I found your site on the net. The installation was easy and the two new databases appeared in the Object Explorer window of my Microsoft SQL Server Management Studio. However, when I tried to open each of the databases, I got a message box as follows:

    “The database Northwind is not accessible”
    “The database pubs is not accessible”

    Perhaps you can reply with explanation of how I should gain access to these two new databases. I offer my thanks in advance to your reply, and certainly appreciated your help with the installation.

    Sincerely,
    Doug Thornton
    Systems Integration Programmer

  3. Pinal Dave,
    I answered my own question, as I just had to authenticate via Windows instead of SQLServer when logging in using Management Studio. Access to databases complete !!!
    Doug Thornton 082307

    • Man this is whay I was looking for days…Adventure Works DB opens with WIndows Authentication…
      We can just create a user
      Go to SQL Server Manager Object Explorer
      Security->Login
      Create a User for Windows Authentication
      Go To User Mapping and check Adventure Works DB.
      And click ok , if it gives error as Adventure Works is Read only.Go back an uncheck it.

      Thats it it works..

      Thanks man

  4. How do I Verify that NorthWind and Pubs database are installed correctly at the end?

    I can load both databases and then I seem to be stuck on what to do next.

    I am using method 2.

    Also is this safe to do as I am connected to my workplace master database and do not want to affect anything in there.

    Thanks Pinal

    Jatinder

  5. I cant seem to select statement. This is first time I’ve used SQL, hence why i’m not grasping it as easy as other people.

    Can you show me please? tks

  6. Once the Northwind database is installed, how do I go about getting/setting the admin username and password as well as other usernames and passwords?

    Thanks
    Jason

  7. Hi ,

    i am getting the follwing error when i tried to attach Adventure Works Database.

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
    Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

    ——————————

    Please Help.

    Thanks In Advance

  8. Hello Pinal,

    I am beginner of sql server 2005 and to the stored procedure.
    I have created simple stored proc.
    but it gives me error like
    “The specified schema name “hr” either does not exist or you do not have permission to use it.”

    i hv given the sp name hr.try
    database is Nirmal
    and table name is unamepass.

    I am waiting for your response

    Thank you very much
    Nirmal Shah

  9. Hello Yuva,
    this error message appears when you try to attach a Microsoft SQL Server 2005 database in SQL Server 2000.

    This behaviour occurs because attaching SQL Server 2005 databases in SQL Server 2000 is not supported. Upgrade your database server to SQL Server 2005.

  10. I was looking all over for a SQL Server 2005 version of the Northwind database and couldn’t find it anywhere… thanks very much for your help!

  11. Hi I too have a problem similar to that posted by YUVA.
    Getting the similar error as follows :
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Could not find row in sysindexes for database ID 9, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
    Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

    And I do see sql 2000 files on my system.
    However I installed Sql server 2005 and am not able to attach adventureworks database into it.
    I dont know how to Upgrade my database server to SQL Server 2005.
    No option is available to remove sql 2000 from my system in add/remove programs.
    I would appreciate if anyone would be able to get me out from this problem.
    Thanks in advance.

  12. Didn’t see this mentioned — in Vista, if you’re executing sqlcmd from the command prompt, run cmd.exe as Administrator… otherwise the script will puke all over the place with ‘permission denied’ and ‘does not exist’ errors.

  13. Thank you so much for your commitment in providing this essential info…so many tutorials (ex. the ASP.NET 2.0 material I’m working with currently) is requiring the NWind db (and many legacy materials reference the Pubs db, of course), and to have such a clear resource to work with makes it a reality…and also reviewing the import scripts that create the db is a valuable exercise as well. CHEERS!

  14. Good article- not just the “what” but also the “why”.

    Unfortunately trying to install the database files for use with Visual studio 2003 and sql server 2005 is a minefield and he has done his best to find a path through it.

  15. I downloaded SQL2000SampleDb.msi and now I have SQL Server 2000 Sample Databases in my C drive. Then I tried executing this command in the command prompt

    c:\>sqlcmd -S .\InstanceName -i instpubs.sql
    c:\>sqlcmd -S .\InstanceName -i instnwnd.sql

    but I got this error,
    ‘sqlcmd’ is not recognized as an internal or external command, operable program or batch file.

    the I tried the other set of command too
    c:\>sqlcmd -i instpubs.sql
    c:\>sqlcmd -i instnwnd.sql

    again I got the same error.. cannot figure out why. Please help me
    Thanks

  16. hey Pinal,

    Thanks for this post..just the thing I was looking out for.

    Give me a call anytime you land up in Ahmedabad, would be great to meet you…not that I’m a big techie..but i undestand whats going on :)

    regards
    Harish

  17. For all those who are using Vista – you should start cmd with administrator privileges.
    Go to C:\windows\system32\cmd.exe right clik and click “Run as Administrator”. Than firs option for installation threw command prompt will work just fine.

  18. I have developed an application in Visual C#.net 2005 it is connected to a Sql Server database at back end.Now i want to create a setup project which should first install my application as well as Sql server. i.e when i install my application it also installs Sql Server if it is not already installed.

    How can i do this?

    I will be very thankful.

  19. I am not able to download “SQL2000SampleDb.msi” from Microsoft url becuase of some security issues is there any other place where i can download it from
    Thanks

  20. This is an excellent stuff.

    I’m learning SQL Server 2005 and ASP .NET, please help me to find any site which explains in such a simple English to learn, I’ll be glad.

    Thanks in advance

    Sri Hari A

  21. Hi Pinal Dave,

    I followed your instructions to install the northwind database, it worked! Thank you very much.

    You are the man!

    Greetings from the Netherlands,
    KdV

  22. Dear Bro,

    Is there any impact if i delete Northwind and AdventureWorks db? I am not suppose to install it at my production db.
    If yes, do i just right click on the db name and choose ‘delete’?

    Thanks

    Regards

  23. Thanks a lot for making this easy -

    My only wish would be that Microsoft would ship northwind as part of the SQL Server install!

    Best regards,
    Lee

  24. I am someone who is entirely new in this field. Your article really did help me to install the two database. Thanks a lot.

  25. Many thanks. Method 1 even worked! Just ensure you have closed down Visual Studio before you do it(Sounds obvious but I thought Id mention it.)

  26. my name is urbain, i am learning sql so please i need your help, can you please send me adventure works database download to my email which is so i can download it to my computer and i can practice and please i need your help

  27. Hi:

    I download SQL2000SampleDb.msi (288k).
    But, I can’t run it.
    It used ‘Windows installer’ to run that file and give me the following message.

    “This installation package could not be opened. Contact the application vendor to verify that this is a valid Windows installer package.”

    Can you tell me how to fix it? Thanks.

    Regards,
    JJ

  28. Pingback: SQL SERVER - 2008 - Download and Install Samples Database AdventureWorks 2005 - Detail Tutorial Journey to SQL Authority with Pinal Dave

  29. hi i was trying to attach adventure works database but it gave me error stating failed to retrieve data for this request can u pls show some other way of attaching adventureworks database…

  30. Pingback: SQL SERVER - 2005 NorthWind Database or AdventureWorks Database - Samples Databases Journey to SQL Authority with Pinal Dave

  31. According to the installation package, AdventureWorks installed correctly but I can not find it in order to attach it.

    Also having trouble with attaching Northwind and Pubs. These I can find, but they fail to attach. I get the following message:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SQLServer.Express.ConnectionInfo)

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

    I am really in need of a fix for this. Thanks in advance.

  32. Dear Pinal,

    many thanks for instructions, they helped a lot. I did attach successfully Northwind and Pubs databases.

    Kind regards,

    Shavkat.

  33. Dear Sir,
    I m dhirendra Singh from DRDA, auraiya(UP). Plz, send me your course details and fee structure. I join your group.

    Dhirendra Singh

  34. Hi Dave,
    can you explain how to configure Adveture works databasae in sql 2005.

    and i need some help to extract full text from a column using prefix i tried but when i give 2 or 3 char for a word it shows blank why?

    do explain

    Thank you

  35. Hi David, after hours looking and trying diferent things I finally found this site, I it took me 2 minutes to install pubs and nwnd, THANKS.
    Santi

  36. Hello,
    I m doing attach mdf file of sqlserver2000 to sqlserver2005 database but while attaching file, it is showing some error like..
    mdf file is read only. you cant do this.
    I also make file non read only but still it is showing the same.
    Can you tell me the solution ??

  37. hi
    after connecting the sql srever2005 management studio express with windows authentication, i am try to attach northwind database i got this errors

    TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ——————————
    ADDITIONAL INFORMATION:

    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
    Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.). (Microsoft SQL Server, Error: 17750)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=17750&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    pls help me

  38. This is the best explanation one could ever give..thanks a lot..
    but in the first method i am getting this error..thought i was able to run second method.can u tel me solution for this..

    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

  39. ur extremely superb.. I saved lot of my .. time to day.. thanks.. guys if ur missing the sql authority .. ur definitely missing a good one..

    goodluck.

  40. hi,

    I was trying to install advntureswork .LT from this link

    “http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004″
    from which i have installed advwrks.DW and advwrks.BI which worked really well but when I was trying 4r .LT it was installed but it says “read only ”

    can i know how to install it perfectly.please help me.

  41. Hi Dave

    I get this error when I try attaching the db.
    Please Suggest.

    Attach database failed for Server ‘DIVZ-PC\SQLEXPRESS’. (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Database ‘pubs’ cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. (Microsoft SQL Server, Error: 3415)

  42. Not able to see the files in Sql Studio Tree

    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

    Is this because of user privilage issue.

    Naveen

  43. Thank you!!! I was having no luck figuring out how to install Northwind until I found your site. MS should be installing these samples if they’re going to continue to refer to them in their tutorials (e.g. the VisualStudio 2008 web development “walkthrough” tutorials.)

    Boy am I glad I found your site! ;0)

  44. JJ is correct. One can no longer run the downloaded SQL2000SampleDb.msi . It just produces an error saying “This installation package could not be opened”

    Microsoft really knows how to screw things up!

  45. i am getting this error while running command

    c:\>sqlcmd -S .\InstanceName -i instnwnd.sql

    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    Please help .

  46. Thank You.

    It was nearly impossible to find out how to install the .mdf and .ldf file. I tried to select File–>open, but the programm said : “No edito avilable for Northwind.mdf” !!!!
    I also tried “new –> Database” but i cannot open an existing.
    Not so easy, because im speaking english not very well!

    Thank You!

  47. Thanks, it work for me. i use windows vista so i install sql 2005,with compatibility setting of windows xp service park 2..
    And it worked well.

  48. Hy, my name is marko,
    when I trying to attach database on SQL Server 2008 i am getting this error while running command

    c:\>sqlcmd -S .\InstanceName -i instnwnd.sql

    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    I have Windows Vista Ultimate X64 SP 2 Operation System

    Please help .

  49. Hi,

    I am not able to attach a MDF file to SQL Server 2005 developer’s edition in Windows Server 2003 system.

    But able to attach the same to SQl Server 2005 developer’s edition in Windows XP system.

    is there any caveats in attaching MDF files in SQl Server 2005 developer’s edition in different OS.

    I am using Management Studio and sa login in both cases to attach the files.

    Thanks in advance

  50. Hi Dave,

    I need help a topic that is soo boring for me because i dont understand.
    Today i searched different resources on the internet to find using of full text search property by SQL Server 2008 easily, but i did not.
    Actually i reached some information but it is not enough for my understanding.
    What am i doing to use this property correctly?

    Where am i using it?

    Could you please give an advice about it:)

    Are there any websites that include understanding information and samples about it on the internet …

    Thanks for your interest.

    Ayşegül.

  51. Hi Pinal,

    Can I restore a SQLServer 2000 DB into SQLServer 2008?
    I want to restore the Cognos 8 SQLServer samples which are meant for 2000 into 2008.

    If yes please help.

    Thanks in advacne,
    Sunil.

  52. TITLE: Microsoft SQL Server Management Studio
    ——————————

    Attach database failed for Server ‘SANAAELHASHMY’. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Unable to open the physical file “C:\SQL Server 2000 Sample Databases\northwnd.mdf”. Operating system error 5: “5(failed to retrieve text for this error. Reason: 15105)”. (Microsoft SQL Server, Error: 5120)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  53. Hey,
    Its really helpful
    1st method gives error Error Locating Server/Instance Specified [xFFFFFFFF].
    but from 2nd method its done.
    But m really keen to know about this error how can i solve it

  54. I am getting the SQL native client like few others in this thread. I am trying to attach to SQL Express 2008 in Windows 7 Enterprise machine.

    HResult 0×2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
    allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

  55. All,

    Those who are using SQLExpress and experiencing below error while attaching NORTHWIND database if using to connect to ASP.NET can use the identity impersonation along with user name and password (as interim solution)

    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this ….

  56. Hi Pinal,

    It’s very nice. i have installed it but through cmd prompt i get some error . i don’t have C Drive in this case what i do? please reply me …………..

    I love all your article ..

  57. I am trying option 2.
    Have run .msi and everything seems to be installed as outlined above.
    However, when I open instnwnd.sql (it opens a new query window) and then press F5 to run it prompts me to connect to a database engine and I am stuck.

    What do I type in the Server Name?

    Thanks

  58. hi Pinal,

    I could download .mdf and .ldf files of Northwind and Pubs databases and easily attach them as well in my SSMS but when I try to extract data fom these two databases ,I’am getting the error “Invalid object name ‘Northwind’. ” ..,,,Could you please help me with this problem..Thanks in advance

    Thanks,
    Amruta

  59. i install sql server 2005,but it’s show only system data base(master,model,msdb,temdb).I want to work in AdvetureWorks data base but it’s not show in data base ,what can i do???????????

  60. Thanks Pinal. I tried to install Northwind db to my SQL Express 2008. I tried method 1 and 3 and it successfully did. I did some work using visual C# 2010 express and it could connect to the DB. But the problem is that when I restart the machine and go back to the SQL Management console, NWind doesn’t connect. When I go to C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA folder, alongwith Northwind mdf & ldf files, I see a new file NORTHWND_log.LDF.
    Can you offer any clue as to why is this happening ?

  61. If you want to install northwind and pbs on windows 2008 then try to install it with third method i.e,cmd method. Only then it will add databases. It wont install with 2nd method.

  62. Hello,I want a large database i.e. high dimensional data.For that i required sample database for my mtech project. can i get it from here.Please do let me know about this.After installing this Northwind Database can i get huge amount of database

  63. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s