none
Sample code not working, missing view in db

    Question

  • Hi,

    I have just started to work through SQL Server 2008 - BI Development & Maintnenace. I downloaded the sample AdventureWorks databases (SQL Server 2008 SR4) as instructed in the book. I have only got to Chapter 1 Lesson 3, and have hit a problem. Exercise 2 has a sql command that I copied from the training kit source CustomerSourceQuery.sql. This gives me an error when I try and press OK.

    TITLE: Microsoft Visual Studio
    ------------------------------

    Error at Data Flow Task [Customer Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Invalid column name 'PersonID'.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Invalid column name 'BusinessEntityID'.".

    ------------------------------
    ADDITIONAL INFORMATION:

    Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Looking at the query, it refers to a view that doesn't exist in the database (Sales.vPersonDemographics).

    One thing that does stand out. When I installed the above sample database, I didn't get AdventureWorks2008, just AdventureWorks, AdventureWorksDW, AdventureWorksDW2008, AdventureWorksLT and AdventureWorksLT2008.

    Where have I gone wrong with the installation, have I downloaded the wrong file. I'm using the 180 day trial that came with the training kit.

    Please help.

    Kind Regards

    Lee Scrivens

    Thursday, January 07, 2010 3:46 PM

Answers

  • Hi I've been having this exact problem... Was having the same symptoms as everyone else.  I resolved this issue by right clicking my instance of SQL in config manager.

    In this case MSSQLSERVER if you selected the default instance name in installation.  Then clicked on the FILESTREAM tab.  I then clicked

    Enable FILESTREAM for Transact-SQL access.  THE INSTALLER DOES NOT SET THIS TICKBOX EVEN IF YOU ENABLE FILESTREAM THROUGH SQL INSTALLATION!

    This is a bug in the SQL installation process in my view.  Microsoft sort it out!!! People that are new to these books need the step by step process to actually WORK so they can learn instead of spending all their time hunting around for tiny tickboxes etc...

    I hope this sorts anyones problem out that was missing the AdventureWorks2008 db.

    Also this will only work if you have made sure the following services are running etc. as described above.
    Monday, April 11, 2011 9:55 PM

All replies

  • Hi Lee,

    There were substantial schema changes made between AdventureWorks (introduced for SQL Server 2005) and AdventureWorks2008.  The original AdventureWorks database has columns named such as PersonID, EmployeeID, CustomerID, etc.  In AdventureWorks2008, these have been replaced by a single column named BusinessEntityID. 

    If the book makes references to BusinessEntityID and AdventureWorks2008, then you need the database you already have installed. Otherwise; you  need to download the original AdventureWorks database, which can be downloaded from here: http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=MSFTDBProdSamples&ReleaseId=4004

    I'm assuming that you can see the AdventureWorks2008 databases in Object Explorer (in SQL Server Management Studio) and can view the tables and other objects in the database (regardless of what the examples in the book may have you doing).    If not, then you likely did not successfully install AdventureWorks2008.  There  are some prerequisties for installing that database that can cause the installation to silently fail.  Please see the following information: http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Installer%20Help

    Hope that helps,

    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, January 11, 2010 2:35 AM
    Moderator
  • I have the same issue. I am aslo using the same book MCTS Self-Paced Training kit(exam 70-448): Microsoft SQL Server 2008-Business Intelligence Development and Maintenance. I have unInstalled and resInstalled the Adventureworks Database SQL Server 2008 SR4. Still getting Invalid column name 'BusinessEntityID' and Invalid column name 'PersonID' 

     

    Monday, February 08, 2010 3:52 PM
  • Let's take a step back then and verify that you do actually have AdventureWorks2008 installed.   Please run the following query.

    SELECT name fromn sys.databases WHERE name LIKE 'Adventure%'

    It should return several AdventureWorks-related databases including "AdventureWorks2008".   If it does not, then the installation was not successful.  The AdventureWorks2008 database has 2 prerequisites that must be met. Full-text must be installed and running and Filestream must be enabled.   If those are conditions are not met, the installation does not work.  Please see http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites  Also, since that book was published, the installation of AdventureWorks has been improved, please make sure you are installing from here: http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=37109 


    If you do see AdventureWorks2008 in the list returned by the query, then please provide the exact code sample that is failing an we'll take it from there.

    Kind regards,

    Gail


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, February 08, 2010 5:09 PM
    Moderator
  • I have just runned the query and it didn't return any AdventureWorks-related databases. I am going to unInstall again and follow the steps listed. I will post my response after that
    Monday, February 08, 2010 6:05 PM
  • I have unInstalled the Adventureworks database. Followed the 2 prerequisites  as stated. ReInstalled from the link provided in your post. Now when I run the query you provided (SELECT name fromn sys.databases WHERE name LIKE 'Adventure%'), it returns 5 databases. (1). AdventureWorks (2).AdventureWorksDW. (3).AdventureWorksDW2008 (4).AdventureWorksLT (5).AdventureWorksLT2008. I don't see 'AdventureWorks2008'  The code I am running from the book is as follows:

    select convert(nvarchar(15),SC.AccountNumber) as CustomerAlternateKey,C.Title, C.FirstName, C.MiddleName,C.LastName, C.Suffix, C.EmailAddress,C.AddressLine1, C.AddressLine2,D.BirthDate, D.MaritalStatus,D.YearlyIncome, D.DateFirstPurchase,D.Gender, D.TotalChildren,D.NumberChildrenAtHome, D.Education,D.Occupation, D.HomeOwnerFlag,D.NumberCarsOwned from Sales.vIndividualCustomer C inner join Sales.Customer SC on C.BusinessEntityID = SC.PersonID inner join Sales.vPersonDemographics D on C.BusinessEntityID =D.BusinessEntityID

    Monday, February 08, 2010 7:02 PM
  • Okay. So, obviously, the code isn't going to work untill AdventureWorks2008 is correctly installed. 

    Please run the following query and report the results:

    SELECT @@VERSION


    Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights
    Monday, February 08, 2010 7:49 PM
    Moderator
  • This is the results of the query "select @@ version"

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

    Monday, February 08, 2010 8:07 PM
  • Ok. I have it worrking perfectly well now. Now I can see six databases
    AdventureWorks
    AdventureWorks2008
    AdventureWorksDW
    AdventureWorksDW2008
    AdventureWorksLT
    AdventureWorksLT2008

    The query is also working

    • Proposed as answer by denkyira Tuesday, February 09, 2010 1:39 PM
    Monday, February 08, 2010 11:17 PM
  • To help others who may face similar issue, I am writing my solution here:

    1. First although the prerequisites mentioned, Steps 7 and 8 as  optional, without turning FILESTREAM on, AdventureWorks OLTP 2008 is not enabled.
    2. I also made ensure that in the control panel, any installed Adventureworks is removed
    3. Using the SSMS to delete any earlier version of the AdventureWorks is not enough especially if you have uninstalled previous version of sql server.
    4. I made sure that under  Microsoft SQL Server\....MSSQL\Data old Adventureworks databases are really deleted.
    5.Now I followed the instruction to turn SQL Full-text Filter Daemon Launcher  services and FILESTREAM on.
    6. Did clean installation of
    AdventureWorks2008_SR4.exe  and it works
    • Proposed as answer by denkyira Tuesday, February 09, 2010 2:01 PM
    Tuesday, February 09, 2010 1:59 PM
  • Hi denkyira

    I have followed your instructions and I still don't see the AdventureWorks2008 database that the book talks about.

    I have spent a whole afternoon on this and I am very frustrated. Is there anything else the you (or anyone else) can please suggest?

    I am using SQL Server 2008 Developer Edition on Windows XP Professional SP 2

    Thanks!


    Regards Amir
    Thursday, April 15, 2010 3:56 PM
  • Can you run this query and let us see what you have.

    SELECT @@VERSION


    Ebenezer
    Thursday, April 15, 2010 5:28 PM
  • Please follow these instructions to reInstall the Adventureworks. From what you are saying the adventureworks was not properly Installed .

    1. Make sure that all Instances of Adventureworks are removed from your machine.

    2. Download the msi installer for  AdventureWorks databases you want to  install fromCodePlex

    3 IMPORTANT: Ensure you have Full-text Filter  Daemon Launcher  Service running and

    4.IMPORTANT:  FILESTREAM services enabled on your SQL Server . 

    Now follow the rest of the instructions to do clean Installation of  the AdventureWorks.

    After Installation please run "select * from sysdatabases"

    You should see six databases as follows:

    AdventureWorks
    AdventureWorks2008
    AdventureWorksDW
    AdventureWorksDW2008
    AdventureWorksLT
    AdventureWorksLT2008

     If you get less than six databases, then installation was not successful.

    Let me know after you follow these steps.


    Ebenezer

    Ebenezer
    Thursday, April 15, 2010 8:23 PM
  • Hi

    SELECT @@VERSION returned:

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)


    Regards Amir
    Friday, April 16, 2010 7:41 AM
  • This is the same version I am using.  Please follow these instructions to reInstall the Adventureworks. From what you are saying the adventureworks was not properly Installed .

    1. Make sure that all Instances of Adventureworks are removed from your machine.

    2. Download the msi installer for  AdventureWorks databases you want to  install fromCodePlex

    3 IMPORTANT: Ensure you have Full-text Filter  Daemon Launcher  Service running and

    4.IMPORTANT:  FILESTREAM services enabled on your SQL Server . 

    Now follow the rest of the instructions to do clean Installation of  the AdventureWorks.

    After Installation please run "select * from sysdatabases"

    You should see six databases as follows:

    AdventureWorks
    AdventureWorks2008
    AdventureWorksDW
    AdventureWorksDW2008
    AdventureWorksLT
    AdventureWorksLT2008

     If you get less than six databases, then installation was not successful.

    Let me know after you follow these steps.


    Ebenezer
    Friday, April 16, 2010 11:23 AM
  • Hi

    I tried this all again and still no good. I looked in the log and there is an error saying failed to create C\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Documents. I Googled and came across this forum entry which suggested changing the SQL Server instance account to Local Service (mine was set to Network Service).

    Now, I can't even get SQL Server to start and when I try to do a SQL Server repair, it asks for the installtion media which I will have back tomorrow. So, untill then I am totally stuck!!!


    Regards Amir
    Monday, April 19, 2010 12:55 PM
  • Hi Amir, all what the error is telling you is, you still have some instances on your machine. The only way to get a proper Installation is to use a different instance name (eg. MSSQLSERVER2) when Installing. You have not completely removed all the instances of the previous Installation and therefore cannot reInstall using the same default Instance name. That is the only problem you are facing now.
    Ebenezer
    Monday, April 19, 2010 2:21 PM
  • Hi Ebenezer

    I detached all databases and deleted the physical files. Is there anything else to do? Googling uninstall adventureworks2008 does not return any sensible suggestions...


    Regards Amir
    Monday, April 19, 2010 3:59 PM
  • Hi Amir, detaching and  deleting all databases and the physical files is does not remove all files from your machine. If you don't want to Install with a different instance name, then, check

    1. control panel, add/Remove. do Uninstall from Add/Remove if it exist. Also

    2. Check c:\Program files for Microsft SQL Server 10 and delete all

     


    Ebenezer
    Monday, April 19, 2010 4:50 PM
  • Hi Ebenezer

    I re-installed SQL Server and still can't get the damn thing to work. I even tried it on a new SQL Server 2008 Express version and got exactly the same problems!

    I don't have time to waste on this anymore. Clearly I am not te only person who has this problem and it would be nice if Microsoft either fix the issue with the MSI or at least provide a fix or whatever as I have wasted almost two days now.

    Ebenezer - thanks for your help anyway.

     


    Regards Amir
    Tuesday, April 20, 2010 4:02 PM
  • I feel you pain Amir.   It has been frusturating to have to waste far too much time in an attempt to 'guess' at all of the things that could be wrong on the set up, configurations, and the code itself, just to finish chapter one. I bought the book to learn about BI, and I have yet to learn anything remotely related to BI because I can't get the code to work.  I have given up on the book and am now working from the Microsoft SSIS tutorials online, but I've already noticed that there are a number of comments about broken code there as well. 
    Sunday, May 16, 2010 11:55 PM
  • Hi Chris

    Yes, it is extremely frustrating and I hope someone in Microsoft is reading our posts. I too have given up on the book and AdventureWorks.


    Regards Amir
    Monday, May 17, 2010 7:43 AM
  • I've just came across the same scenerio. AdventureWorks has been updated and the query in this Book is now incorrect. Luckily its quite easy to see the issue from the query and the error message. All you need to do is to update the query so that it works with the latest AdventureWorks.

    I made the following 3 changes to the query in the book:

    1) inner join Sales.vPersonDemographics D to inner join Sales.vIndividualDemographics D

    2) on C.BusinessEntityID = SC.PersonID to on C.CustomerID = SC.CustomerID

    3) on C.BusinessEntityID = D.BusinessEntityID to on C.CustomerID = D.CustomerID

    My query now looks like this: select convert(nvarchar(15),SC.AccountNumber) as CustomerAlternateKey, C.Title, C.FirstName, C.MiddleName, C.LastName, C.Suffix, C.EmailAddress, C.AddressLine1, C.AddressLine2, D.BirthDate, D.MaritalStatus, D.YearlyIncome, D.DateFirstPurchase, D.Gender, D.TotalChildren, D.NumberChildrenAtHome, D.Education, D.Occupation, D.HomeOwnerFlag, D.NumberCarsOwned
    from Sales.vIndividualCustomer C
    inner join Sales.Customer SC
    on C.CustomerID = SC.CustomerID
    inner join Sales.vIndividualDemographics D
    on C.CustomerID = D.CustomerID

    Although you will probably come across further issues....

    Regards,

    Dave

    MCITP Database Administrator 2008

    • Proposed as answer by gka123 Sunday, July 04, 2010 6:01 PM
    • Edited by gka123 Sunday, July 04, 2010 6:52 PM Update
    Sunday, July 04, 2010 6:00 PM
  • gr8 job JKA123

     

    It works perfectly...

    Wednesday, September 08, 2010 10:29 PM
  • I am happy to see that the solution is helping others.
    Ebenezer
    • Proposed as answer by Juan Rey Pena Wednesday, March 16, 2011 6:33 PM
    • Unproposed as answer by Juan Rey Pena Wednesday, March 16, 2011 6:33 PM
    Thursday, September 09, 2010 2:46 AM
  • I have the same problem and i resolved here:

    1º. Delete ONLY the "AdventureWorks" database.

    2º. Execute AdventureWorks installer.

     

    Try it.

    • Proposed as answer by Juan Rey Pena Wednesday, March 16, 2011 6:35 PM
    Wednesday, March 16, 2011 6:35 PM
  • Thanx alot Denkyira your solution really did work for me :-)
    • Proposed as answer by jhowe1 Monday, April 11, 2011 9:50 PM
    • Unproposed as answer by jhowe1 Monday, April 11, 2011 9:50 PM
    Sunday, March 20, 2011 9:55 PM
  • Hi I've been having this exact problem... Was having the same symptoms as everyone else.  I resolved this issue by right clicking my instance of SQL in config manager.

    In this case MSSQLSERVER if you selected the default instance name in installation.  Then clicked on the FILESTREAM tab.  I then clicked

    Enable FILESTREAM for Transact-SQL access.  THE INSTALLER DOES NOT SET THIS TICKBOX EVEN IF YOU ENABLE FILESTREAM THROUGH SQL INSTALLATION!

    This is a bug in the SQL installation process in my view.  Microsoft sort it out!!! People that are new to these books need the step by step process to actually WORK so they can learn instead of spending all their time hunting around for tiny tickboxes etc...

    I hope this sorts anyones problem out that was missing the AdventureWorks2008 db.

    Also this will only work if you have made sure the following services are running etc. as described above.
    Monday, April 11, 2011 9:55 PM
  • I found the same problem it is an easy fix if you have the correct Data 

    connection.   

     

    BusinessEntityId is the incorrect name for sp.SalesPersonId (the confusion being in the prefix for SalesPerson is sp)

     line 16 reads this:

       INNER JOIN Sales.SalesOrderHeader AS soh 

          ON sp.SalesPersonID = soh.SalesPersonID

    Saturday, December 24, 2011 5:22 AM
  • Hi,

    I need some help here!  I've read through the posts in this forum and followed the steps.  However, after I install the databases from http://msftdbprodsamples.codeplex.com/releases/view/37109#DownloadId=96448, I only see the following databases:

    AdventureWorks
    AdventureWorksDW
    AdventureWorksDW2008
    AdventureWorksLT
    AdventureWorksLT2008

    I don't see AdventureWorks2008.  I've started the SQL Full-Text Filter Daemon Launcher service and turned FILESTREAM on as well.

    I executed the "SELECT @@VERSION" SQL command and got the following:

    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) 

    What am I doing wrong here?  Please help!

    Thursday, March 15, 2012 2:04 PM
  • Hi If you seeing only five databases, that means your installation was not successful. You don't turn-on FileStream after Installation. You have to enable FileStream during Installation. So if I were you I will unInstall, clean the system and re-Install. This time make sure to have FileStream check

    Ebenezer

    Thursday, March 15, 2012 2:16 PM
  • Hi If you are seeing only five databases, that means your installation was not successful. You don't turn-on FileStream after Installation. You have to enable FileStream during Installation. So if I were you I will unInstall, clean the system and re-Install. This time make sure to have FileStream check

    Ebenezer

    Thursday, March 15, 2012 2:18 PM
  • I don't understand something.. FILESTREAM option is turned on through the SQL Configuration Manager, right?  So, are you saying I need to do the following?

    1. Uninstall AdventureWorks (not sure how to do this - there's no option in Add/Remove programs)

    2. Turn FILESTREAM off.

    3. Run the installer to install the sample databases.

    4. While installer is running, turn FILESTREAM on

    Is that right?

    Thursday, March 15, 2012 2:22 PM
  • Correct, but I don't know where you will turn off FILESTREAM after you do unInstall. But follow that Instruction and let me know

    Ebenezer

    Thursday, March 15, 2012 2:55 PM
  • Hi denkyira, this did not work.  I still see 5 AdventureWorks databases.  I'm getting REALLY frustrated. I've been trying to fix this for days now.

    Thursday, March 15, 2012 3:24 PM
  • If it is the AdventureWorks Database, I can zip a working and properly Installed one, put it on skydrive so you just pick it and restore it. Let me know if you like it this way.

    Ebenezer

    Thursday, March 15, 2012 3:30 PM
  • I appreciate that denkyira, but there should be an easier solution to this.  I've followed the steps.. it just doesn't make sense why it's going wrong.  I've uninstalled and re-installed AdventureWorks more than 5 times and I never see AdventureWorks2008.

    Can you confirm that I have to download the databases at this link? http://msftdbprodsamples.codeplex.com/releases/view/37109

    Thursday, March 15, 2012 7:22 PM
  • Your issue is not the installation steps or the AdventureWorks. The problem is not complicated as you are making it seems. You need to clean your computer system after  so many uninstalls. you still have left overs. Please  make sure all Register keys etc are removed after uninstall. I stated this in my earlier post. ccleaner has an in built uninstall that will clean your registry. You can do a simple perfect installation if you make sure that your system is clean. If you live in the USA, you can send me an email address and I will send you a link to view your computer and help you. BUT CLEAN YOUR MACHINE before installing a new one simple


    Ebenezer

    Friday, March 16, 2012 4:30 AM
  • Thanks Denkyira, I contacted MS Press Book support and they showed me how to install the correct databases.  The only problem I have left is I can't deploy the AdventureWorks sample reports.  I get errors.  I am going to uninstall everything, clean the registry and re-install sql server 2008 and try again.  I'll let you know how it goes.  Thanks!

    Thursday, March 29, 2012 3:24 PM
  • Just let me know and I will help you to the end. 


    Ebenezer

    Thursday, March 29, 2012 3:45 PM
  • Gka,

    You saved me a lot of time and hassle.  It seems that everyone here was focused on their db install issues or other issues.  Your modified SQL was the perfect solution.  Thanks for it and for sticking to subject.

    Jerid

    Thursday, March 29, 2012 8:33 PM