Problem going forward in SQL Server 2008 Step by Step...

Question

Hello,

Bought the book, now that I have it, I see that many of you are not fond of it...

I have worked with ASP.NET and a little C#, and now want to combine that knowledge and learn how to administer SQL databases to create powerful applications for my business.

The part where I am stuck is the transition between Chapter 5 and Chapter 6.

In the final pages of Chapter 5 regarding database diagrams, I follow the steps exactly as the book outlines. However, in the book, I notice that tables from both the SBS and SBSFS databases are included in that diagram, and that the relationships in the diagram do not match the ones I have on my screen. Now, I did see some posts here saying the code in the book varies from the CD. What I had been doing is copying the code from the book in order to get the feel of doing, rather than clicking and executing the right code file. So, I deleted the databases, started from scratch in Chapter 4 on through 5, and the end result was the same thing I ended up with in the first place.

Assuming that the screenshot may have been a sample not to reflect actual results (i mean, why on earth would a beginners guide put screenshots in of what you are supposed to see, right? lol) I began moving on to Chapter 6. I got to the first example, and the lines of red text appeared. In fact, this is what its telling me:

 

Msg 1911, Level 16, State 1, Line 1

Column name 'CustomerID' does not exist in the target table or view.

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '('.

that the screenshot may have been a sample not to reflect actual results (i mean, why on earth would a beginners guide put screenshots in of what you are supposed

Obviously something is wrong here...

Do you suspect that I missed something that would have caused this? Or is this an error that many people have encountered?

As a big fan of technical manuals, I have to say that Wrox is my favorite shop for reference manuals and such, especially since they post all known errata for each book. That has saved me much time in certain endeavours. However, I have found Microsoft Press Step By Step books a way to get off the ground exremely fast for me, since the step by step approach is how I learn best - so these things are worth their weight in gold to me. I just wish that there was a place for MP to post their errata for their editions like Wrox...

Answer 1

HI Cheese !

The error is pretty simple , its unable to retrieve data from "ColumnID" as it doesn't exist in your source table.

Can you please post the query which you are running.

Thanks, Hasham

Answer 2

Hi,
It seems you are trying to execute DML(INSERT,UPDATE,DELETE) statement.
You are using ColumnID in your statement i think it is not available in table structure.
See carefully you are missing close parenthesis in your statement.
BTW,it is my assumption.

As Hasham said you need to post your query.

---

Shatrughna

Answer 3

Here is everything I have done so far...

 

Chapter 5 Master Script

USE SQL2008SBS
GO

CREATE SCHEMA HumanResources AUTHORIZATION dbo
GO

CREATE TABLE Customers.Customer
(CustomerID     INT         IDENTITY(1,1),
CompanyName     VARCHAR(50) NULL,
FirstName       VARCHAR(50) NULL,
LastName        VARCHAR(50) NULL,
ModifiedDate    DATE        NOT NULL CONSTRAINT df_modifieddate DEFAULT GETDATE(),
CONSTRAINT pk_customer PRIMARY KEY (CustomerID))
GO

CREATE TABLE Customers.CustomerAddress
(AddressID      INT         IDENTITY(1,1),
CustomerID      INT         NOT NULL,
AddressTypeID   INT         NOT NULL,
AddressLine1    VARCHAR(50) NOT NULL,
AddressLine2    VARCHAR(50) SPARSE NULL,
AddressLine3    VARCHAR(50) SPARSE NULL,
City            VARCHAR(50) NOT NULL,
StateProvinceID INT         NULL,
CountryID       INT         NULL,
CONSTRAINT pk_customeraddress PRIMARY KEY (AddressID))
GO

CREATE TABLE Orders.OrderHeader
(OrderID        INT         IDENTITY(1,1),
CustomerID      INT         NOT NULL,
OrderDate       DATE        NOT NULL CONSTRAINT df_orderdate DEFAULT GETDATE(),
SubTotal        MONEY       NOT NULL CONSTRAINT ck_subtotal CHECK (SubTotal > 0),
TaxAmount       MONEY       NOT NULL CONSTRAINT ck_taxamount CHECK (TaxAmount >= 0),
ShippingAmount  MONEY       NOT NULL CONSTRAINT ck_shippingamount CHECK (ShippingAmount >= 0),
GrandTotal      AS (SubTotal + TaxAmount + ShippingAmount),
FinalShipDate   DATE        NULL,
CONSTRAINT pk_orderheader PRIMARY KEY (OrderID))
GO

CREATE TABLE Orders.OrderDetail
(OrderDetailID  INT         IDENTITY(1,1),
OrderID         INT         NOT NULL,
SKU             CHAR(10)    NOT NULL,
Quantity        INT         NOT NULL CHECK (Quantity > 0),
UnitPrice       MONEY       NOT NULL CHECK (UnitPrice > 0),
ShipDate        DATE        NULL,
CONSTRAINT pk_orderdetail PRIMARY KEY (OrderDetailID))
GO

CREATE TABLE Products.Product
(ProductID          INT             IDENTITY(1,1),
SubcategoryID       INT             NOT NULL,
ProductName         VARCHAR(50)     NOT NULL,
ProductCost         MONEY           NOT NULL CHECK (ProductCost > 0),
ListPrice           MONEY           NOT NULL CHECK (ListPrice > 0),
ProductMargin       AS (ListPrice - ProductCost),
ProductDescription  XML             NULL,
CONSTRAINT pk_product PRIMARY KEY (ProductID))
GO

CREATE TABLE HumanResources.Employee
(EmployeeID     INT         IDENTITY(1,1),
FirstName       VARCHAR(50) NOT NULL,
LastName        VARCHAR(50) NOT NULL,
JobTitle        VARCHAR(50) NOT NULL,
BirthDate       DATE        NOT NULL,
HireDate        DATE        NOT NULL,
CONSTRAINT pk_employee PRIMARY KEY (EmployeeID))
GO

CREATE TABLE HumanResources.EmployeeAddress
(AddressID      INT         IDENTITY(1,1),
EmployeeID      INT         NOT NULL,
AddressTypeID   INT         NOT NULL,
AddressLine1    VARCHAR(50) NOT NULL,
AddressLine2    VARCHAR(50) SPARSE NULL,
AddressLine3    VARCHAR(50) SPARSE NULL,
City            VARCHAR(50) NOT NULL,
StateProvinceID INT         NULL,
CountryID       INT         NULL,
CONSTRAINT pk_employeeaddress PRIMARY KEY (AddressID))
GO

CREATE TABLE LookupTables.AddressType
(AddressTypeID      INT         IDENTITY(1,1),
AddressType         VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_addresstype PRIMARY KEY(AddressTypeID))
GO

CREATE TABLE LookupTables.Country
(CountryID      INT         IDENTITY(1,1),
CountryName     VARCHAR(70) NOT NULL UNIQUE,
CONSTRAINT pk_country PRIMARY KEY(CountryID))
GO

CREATE TABLE LookupTables.StateProvince
(StateProvinceID    INT         IDENTITY(1,1),
CountryID           INT         NOT NULL,
StateProvince       VARCHAR(50) NOT NULL UNIQUE,
StateProvinceAbbrev CHAR(2)     NOT NULL,
CONSTRAINT pk_stateprovince PRIMARY KEY(StateProvinceID))
GO

CREATE TABLE LookupTables.ProductCategory
(CategoryID     INT         IDENTITY(1,1),
Category        VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productcategory PRIMARY KEY(CategoryID))
GO

CREATE TABLE LookupTables.ProductSubCategory
(SubcategoryID      INT         IDENTITY(1,1),
CategoryID          INT         NOT NULL,
SubcategoryName     VARCHAR(20) NOT NULL UNIQUE,
CONSTRAINT pk_productsubcategory PRIMARY KEY(SubcategoryID))
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customertocustomeraddress FOREIGN KEY (CustomerID)
    REFERENCES Customers.Customer (CustomerID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstoaddresstype FOREIGN KEY (AddressTypeID)
    REFERENCES LookupTables.AddressType (AddressTypeID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstostateprovince FOREIGN KEY (StateProvinceID)
    REFERENCES LookupTables.StateProvince (StateProvinceID)    
GO

ALTER TABLE Customers.CustomerAddress
    ADD CONSTRAINT fk_customeraddresstocountry FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country (CountryID)    
GO

ALTER TABLE Orders.OrderHeader
    ADD CONSTRAINT fk_customertoorderheader FOREIGN KEY (CustomerID)
    REFERENCES Customers.Customer (CustomerID)    
GO

ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT fk_orderheadertoorderdeatils FOREIGN KEY (OrderID)
    REFERENCES Orders.OrderHeader (OrderID)    
GO

ALTER TABLE Products.Product
    ADD CONSTRAINT fk_producttosubcategory FOREIGN KEY (SubcategoryID)
    REFERENCES LookupTables.ProductSubCategory (SubcategoryID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeetoemployeeaddress FOREIGN KEY (EmployeeID)
    REFERENCES HumanResources.Employee (EmployeeID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstoaddresstype FOREIGN KEY (AddressTypeID)
    REFERENCES LookupTables.AddressType (AddressTypeID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstostateprovince FOREIGN KEY (StateProvinceID)
    REFERENCES LookupTables.StateProvince (StateProvinceID)    
GO

ALTER TABLE HumanResources.EmployeeAddress
    ADD CONSTRAINT fk_employeeaddresstocountry FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country (CountryID)    
GO

ALTER TABLE LookupTables.StateProvince
    ADD CONSTRAINT fk_countrytostateprovince FOREIGN KEY (CountryID)
    REFERENCES LookupTables.Country(CountryID)    
GO

ALTER TABLE LookupTables.ProductSubCategory
    ADD CONSTRAINT fk_productcategorytosubcategory FOREIGN KEY (CategoryID)
    REFERENCES LookupTables.ProductCategory(CategoryID)
GO

USE SQL2008SBSFS
GO

CREATE TABLE Products.ProductDocument
(DocumentID     UNIQUEIDENTIFIER    ROWGUIDCOL UNIQUE,
ProductID       INT                 NOT NULL,
DocumentType    VARCHAR(20)         NOT NULL,
Document        VARBINARY(MAX)      FILESTREAM NULL,
CONSTRAINT pk_productdocument PRIMARY KEY(DocumentID))
GO

Will post the schema, if required...

First step of chapter six:

USE SQL2008SBS
GO

--Must drop foreign key first, because it depends on the primary key
ALTER TABLE Orders.OrderDetail
    DROP CONSTRAINT fk_orderheadertoorderdeatils
GO   
--Change the existing primary keys to nonclustered
ALTER TABLE Orders.OrderHeader
    DROP CONSTRAINT pk_orderheader
GO
ALTER TABLE Orders.OrderHeader
    ADD CONSTRAINT pk_orderheader PRIMARY KEY NONCLUSTERED (OrderID)
GO
ALTER TABLE Orders.OrderDetail
    DROP CONSTRAINT pk_orderdetail
GO
ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT pk_orderdetail PRIMARY KEY NONCLUSTERED (OrderDetailID)
GO
--Recreate the foreign key (fix the misspelling from chapter 5)
ALTER TABLE Orders.OrderDetail
    ADD CONSTRAINT fk_orderheadertoorderdetails FOREIGN KEY (OrderID)
    REFERENCES Orders.OrderHeader (OrderID)    
GO
--Create new clustered indexes on the ShipDate/FinalShipDate columns
CREATE CLUSTERED INDEX icx_finalshipdate ON Orders.OrderHeader (FinalShipDate)
GO
CREATE CLUSTERED INDEX icx_shipdate ON Orders.OrderDetail (ShipDate)
GO

Answer 4

question, i have that book and i am missing like 4 or  of the CREATE TABLE, was that in your book or did you get it somewhere else. Also, i found that copying the failed line in note pad or wordpad allows me to identify what the issue is. Are you planning on taking the cert exam and when

Answer 5

here is the link to the errata that i found

 

http://oreilly.com/catalog/errata.csp?isbn=9780735626041

Answer 6

looks like your bug is a known conflict in the book

 

http://social.msdn.microsoft.com/Forums/en-IE/sqlgetstarted/thread/b43bc806-bfa0-4074-9f50-da98e603fc1b

Answer 7

Once again lost in translation:

Msg 1911, Level 16, State 1, Line 1
Column name 'CustomerID' does not exist in the target table or view.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Is their any hope to fix?

Answer 8

I'm having a similar problem but at an earlier point in Chapter 5.  When I try to add FK constraints I get the following errors.  Does anyone know how to fix this?  Thanks, Scott

CREATE

TABLE LookupTables.Country

(

CountryID INT IDENTITY(1,1),

CountryName

VARCHAR(70) NOT NULL UNIQUE,

CONSTRAINT

pk_country PRIMARY KEY(CountryID))

GO

CREATE

TABLE LookupTables.StateProvince

(

StateProvinceID INT IDENTITY(1,1),

CountryID I

NT NOT NULL,

StateProvince

VARCHAR(50) NOT NULL UNIQUE,

StateProvinceAbbrev

CHAR(2) NOT NULL,

CONSTRAINT

pk_stateprovince PRIMARY KEY(StateProvinceID))

GO

ALTER

TABLE LookupTables.StateProvince

 ADD CONSTRAINT fk_countrytostateprovince FOREIGN KEY (CountryID)

 REFERENCES LookupTables.Country(CountryID)

GO

Msg 1769, Level 16, State 1, Line 2

Foreign key 'fk_countrytostateprovince' references invalid column 'CountryID' in referencing table 'StateProvince'.

Msg 1750, Level 16, State 0, Line 2

Could not create constraint. See previous errors.    

Answer 9

Hi Scott!

The following scripts executed fine on MS SQL Server 2008 R2;

CREATE    TABLE Country
(
CountryID    INT IDENTITY(1,1),
CountryName    VARCHAR(70) NOT NULL UNIQUE,
CONSTRAINT    pk_country PRIMARY KEY(CountryID))

GO

CREATE    TABLE StateProvince
(
StateProvinceID    INT IDENTITY(1,1),
CountryID        INT NOT NULL,
StateProvince    VARCHAR(50) NOT NULL UNIQUE,
StateProvinceAbbrev    CHAR(2) NOT NULL,
CONSTRAINT        pk_stateprovince PRIMARY KEY(StateProvinceID))
GO

ALTER    TABLE StateProvince
ADD        CONSTRAINT fk_countrytostateprovince    FOREIGN KEY (CountryID)
REFERENCES Country(CountryID)
GO

Please let me know if this helps.


Thanks, Hasham

Answer 10

Thanks, Hasham!  This script did the trick!

---

Scott Olander