Question about Adventure Works practice tables

N2IT

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?

gosh1976

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.

RobertKaucher

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.

N2IT

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

N2IT

RobertKaucher wrote: »

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.

RobertKaucher

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

gosh1976

I'm pretty sure the version I installed that has person.person is adventureworks_sr4 located here: msftdbprodsamples - Release: SQL Server 2008 SR4

RobertKaucher

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.

N2IT

RobertKaucher wrote: »

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

gosh1976

RobertKaucher wrote: »

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.

RobertKaucher

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

gosh1976

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.

RobertKaucher

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.

gosh1976

That creates a 522MB file wonder where I can upload a file that size for free?

RobertKaucher

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.

gosh1976

Here is a link to that file: http://www.filedropper.com/script2

RobertKaucher

+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.

N2IT

RobertKaucher wrote: »

+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.

RobertKaucher

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.

N2IT

RobertKaucher wrote: »

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.

RobertKaucher

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...

N2IT

RobertKaucher wrote: »

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.

N2IT

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.