Adventureworks Create Table

[MichaelRed]

I am trying to get through a adventure works tutorial from MS. Alas I get part way through chapter 2 and run into an issue. The script fails attempting to create a table Statement. I include the entire code, as I am quite unsure of the root cause see the comment line highlighted in RED.

Also, not that there exists a previous create table statement which does not produce an error (see highlghted comment in GREEN

/*
	Programming SQL Server 2005 
	Chapter 2
	Exploring the T-SQL Enhancements in
	SQL Server 2005 
	Samples By: Stephen Forte (stephenforte@hotmail.com)
*/

/*
	Common Table Expression

	--CTE 1: Simple Common Table Expression
*/

USE AdventureWorks
GO

With AllMRContacts
As
(Select * From Person.Contact Where Title = 'Mr.')

Select lastName + ', ' + FirstName as Contact
From AllMRContacts
Order By LastName

/*
	--CTE 2: Simple Join
*/
USE AdventureWorks
GO
With OrderCountCTE(SalesPersonID, OrderCount)
As
(
	select SalesPersonID, Count(*)
	from  Sales.SalesOrderHeader
	where salespersonID is not null
	group by SalesPersonID 
)
Select sp.SalesPersonID, oc.OrderCount, sp.SalesYTD
From Sales.SalesPerson sp 
inner join OrderCountCTE oc on oc.SalesPersonID=sp.SalesPersonID
Order By oc.OrderCount

/*
	--CTE-Recursive Query

	--kill the table if it exists already
*/
	Drop table Employee_Tree
/*
	--create a table with tree data
	--ReportsTo is a "domestic key" back to Employee_id
*/

create table Employee_Tree (Employee_NM nvarchar(50), 
							Employee_ID int Primary Key, 
							ReportsTo int);  --[COLOR=green][b]<--No error here[/b][/color]
/*
	--insert some data, build a reporting tree
*/
insert into employee_tree values('Richard', 1, null)
insert into employee_tree values('Stephen', 2, 1)
insert into employee_tree values('Clemens', 3, 2)
insert into employee_tree values('Malek', 4, 2)
insert into employee_tree values('Goksin', 5, 4)
insert into employee_tree values('Kimberly', 6, 1)
insert into employee_tree values('Ramesh', 7, 5);

/*
	--Select * from Employee_Tree
	--Recursive Query

*/

WITH SimpleRecursive(Employee_NM, Employee_ID, ReportsTO) 
	AS
(SELECT Employee_NM, Employee_ID, ReportsTO
  FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO 
 FROM Employee_Tree  P  INNER JOIN
 SimpleRecursive A ON A.Employee_ID = P.ReportsTO
)
SELECT sr.Employee_NM as Employee, et.employee_nm as Boss
FROM SimpleRecursive sr inner join Employee_Tree et on sr.reportsto=et.employee_id;

/*
	--Recursive Query with MAXRECURSION
*/

WITH SimpleRecursive(Employee_NM, Employee_ID, ReportsTO) 
	AS
(SELECT Employee_NM, Employee_ID, ReportsTO
  FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO 
 FROM Employee_Tree  P  INNER JOIN
 SimpleRecursive A ON A.Employee_ID = P.ReportsTO
)
SELECT sr.Employee_NM as Employee, et.employee_nm as Boss
FROM SimpleRecursive sr inner join Employee_Tree et
on sr.reportsto=et.employee_id
OPTION(MAXRECURSION 2);

/*
	--Recursive Query uses a generated column SubLevel instead of MAXRECURSION
*/

WITH SimpleRecursive(Employee_NM, Employee_ID, ReportsTO, SubLevel)
	AS
(SELECT Employee_NM, Employee_ID, ReportsTO, 0
  FROM Employee_Tree WHERE Employee_ID = 2
UNION ALL
SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO, SubLevel + 1
 FROM Employee_Tree  P  INNER JOIN
 SimpleRecursive A ON A.Employee_ID = P.ReportsTO
)
SELECT sr.Employee_NM as Employee, et.employee_nm as Boss
FROM SimpleRecursive sr inner join Employee_Tree et
on sr.reportsto=et.employee_id
where SubLevel <=2;

/*
	--CTE Finding Dupes
	-- Finding and removing duplicates with a CTE
*/

/*
	-- DELETE Products?  
*/

CREATE TABLE Products 
  (Product_ID int NOT NULL,
  Product_Name varchar (25),
  Price money NULL,
CONSTRAINT PK_Products PRIMARY KEY NONCLUSTERED (Product_ID)
) -- [COLOR=red][b]<--ERROR HERE[/b][/color]
GO
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (1, 'Widgets', 25)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (2, 'Gadgets', 50)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (3, 'Thingies', 75)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (4, 'Whoozits', 90)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (5, 'Whatzits', 5)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (6, 'Gizmos', 15)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (7, 'Widgets', 24)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (8, 'Gizmos', 36)
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES (9, 'Gizmos', 36)
Go

/*
	--without CTE
*/

SELECT * FROM Products_T WHERE Product_ID NOT IN
(SELECT MIN(Product_ID) FROM Products_T AS P
 WHERE Products_T.Product_Name = P.Product_Name);

/*
--Find the Dupes with a CTE
*/
WITH CTEMinProductRecords AS (
 SELECT MIN(Product_ID) AS Product_ID, Product_Name
 FROM Products
 GROUP BY Product_Name
 HAVING COUNT(*) > 1)
SELECT * 
FROM Products JOIN CTEMinProductRecords ON
 Products.Product_Name = CTEMinProductRecords.Product_Name
 AND Products.Product_ID > CTEMinProductRecords.Product_ID 


/*
	Apply
	--table returning function
*/

CREATE FUNCTION fn_getcustomerorders(@CustomerID int, @TopRecords bigint)
RETURNS TABLE
 As Return
  SELECT TOP (@TopRecords) *
  FROM Sales.SalesOrderHeader
  WHERE CustomerID = @CustomerID
  ORDER BY OrderDate DESC
 

--use cross apply (a join operator)
SELECT * FROM Sales.Customer cust
CROSS APPLY fn_getcustomerorders(cust.CustomerID, 647)
Select * from Sales.SalesOrderHeader
/*
Pivot
*/
USE AdventureWorks
GO

/*
	--pivot the salesorderheader data by summed years aggergates
*/

SELECT CustomerID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
FROM 
(
SELECT CustomerID, datepart(yyyy, OrderDate) as OrderYear, TotalDue
From Sales.SalesOrderHeader
) piv
PIVOT
(
Sum (TotalDue)
For OrderYear IN
([2001], [2002], [2003], [2004])
) As chld
Order by CustomerID


/*
	--Pivot Example 2: Creating a Dynamic Pivot
	--First the Static Pivot
*/

USE AdventureWorks
GO

SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
      FROM Sales.SalesOrderHeader) as Header
PIVOT(SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004])) as Piv


/*
	--Dynamic Pivot
*/

DECLARE @tblOrderDate AS TABLE(y int NOT NULL PRIMARY KEY)
INSERT INTO @tblOrderDate SELECT DISTINCT YEAR(OrderDate) FROM Sales.SalesOrderHeader

/*
	-- Construct the column list for the IN clause
	-- e.g., [2002],[2003],[2004]
*/
DECLARE @cols AS nvarchar(MAX), @years AS int
SET @years = (SELECT MIN(y) FROM @tblOrderDate)
SET @cols = N''
WHILE @years IS NOT NULL
BEGIN
  SET @cols = @cols + N',['+CAST(@years AS nvarchar(10))+N']'
  SET @years = (SELECT MIN(y) FROM @tblOrderDate WHERE y > @years)
END
SET @cols = SUBSTRING(@cols, 2, LEN(@cols))

/*
	-- Construct the full T-SQL statement and execute it dynamically.
*/

DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
      FROM Sales.SalesOrderHeader) as Header
  PIVOT(SUM(TotalDue) FOR orderyear IN(' + @cols + N')) AS Piv'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

/*
	--Dynamic Pivot with CTE and "SELECT @cols = ..." syntact
	-- Construct the column list for the IN clause
	-- e.g., [2002],[2003],[2004]
*/

DECLARE @cols AS nvarchar(MAX)
;

WITH YearsCTE
AS
(SELECT DISTINCT YEAR(OrderDate) as [Year] FROM Sales.SalesOrderHeader)
SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([YEAR] AS nvarchar(10)) + ']'
FROM YearsCTE
ORDER BY [YEAR]

