+ Reply to Thread
Results 1 to 23 of 23
  1. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #1

    Default Question about Adventure Works practice tables

    I ran the script and have "most" of them in my SQL studio under AdventuerWorks. The problem is a lot of the tables are missing columns or missing primary tables. Like Person.Person is not listed. Kind of hard to go through the lessons when you don't have the appropriate tables lol.

    I am going through ms press 70-433. I am finding several columns from tables are missing and some tables are missing.

    Any thoughts?
    Reply With Quote Quote  

  2. SS -->
  3. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #2
    I just checked my adventure works database tables and I do have person.person. However, I did a tutorial not long ago for Microsoft expression web and my tables were definitely a little different than the ones in the tutorial.
    Reply With Quote Quote  

  4. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #3
    Did you in fact download the version of the DB used by you text? There are very different versions of the AW database out there. It may be that the text uses an older version (2000). I just checked a SQL Server 2005 test instance here at work and the schema is in line with what you describe: no Person.Person. Did you download the AW2008R2 databases or did you use something else?

    Either way, it almost does not matter. I recll having this exact issue with Amazon.com: SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach (978159059980: Joseph Sack: Books but I learned a lot from addapting the queries to a slightly different schema. So you could go that path.
    Reply With Quote Quote  

  5. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #4
    Quote Originally Posted by gosh1976 View Post
    I just checked my adventure works database tables and I do have person.person. However, I did a tutorial not long ago for Microsoft expression web and my tables were definitely a little different than the ones in the tutorial.

    Thanks for the info
    Reply With Quote Quote  

  6. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #5
    Quote Originally Posted by RobertKaucher View Post
    Did you in fact download the version of the DB used by you text? There are very different versions of the AW database out there. It may be that the text uses an older version (2000). I just checked a SQL Server 2005 test instance here at work and the schema is in line with what you describe: no Person.Person. Did you download the AW2008R2 databases or did you use something else?

    Either way, it almost does not matter. I recll having this exact issue with Amazon.com: SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach (978159059980: Joseph Sack: Books but I learned a lot from addapting the queries to a slightly different schema. So you could go that path.

    I think I am going to do what you suggested.
    Reply With Quote Quote  

  7. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #6
    It looks like there was once an AW2008 REPORTING SERVICES DB with a different schema. This seems to be the DB that won out in early 2009. I'm trying to locate a copy of the database that has [Person].[Person] - If I manage to, I'm going to write a short blog post about this. Also, if you have Denali installed you seem to get the ISupportInitialize.EndEdit error when installing the sample DBs. Should not be so hard to install these things. Just give me a zip file with the DBS to install or even better a couple SQL scripts I can run.

    Here is the documentaion for the 2005/2008 AdventureWorks Schema:
    Schemas in AdventureWorks

    Here are the downloads for the 2008 Databases:
    Microsoft SQL Server Product Samples: Database

    Here is the AW2000 download:
    Download details: Adventure Works Cycles Database 2000
    Reply With Quote Quote  

  8. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #7
    I'm pretty sure the version I installed that has person.person is adventureworks_sr4 located here: msftdbprodsamples - Release: SQL Server 2008 SR4
    Reply With Quote Quote  

  9. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #8
    Quote Originally Posted by gosh1976 View Post
    I'm pretty sure the version I installed that has person.person is adventureworks_sr4 located here: msftdbprodsamples - Release: SQL Server 2008 SR4
    Odd that all the versions I have used did not have Person.Person. I'm downloading that right now. I'd be willing to wager 10-1 that Iwon't be able to install it, though.

    I installed it to an express instance of SQL Server 2008 (non R2) - there is no Person.Person.
    Last edited by RobertKaucher; 02-01-2011 at 03:44 PM.
    Reply With Quote Quote  

  10. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #9
    Quote Originally Posted by RobertKaucher View Post
    Odd that all the versions I have used did not have Person.Person. I'm downloading that right now. I'd be willing to wager 10-1 that Iwon't be able to install it, though.

    I installed it to an express instance of SQL Server 2008 (non R2) - there is no Person.Person.

    Same version I installed it in. Very bizarre
    Reply With Quote Quote  

  11. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #10
    Quote Originally Posted by RobertKaucher View Post
    Odd that all the versions I have used did not have Person.Person. I'm downloading that right now. I'd be willing to wager 10-1 that Iwon't be able to install it, though.

    I installed it to an express instance of SQL Server 2008 (non R2) - there is no Person.Person.
    I installed it on the full version of sql server 2008 r2 - and person.person is right there. I am 99.99% sure that is the version of the adventure works database I installed because I found the file I downloaded in my downloads folder.
    Reply With Quote Quote  

  12. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #11
    Quote Originally Posted by gosh1976 View Post
    I installed it on the full version of sql server 2008 r2 - and person.person is right there. I am 99.99% sure that is the version of the adventure works database I installed because I found the file I downloaded in my downloads folder.
    I downloaded: AdventureWorks2008_SR4.exe from your link.
    And when I run:

    USE AdventureWorks2008
    GO
    SELECT TABLE_SCHEMA AS 'Schema Name'
    ,TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'Person'
    GO

    I get:
    Schema Name table_name
    Person Address
    Person AddressType
    Person Contact
    Person ContactType
    Person CountryRegion
    Person vAdditionalContactInfo
    Person vStateProvinceCountryRegion
    Person StateProvince
    Reply With Quote Quote  

  13. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #12
    I executed the same query just copied and pasted and this is what I got:

    Person Address
    Person AddressType
    Person StateProvince
    Person BusinessEntity
    Person BusinessEntityAddress
    Person BusinessEntityContact
    Person ContactType
    Person CountryRegion
    Person EmailAddress
    Person Password
    Person Person
    Person vAdditionalContactInfo
    Person PersonPhone
    Person PhoneNumberType
    Person vStateProvinceCountryRegion


    I'll check my external hdd when I get home to see if I downloaded another version that I don't remember.
    Reply With Quote Quote  

  14. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #13
    Quote Originally Posted by gosh1976 View Post
    I executed the same query just copied and pasted and this is what I got:

    Person Address
    Person AddressType
    Person StateProvince
    Person BusinessEntity
    Person BusinessEntityAddress
    Person BusinessEntityContact
    Person ContactType
    Person CountryRegion
    Person EmailAddress
    Person Password
    Person Person
    Person vAdditionalContactInfo
    Person PersonPhone
    Person PhoneNumberType
    Person vStateProvinceCountryRegion


    I'll check my external hdd when I get home to see if I downloaded another version that I don't remember.
    Wow! That is very different. Could you do us all a huge favor and generate the scripts and include the data and post it some place?

    Right Click the DB -> GenerateScripts ->

    On the set script options page click Advanced

    Under type of data to script select Schema and Data

    Then complete the wizard.
    Reply With Quote Quote  

  15. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #14
    That creates a 522MB file wonder where I can upload a file that size for free?
    Reply With Quote Quote  

  16. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #15
    Quote Originally Posted by gosh1976 View Post
    That creates a 522MB file wonder where I can upload a file that size for free?
    DANG! If you zip that it will be just over 25 MB.
    Reply With Quote Quote  

  17. Senior Member
    Join Date
    Jun 2007
    Posts
    440

    Certifications
    A+, Net+, MCDST, CCENT, MCTS: Win 7 Configuring, CCNA, CWTS, CCNA Voice, A.A.S. in Networking
    #16
    Here is a link to that file: http://www.filedropper.com/script2
    Last edited by gosh1976; 02-02-2011 at 02:34 AM.
    Reply With Quote Quote  

  18. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #17
    +rep! Thanks, I am going to run it via SQLCMD today. I am working from home so around lunch time I'll look at it. Does not look like I can open it in SSMS! I'll probably change the logical and physical file names in the script.
    Reply With Quote Quote  

  19. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #18
    Quote Originally Posted by RobertKaucher View Post
    +rep! Thanks, I am going to run it via SQLCMD today. I am working from home so around lunch time I'll look at it. Does not look like I can open it in SSMS! I'll probably change the logical and physical file names in the script.
    Robert are you talking about the .mdf file?

    I had to go into properties>security>advanced>uncheck inheritable permission's from this object's parent.
    Reply With Quote Quote  

  20. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #19
    Quote Originally Posted by N2IT View Post
    Robert are you talking about the .mdf file?

    I had to go into properties>security>advanced>uncheck inheritable permission's from this object's parent.
    In the script that was posted. I already have AdventureWorks2008 installed - so I would need different logical and physical names for the database with Person.Person schema.
    Reply With Quote Quote  

  21. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #20
    Quote Originally Posted by RobertKaucher View Post
    In the script that was posted. I already have AdventureWorks2008 installed - so I would need different logical and physical names for the database with Person.Person schema.
    BTW I am finding that I am fine tuning my SQL not having the columns and tables present. Each time I dig into SSMS I am forced to think about the relationships and how the data is going to look after I write the query. Instead of just learning the syntax, which I have quiet a bit of exposure to, I am forcing myself to actually think like an analyst and pull data that would make sense.

    BTW I think I am going to schedule the exam in 2-3 weeks. I haven't locked into anything yet and I am feeling kind of sloppy.
    Reply With Quote Quote  

  22. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #21
    Quote Originally Posted by N2IT View Post
    BTW I am finding that I am fine tuning my SQL not having the columns and tables present. Each time I dig into SSMS I am forced to think about the relationships and how the data is going to look after I write the query. Instead of just learning the syntax, which I have quiet a bit of exposure to, I am forcing myself to actually think like an analyst and pull data that would make sense.

    BTW I think I am going to schedule the exam in 2-3 weeks. I haven't locked into anything yet and I am feeling kind of sloppy.
    Yes, that's the kind of learning I was talking about. And I think that kind of conceptual stuff will actually cement the T-SQL better.

    Scheduling an exam always helps me focus...
    Reply With Quote Quote  

  23. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #22
    Quote Originally Posted by RobertKaucher View Post
    Yes, that's the kind of learning I was talking about. And I think that kind of conceptual stuff will actually cement the T-SQL better.

    Scheduling an exam always helps me focus...

    Robert thanks for hangin in there with me on this one. After I get off work I am scheduling this exam 2 weeks out. The book isn't all that big and a lot of the information I already know.

    I think this one would be the one and only SQL cert I could pass within a few weeks since it is so DML driven. I will really have to get into the other parts of the material though. This is going to be challenging. lol



    ****Scheduled for the 10th of this month. That's the most aggressive I have ever gotten with an exam. Wish me luck I'll need it.
    Last edited by N2IT; 02-02-2011 at 07:08 PM.
    Reply With Quote Quote  

  24. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #23
    Robert

    I've completed the book. I just wanted to say some of the chapters go over parts of SQL that I don't think I'll ever use. I mean a lot of it!

    I wish they had a business user certification to show you know how to use SQL to write subqueries, joins, unions, and various other DML techniques. I am going to give this a go. I have taken SQL and database management classes before so the methodology and terminology is still there. A lot of this is review, however Microsoft does things different than I was taught.
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks