Question about Adventure Works practice tables

N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
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?

Comments

  • gosh1976gosh1976 Member Posts: 441
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    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 (978159059980icon_cool.gif: Joseph Sack: Books but I learned a lot from addapting the queries to a slightly different schema. So you could go that path.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    gosh1976 wrote: »
    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
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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 (978159059980icon_cool.gif: 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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    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
  • gosh1976gosh1976 Member Posts: 441
    I'm pretty sure the version I installed that has person.person is adventureworks_sr4 located here: msftdbprodsamples - Release: SQL Server 2008 SR4
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    gosh1976 wrote: »
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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
  • gosh1976gosh1976 Member Posts: 441
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    gosh1976 wrote: »
    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
  • gosh1976gosh1976 Member Posts: 441
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    gosh1976 wrote: »
    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.
  • gosh1976gosh1976 Member Posts: 441
    That creates a 522MB file wonder where I can upload a file that size for free?
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    gosh1976 wrote: »
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    +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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    +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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    N2IT wrote: »
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,299 ■■■■■■■■■■
    N2IT wrote: »
    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...
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
Sign In or Register to comment.