/*
	-- Construct the full T-SQL statement and execute it dynamically.
*/

DECLARE @sql AS nvarchar(MAX)
SET @sql = N'SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
      FROM Sales.SalesOrderHeader) as Header
  PIVOT(SUM(TotalDue) FOR orderyear IN(' + @cols + N')) AS Piv'
PRINT @sql -- for debugging
EXEC sp_executesql @sql

/*
Top Enhancements
*/
USE AdventureWorks
GO
Create Procedure usp_SEL_ReturnTopOrders
@NumberofRows bigint
As
Select TOP (@NumberofRows) SalesOrderID
From Sales.SalesOrderHeader
Order By SalesOrderID

/*
	--call it
*/
exec usp_SEL_ReturnTopOrders @NumberofRows=100

/*
	--using an expression
*/

Select TOP (Select Count(*) from Sales.SalesPerson) 
SalesOrderID, RevisionNumber, OrderDate
From Sales.SalesOrderHeader
Order By SalesOrderID

/*
	--using a percent
*/

DECLARE @NumberOfRows AS FLOAT
SET @NumberOfRows = 10

SELECT TOP (@NumberOfRows) PERCENT *
FROM  Sales.SalesOrderHeader
ORDER BY OrderDate


/*
Ranking and Windowing Functions 
--Ranking 1: Row_Number()
*/

USE AdventureWorks
GO

/*
	--Row_Number using a unique value
*/

Select SalesOrderID, CustomerID, 
	Row_Number() Over (Order By SalesOrderID) as RowNumber
From Sales.SalesOrderHeader

/*
	--Order By CustomerID
*/

/*
	--use a common table expression if you want
	--to filter by one of the rows that contain a
	--ranking function since ranking functions
	--are not allowed in where or having clauses
/*

With NumberRows
As
(
Select SalesOrderID, CustomerID, 
	Row_Number() Over (Order By SalesOrderID) as RowNumber
From Sales.SalesOrderHeader
)

Select * from NumberRows
where RowNumber between 100 and 200

/*
	--Row_Number using a unique value
	--different order by will cause the row numbers
	--to be wrong, this is perfectly legal syntax,
	--however it will produce an incorrect output
	--see the Note in Chapter 02
*/

Select SalesOrderID, CustomerID, 
	Row_Number() Over (Order By SalesOrderID) as RowNumber
From Sales.SalesOrderHeader
Order By CustomerID --Different ORDER BY than in Row_NUMBER

/*
	--Row_Number using a non-unique value
*/

Select SalesOrderID, CustomerID, 
	Row_Number() Over (Order By CustomerID ) as RowNumber
From Sales.SalesOrderHeader
Order By CustomerID 

/*
	--use two in your Over() statement
	--notice that SalesOrderID 43860
	--has changed its rank based on the
	--over statement
*/

Select SalesOrderID, CustomerID, 
	Row_Number() Over (Order By CustomerID ASC, SalesOrderID DESC) as RowNumber
From Sales.SalesOrderHeader
Order By CustomerID ASC, SalesOrderID DESC

/*
	--rank by totaldue, summed
	--need a CTE to do a sum
	--so this example will have a
	--customerID summed with all of
	--their orders
*/

With CustomerSum
As
(
Select CustomerID,sum(totaldue) as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)  
/*
	--this appends a row_number to the end of the resultset
*/

Select *, 
	Row_Number() Over (Order By totalamt Desc) as RowNumber
From CustomerSum

/*
	--Ranking 2: RANK()
	--will rank items according to your
	--over statement like in the olympics
	--gold, silver, bronse with ties skipping
	--the next n items where n is the number
	--of ties
*/

Select SalesOrderID, CustomerID, 
	RANK() Over (Order By CustomerID) as Rank
From Sales.SalesOrderHeader
Order By CustomerID

/*
	--rank by totaldue, summed
	--need a CTE to do a sum
	--so this example will have a
	--customerID summed with all of
	--their orders
*/

With CustomerSum
As
(
Select CustomerID,sum(totaldue) as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)
Select *, 
	Rank() Over (Order By totalamt Desc) as Rank
From CustomerSum

/*
	--run this and rerun the above query
*/

update Sales.SalesOrderDetail
set orderqty=13 --3 is the orig amt
/*
	--select * from Sales.SalesOrderDetail
*/

where salesorderid=44086 and SalesOrderDetailID=1542


/*
	--rank by totaldue, summed and rounded (nearest 100)
	--need a CTE to do a sum & rounding
	--so this example will have a
	--customerID summed with all of
	--their orders
*/

With CustomerSum
As
(
Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)
Select *, 
	Rank() Over (Order By totalamt Desc) as Rank
From CustomerSum
/*
	--this example is in progress
	--rank the salespersons
*/

With SalesPersonSum
As
(
Select SalesPersonID, Sum(TotalDue) as TotalSales
From Sales.SalesOrderHeader
Where datepart("yyyy", OrderDate)=2004
Group By SalesPersonID
)
Select SalesPersonID, TotalSales,
	Rank() Over (Order by totalsales DESC) as SalesRank
From SalesPersonSum

/*
	--Ranking 3: DENSE_RANK()
*/

Select SalesOrderID, CustomerID, 
	DENSE_RANK() Over (Order By CustomerID) as DenseRank
From Sales.SalesOrderHeader
Order By CustomerID

/*
	--rank by totaldue, summed and rounded
	--need a CTE to do a sum & rounding
	--so this example will have a
	--customerID summed with all of
	--their orders
*/

use adventureworks
go
With CustomerSum
As
(
Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)
Select *, 
	Rank() Over (Order By totalamt Desc) as Rank,
	Dense_Rank() Over (Order By totalamt Desc) as DenseRank
From CustomerSum

/*
	--Ranking 4: NTILE(n)
*/

Select SalesOrderID, CustomerID, 
	NTILE(10000) Over (Order By CustomerID) as NTile
From Sales.SalesOrderHeader
Order By CustomerID

/*
	--rank by totaldue, summed
	--need a CTE to do a sum
	--so this example will have a
	--customerID summed with all of
	--their orders
*/

With CustomerSum
As
(
Select CustomerID,sum(totaldue) as totalamt
From Sales.SalesOrderHeader
Group by CustomerID
)
Select *, 
	Rank() Over (Order By totalamt Desc) as Rank,
	NTILE(100) Over (Order By totalamt ASC) as Percentile
From CustomerSum
Order By totalamt DESC


/*
	--Ranking All
*/

Select SalesOrderID as OrderID, CustomerID,
	Row_Number() Over (Order By CustomerID) as RowNum,
	RANK() Over (Order By CustomerID) as Rank,
	DENSE_RANK() Over (Order By CustomerID) as DRank, 
	NTILE(10000) Over (Order By CustomerID) as NTile
From Sales.SalesOrderHeader
Order By CustomerID

/*
	--Ranking 5: Partition By
*/

Select SalesOrderID, SalesPersonID, OrderDate,
Row_NUMBER() Over (Partition By SalesPersonID Order By OrderDate) as OrderRank
From Sales.SalesOrderHeader
Where SalesPersonID is not null and SalespersonID in (279, 287)

/*
	--Partition (window) by territory
*/

With CTETerritory
As
(
Select cr.Name as CountryName, CustomerID, Sum(TotalDue) As TotalAmt
From Sales.SalesOrderHeader soh 
	inner join Sales.SalesTerritory ter
		on soh.TerritoryID=ter.TerritoryID
	inner join Person.CountryRegion cr 
		on cr.CountryRegionCode=ter.CountryRegionCode
Group By cr.Name, CustomerID
)
Select *, Rank() Over (Partition By CountryName Order By TotalAmt, CustomerID DESC) As rank
,Avg (TotalAmt) OVER(Partition By CountryName) as average
From CTETerritory

/*
	--Over with MAX, MIN, AVG Aggregate
	-- there is one oldest/youngest employee age for  
	--each job function and an average
*/

select *,
  RANK() OVER(PARTITION BY Title ORDER BY age) as [rank],
  MAX(age) OVER(PARTITION BY Title) as [oldest age in job title],
  Min(age) OVER(PARTITION BY Title) as [youngest age in job title],
  Avg (age) OVER(PARTITION BY Title) as [average age in job title]
from
(
 select EmployeeID, Title, 
   datediff(yy,birthdate,getdate()) as age
 from HumanResources.Employee
) as ep	

/*
	--Over with MAX Aggregate
	--That sums the largest order sum for a year
	--select * from Sales.SalesOrderHeader
	--Pull in the total amount to get the order
	--and the max amout of the order for any
	--given year
*/

Select CustomerID, OrderYear, TotalAmt, 
  RANK() OVER(PARTITION BY CustomerID ORDER BY totalamt) as [custrankbyamt],
  AVG(totalamt) OVER(PARTITION BY customerid) as [avg order sum],
  MAX(totalamt) OVER(PARTITION BY customerid) as [largest order sum]
From
(
Select CustomerID, datepart("yyyy", OrderDate) as OrderYear, 
	sum(totaldue) TotalAMt
From Sales.SalesOrderHeader
Group by CustomerID, datepart("yyyy", OrderDate)
) as detaildata
Order By CustomerID


/*
DDL Triggers
*/
/*
AU: This code differs from the SQL code in your sample .sql file, and fails because 
CREATE TRIGGER is not the first SQL statement in the batch.  The sample code fails as 
well because ALTER TRIGGER Is not the first SQL statement in the batch.
*/

/*
	--create a log table
*/

Create Table tblDDLTriggerLog (LogInfo xml)

/*
	--create a dummy table to delete later on
*/

create table todelete (id int primary key)

/*
	--add some dummy data in just for fun
*/

insert into todelete values (1)
/*
	--create a trigger that will disallow any table
	--drops and log the event data into our log table
*/

alter TRIGGER stopdropanytable ON DATABASE AFTER DROP_TABLE
AS
ROLLBACK
/*
	-- For results window!
/*

PRINT 'DROP TABLE attempt in database ' + DB_NAME() + '!!!'
Insert into tblDDLTriggerLog Values(eventdata())

/*
	--now see this trigger in action
*/

drop table todelete
select * from tblDDLTriggerLog
select * from todelete

/*
--clean up
/*

alter trigger stopdropanytable ON DATABASE AFTER DROP_TABLE
AS

/*
	--ROLLBACK
	-- For results window!
*/

PRINT 'DROP TABLE attempt in database ' + DB_NAME() + '.'

drop table todelete
drop table tblDDLTriggerLog
drop trigger stopdropanytable
*/

/*
	--Error Handle
	-- Part1
/*

BEGIN TRY
	Select 5/0 
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

/*
	--Part2:
	-- Compile error doesn't get caught
*/

BEGIN TRY
	Select * * From XYX
END TRY
BEGIN CATCH
	print 'Error'
END CATCH
GO

/*
	-- Statement level recompilation doesn't get caught
	-- Example - nonexistent object
*/

BEGIN TRY
	Select * From XYX
END TRY
BEGIN CATCH
	print 'Error'
END CATCH
GO

/*
	-- These errors get caught when they occur
	-- below the current level of execution (dynamic SQL or a SP)
	-- Dynamic SQL Example
*/

BEGIN TRY
	exec sp_executesql 'Select * * From XYX'
END TRY
BEGIN CATCH
	print 'Error'
END CATCH
GO

/*
	-- Stored Procedure Example
*/

CREATE PROCEDURE MyErrorProc
AS
	Select * From XYX
GO

BEGIN TRY
	exec MyErrorProc
END TRY
BEGIN CATCH
	print 'Error'
END CATCH
GO


/*
	XML
	--XML 'RAW' SQL Server 2000 Mode
	--Simple XML Raw v XML Auto (SQL Server 2000)
	--XML Raw goes to 1 row, Auto to more rows per table
*/

Select Customer.CustomerID, OrderDetail.SalesOrderID,OrderDetail.OrderDate
From Sales.Customer Customer
inner join Sales.SalesOrderHeader OrderDetail on 
	OrderDetail.customerid=Customer.customerid
where Customer.CustomerID<20
Order by Customer.CustomerID
For XML AUTO --1 row per child record (table in your join)

/*
	--FOR XML RAW --1 row per record
*/

Any previous users solve this?



MichaelRed

 

[bborissov]

And the error is?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MichaelRed]

Currently, the first error is

invalid object name 'Products'


Thanks for your interest



MichaelRed