Question about Adventure Works practice tables

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.

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.

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

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.

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

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

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.

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

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.

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

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.

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.

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

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.

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

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

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.

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.

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.

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

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.