Sample scripts

Question

I have the sample scripts for 2008 installed, sql server 2008 express installed, and the three sample AdventureWorks DBS (oltp, dw, lt).  I attempt to run the AlterSchemaFromDbo or the AlterSchemaToDbo and neither works due to errors such as
"Cannot find the object 'vAdditionalContactInfo', because it does not exist or you do not have permission."

I don't see the views mentioned in the DB.  The instructions for these scripts do not indicate I need to set anything else up.  I simply restored the db's from the samples directory using provided restore script.

The one change I had to make was to renamed AdventureWorks2008 database to AdventureWorks since the script did not reference it with the 2008.

Answer 1

The view is the restored copy should be Person.vAdditionalContactInfo.  This is the script for the view:

 

Code Snippet

USE [AdventureWorks]

GO

/****** Object: View [Person].[vAdditionalContactInfo] Script Date: 09/12/2008 22:20:59 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [Person].[vAdditionalContactInfo]

AS

SELECT

[ContactID]

,[FirstName]

,[MiddleName]

,[LastName]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:telephoneNumber)[1]/act:number', 'nvarchar(50)') AS [TelephoneNumber]

,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:telephoneNumber/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [TelephoneSpecialInstructions]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') AS [Street]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:City)[1]', 'nvarchar(50)') AS [City]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') AS [StateProvince]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') AS [PostalCode]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)') AS [CountryRegion]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:homePostalAddress/act:SpecialInstructions/text())[1]', 'nvarchar(max)') AS [HomeAddressSpecialInstructions]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:eMailAddress)[1]', 'nvarchar(128)') AS [EMailAddress]

,LTRIM(RTRIM([ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:SpecialInstructions/text())[1]', 'nvarchar(max)'))) AS [EMailSpecialInstructions]

,[ContactInfo].ref.value(N'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

(act:eMail/act:SpecialInstructions/act:telephoneNumber/act:number)[1]', 'nvarchar(50)') AS [EMailTelephoneNumber]

,[rowguid]

,[ModifiedDate]

FROM [Person].[Contact]

OUTER APPLY [AdditionalContactInfo].nodes(

'declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";

/ci:AdditionalContactInfo') AS ContactInfo(ref)

WHERE [AdditionalContactInfo] IS NOT NULL;

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'VIEW',@level1name=N'vAdditionalContactInfo'

GO

 

 

 

 

Answer 2

Thanks Jonathan.  I should have elaborated more.  That was just one example of the errors, which are all similar in that they are related to something missing.

1. I have restored from the backup the AdventureWorks2008 database to verify that I have not accidentally muddied anything up.

2. I ran your script to create the view, and receive these errors:
Msg 208, Level 16, State 1, Procedure vAdditionalContactInfo, Line 88
Invalid object name 'Person.Contact'.
Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added. Property 'MS_Description' already exists for 'Person.vAdditionalContactInfo'.

Such is life that something is always missing   MS SQL Server should report "I feel Person.Contact is missing in my life."  j/k

3.  Perhaps I have the wrong downloads as there are a huge number of different installers for the samples available, and maybe I downloaded the wrong thing.  This is what I installed, then restored these three DBs.  SQL2008.AdventureWorks_OLTP_DB_v2008.x86.msi
SQL2008.AdventureWorks_LT_DB_v2008.x86.msi
SQL2008.AdventureWorks_DW_BI_v2008.x86.msi
SQL2008.All_Product_Samples_Without_DBs.x86.msi

Is the fact that the restore script creates the DB as AdventureWorks2008, but the sample script references it as  AdventureWorks (no 2008 on the end) indicative of some misunderstanding on my part about what DB these sample scripts go with?

This is the first time I've ever taken the time to setup one of MS's sample databases.

One thing is I couldn't check the "Restore DB" option in the installer because it would complain saying full text search was not enabled, even though I checked everywhere I knew to check and it seemed it was enabled.  So I left that install option unchecked and restored them myself using the restore scripts provided in the samples directory.  Is it possible that the restore will not create things such as views if they require a feature that is not enabled, and that perhaps would explain why these things do not exist?


Here are the errors I get from running the AlterSchemaToDbo.sql, which is the only thing other than your script that I attempted to run since I restored the DB this last time:

Code Snippet

Msg 15151, Level 16, State 1, Line 5
Cannot find the object 'vIndividualDemographics', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 13
Cannot find the object 'vVendor', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 5
Cannot find the object 'Contact', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 6
Cannot find the object 'ContactCreditCard', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 15
Cannot find the object 'CustomerAddress', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 19
Cannot find the object 'EmployeeAddress', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 23
Cannot find the object 'Individual', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 60
Cannot find the object 'StoreContact', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 65
Cannot find the object 'VendorAddress', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 66
Cannot find the object 'VendorContact', because it does not exist or you do not have permission.

This is the script:
/*============================================================================
  File:     AlterSchemaToDBO.sql

  Summary:  Changes the schema to dbo for each table in the AdventureWorks
            sample database.

  Date:     April 01, 2005
------------------------------------------------------------------------------
  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

  This source code is intended only as a supplement to Microsoft
  Development Tools and/or on-line documentation.  See these other
  materials for detailed information regarding Microsoft code samples.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Change schemas to dbo
USE AdventureWorks;
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

-- Must drop the schema bound views first
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[Production].[vProductAndDescription]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [Production].[vProductAndDescription];
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[Person].[vStateProvinceCountryRegion]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [Person].[vStateProvinceCountryRegion];
GO

ALTER SCHEMA [dbo] TRANSFER [Person].[vAdditionalContactInfo];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vEmployee];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vIndividualCustomer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vIndividualDemographics];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidate];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidateEducation];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[vJobCandidateEmployment];
ALTER SCHEMA [dbo] TRANSFER [Production].[vProductModelCatalogDescription];
ALTER SCHEMA [dbo] TRANSFER [Production].[vProductModelInstructions];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vSalesPerson];
ALTER SCHEMA [dbo] TRANSFER [Sales].[vStoreWithDemographics];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[vVendor];
GO

ALTER SCHEMA [dbo] TRANSFER [Person].[Address];
ALTER SCHEMA [dbo] TRANSFER [Person].[AddressType];
ALTER SCHEMA [dbo] TRANSFER [Production].[BillOfMaterials];
ALTER SCHEMA [dbo] TRANSFER [Person].[Contact];
ALTER SCHEMA [dbo] TRANSFER [Sales].[ContactCreditCard];
ALTER SCHEMA [dbo] TRANSFER [Person].[ContactType];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CountryRegionCurrency];
ALTER SCHEMA [dbo] TRANSFER [Person].[CountryRegion];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CreditCard];
ALTER SCHEMA [dbo] TRANSFER [Production].[Culture];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Currency];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CurrencyRate];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Customer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[CustomerAddress];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Department];
ALTER SCHEMA [dbo] TRANSFER [Production].[Document];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Employee];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeeAddress];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeeDepartmentHistory];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[EmployeePayHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[Illustration];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Individual];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[JobCandidate];
ALTER SCHEMA [dbo] TRANSFER [Production].[Location];
ALTER SCHEMA [dbo] TRANSFER [Production].[Product];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductCategory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductCostHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductDescription];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductDocument];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductInventory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductListPriceHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModel];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModelIllustration];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductModelProductDescriptionCulture];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductPhoto];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductProductPhoto];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductReview];
ALTER SCHEMA [dbo] TRANSFER [Production].[ProductSubcategory];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[ProductVendor];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[PurchaseOrderDetail];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[PurchaseOrderHeader];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderDetail];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderHeader];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesOrderHeaderSalesReason];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesPerson];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesPersonQuotaHistory];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesReason];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTaxRate];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTerritory];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SalesTerritoryHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[ScrapReason];
ALTER SCHEMA [dbo] TRANSFER [HumanResources].[Shift];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[ShipMethod];
ALTER SCHEMA [dbo] TRANSFER [Sales].[ShoppingCartItem];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SpecialOffer];
ALTER SCHEMA [dbo] TRANSFER [Sales].[SpecialOfferProduct];
ALTER SCHEMA [dbo] TRANSFER [Person].[StateProvince];
ALTER SCHEMA [dbo] TRANSFER [Sales].[Store];
ALTER SCHEMA [dbo] TRANSFER [Sales].[StoreContact];
ALTER SCHEMA [dbo] TRANSFER [Production].[TransactionHistory];
ALTER SCHEMA [dbo] TRANSFER [Production].[TransactionHistoryArchive];
ALTER SCHEMA [dbo] TRANSFER [Production].[UnitMeasure];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[Vendor];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[VendorAddress];
ALTER SCHEMA [dbo] TRANSFER [Purchasing].[VendorContact];
ALTER SCHEMA [dbo] TRANSFER [Production].[WorkOrder];
ALTER SCHEMA [dbo] TRANSFER [Production].[WorkOrderRouting];
GO


-- Recreate the indexed views

CREATE VIEW [dbo].[vProductAndDescription]
WITH SCHEMABINDING
AS
-- View (indexed or standard) to display products and product descriptions by language.
SELECT
    p.[ProductID]
    ,p.[Name]
    ,pm.[Name] AS [ProductModel]
    ,pmx.[CultureID]
    ,pd.[Description]
FROM [dbo].[Product] p
    INNER JOIN [dbo].[ProductModel] pm
    ON p.[ProductModelID] = pm.[ProductModelID]
    INNER JOIN [dbo].[ProductModelProductDescriptionCulture] pmx
    ON pm.[ProductModelID] = pmx.[ProductModelID]
    INNER JOIN [dbo].[ProductDescription] pd
    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription] ON [dbo].[vProductAndDescription]([CultureID], [ProductID]);
GO

CREATE VIEW [dbo].[vStateProvinceCountryRegion]
WITH SCHEMABINDING
AS
SELECT
    sp.[StateProvinceID]
    ,sp.[StateProvinceCode]
    ,sp.[IsOnlyStateProvinceFlag]
    ,sp.[Name] AS [StateProvinceName]
    ,sp.[TerritoryID]
    ,cr.[CountryRegionCode]
    ,cr.[Name] AS [CountryRegionName]
FROM [dbo].[StateProvince] sp
    INNER JOIN [dbo].[CountryRegion] cr
    ON sp.[CountryRegionCode] = cr.[CountryRegionCode];
GO

-- Index the view
CREATE UNIQUE CLUSTERED INDEX [IX_vStateProvinceCountryRegion] ON [dbo].[vStateProvinceCountryRegion]([StateProvinceID], [CountryRegionCode]);
GO

SET ANSI_NULLS OFF;
GO
SET QUOTED_IDENTIFIER OFF;
GO

USE master;
GO

It would seem the scripts are comparatively small to the DB, and would compress quite alot anyways.  I might suggest that they be in the same install as the DB for which they were created.  I have a feeling either I have gotten the wrong version of an installer or through some development/release process the scripts and the DB are some how not married together.

Answer 3

The one change I had to make was to renamed AdventureWorks2008 database to AdventureWorks since the script did not reference it with the 2008.

You cannot do that. AdventureWorks and AdventureWorks2008 are quite different. It appears that the script is SQL Server 2005 script.

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

Answer 4

>Msg 208, Level 16, State 1, Procedure vAdditionalContactInfo, Line 88
>Invalid object name 'Person.Contact'.

Again, it appears that you are running SQL Server 2005 script against AdventureWorks2008 database. That table has changed:

AdventureWorks --> Person.Contact (SQL Server 2005)

AdventureWorks2008--> Person.Person (SQL Server 2008)

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM