Trainig Kit 70-432 - Chapter 4 problem (Spatial Location problem)

Question

1. Execute the following code to create a nonclustered index on the Person.Address table:

CREATE NONCLUSTERED INDEX idx_city ON Person.Address(City) INCLUDE (AddressLine1)

2. Execute the following code to create a fi ltered index on the Person.Address table:

CREATE NONCLUSTERED INDEX idx_city2 ON Person.Address(City)
INCLUDE (AddressLine1, AddressLine2)
WHERE AddressLine2 IS NOT NULL

3. Execute the following code to create a spatial index on the Person.Address table:

CREATE SPATIAL INDEX sidx_spatiallocation
ON Person.Address(SpatialLocation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (MEDIUM, LOW, MEDIUM, HIGH ),
CELLS_PER_OBJECT = 64);


Made 1. and 2. without problem, but when I try to put spatial code it gives error:

Msg 1911, Level 16, State 103, Line 1
Column name 'SpatialLocation' does not exist in the target table or view.

Please help me, I am a newbie :-)

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 1

The code example in step 3 works correctly for me.  Perhaps the column SpatialLocation was deleted or renamed in the database in a previous exercise.   

You should verify that the column exists in the Person.Address table.  You can do this by expanding the database and table nodes in Object Explorer (i.e. expand Databases, expand AdventureWorks2008, expand Tables, Expand Person.Address), or you can run the following statement.

 select * from sys.columns
 where object_id = OBJECT_ID('Person.Address');

If the column has been renamed, you can easily rename it.  If, for some reason, the column was deleted, you'll need to restore the database from backup (if you have one) or reinstall the database from Codeplex.

 

Hope that helps,

Gail

 

 

 

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Hello Gail

Thanks for your post.

I'm confused. I have downloaded and installed AdventureWorks2008 SR4.exe, all databases selected. There is no AdventureWorks2008 database, I have a Adventure Works, a AdventureWorksDW and a AdventureWorksDW 2008 database - which should I use? I thought I should use AdventureWorks mentioned in the book, but you are using AdventureWorks2008 database!

No problem running the script:

select * from sys.colums

where object_id = OBJECT_ID('Person.Address');

I have added screen prints, hope you can help me :-)

  

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

I am not familiar with the 70-432 training kit, but I am very familiar with the different versions of AdventureWorks.   The training kit should be telling you to use the AdventureWorks2008 database.  This is the database that was created for SQL Server 2008 and contains columns and objects not supported in the AdventureWorks database, which was originally developed for SQL Server 2005.

Without going into too much history, there is a 1 to 1 mapping of sample databases to SQL Server version.  You can certainly use the original AdventureWorks database on SQL Server 2008 and higher, but in your case, you really need AdventureWorks2008 because it contains the spatial data type column that you're trying to reference in the create spatial index statement.

The SR4 installation does have a listing for AdventureWorks2008, but that, and only that database, requires both full-text to be installed and filestream to be enabled.  If you don't have those prequisites, it skips over installing that database.  For example, in the screenshot below, it shows that the instance of SQL Server that I'm trying to install to does not have filestream enabled.

You should go back to the installation point (http://msftdbprodsamples.codeplex.com/releases/view/37109)  and walk through the Database Prerequisites instructions (there's a link to these on that page)  and then try to install again. 

 

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Hi Gail,

Thanks for your post.

I uninstalled the SQL2008 server and reinstalled the server. Now I have the AdventureWorks2008 database :-)

---

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.