July 7, 2015 at 9:32 am
Hi all,
From Pages 342-345 of the book "Microsoft SQL Server 2012 BiBle. The Comprehensive, Tutorial Resource" written by Adam Jorgensen, et. al. (published by Wiley), I manuaklly copied the following code:
--Pages 342-345 of Microsoft SQL Server 2012 BIBLE, written by Adam Jorgensen, et. al.
-- to create Objects: Table [dbo].[Customer]/[Item]/[Orders]/[OrderDetail]/[ItemInfo]
-- copied 2 July 2015; Run 6 July 2015
USE SampleDB;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[Item]') AND type in (N'U'))
DROP TABLE [dbo].[Item]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[Orders]') AND type in (N'U'))
DROP TABLE [dbo].[Orders]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[OrderDetail]') AND type in (N'U'))
DROP TABLE [dbo].[OrderDetail]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[ItemInfo]') AND type in (N'U'))
DROP TABLE [dbo].[ItemInfo]
GO
-------------------------------------------------------------------#1 Table
/***** Object: Table [dbo].[Customer] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar] (50) NULL,
[Address] [nvarchar] (50) NULL,
[City] [nvarchar] (50) NULL,
[State] [nvarchar] (50) NULL,
[ZipCode] [nvarchar] (50) NULL,
[Phone] [nvarchar] (50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT [dbo].[Customer] ([CustomerID], [Name], [Address], [City], [State], [ZipCode], [Phone])
VALUES (1, N'Scott', N'555 Main St.', N'Palm Beach', N'FL', N'33333', N'555-555-5555')
INSERT [dbo].[Customer] ([CustomerID], [Name], [Address], [City], [State], [ZipCode], [Phone])
VALUES (2, N'Adam', N'111 Works St.', N'Jax', N'FL', N'34343', N'444-444-4444')
INSERT [dbo].[Customer] ([CustomerID], [Name], [Address], [City], [State], [ZipCode], [Phone])
VALUES (3, N'John', N'123 Pike Blvd.', N'Seattle', N'WA', N'9889', N'999-999-9999')
SET IDENTITY_INSERT [dbo].[Customer] OFF
-- #2 Table ------------------------------------------------------------------#2 Table
/****** Object: Table [dbo].[Item] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Item](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [nvarchar] (50) NULL,
[ItemDescription] [nvarchar] (50) NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Item] ON
INSERT [dbo].[Item] ([ItemID], [ItemNumber], [ItemDescription])
VALUES (1, N'V001', N'Verizon Windiows Phone 7')
INSERT [dbo].[Item] ([ItemID], [ItemNumber], [ItemDescription])
VALUES (2, N'A017', N'Alienware MX 18')
INSERT [dbo].[Item] ([ItemID], [ItemNumber], [ItemDescription])
VALUES (3, N'P003', N'Peters Pea Shooter 3000')
SET IDENTITY_INSERT [dbo].[Item] OFF
-- #3 Table ------------------------------------------------------------------#3 Table
/****** Object: Table [dbo].[Orders] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [Int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[OrderNumber] [nvarchar] (50) NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (1, 1, N'10001', '6/15/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (2, 2, N'10002', '6/16/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (3, 1, N'10003', '6/17/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (4, 2, N'10004', '6/18/2011')
SET INDENTITY_INSERT [dbo].[Orders] OFF
-- #4 Table -------------------------------------------------------------------#4 Table
/****** Object: Table [dbo].[OrderDetail] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[ItemID] [int] NULL,
[Quantity] [int] NULL,
[Price] [money] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[OrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[OrderDetail] ON
INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity], [Price])
VALUES (1, 1, 1, 1, 299.9900)
INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity], [Price])
VALUES (2, 2, 2, 1, 3399.9900)
INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity], [Price])
VALUES (3, 1, 1, 5, 1499.9500)
INSERT [dbo].[OrderDetail] ([OrderDetailID], [OrderID], [ItemID], [Quantity], [Price])
VALUES (4, 2, 3, 2, 3.9900)
SET INDENTITY_INSERT [dbo].[OrderDetail] OFF
-- #5 Table ----------------------------------------------------------------------#5 Table
/****** Object: Table [dbo].[ItemInfo] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemInfo](
[OrderID] [int] NOT NULL,
[ItemData] [xml] NULL
) ON [PRIMARY]
GO
=======================
I executed the above code in my Microsoft SQL Server 2012 Management Studio (SSMS2012). It ran paritally successfully - it created 4 dbo tables (Customer, Item, ItemInfo, OrderDetail), but, not the dbo. Orders table, and it gave me the following error Messages:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_Item' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 195, Level 15, State 7, Line 10
'INDENTITY_INSERT' is not a recognized SET option.
Msg 195, Level 15, State 7, Line 10
'INDENTITY_INSERT' is not a recognized SET option.
***********************************************************
I have no ideas (1) how the SET IDENTITY_INSERT [dbo].{Orders] OFF/ON works, (2) why the dbo.Orders table was not created, and (3) How to resolve the problem. Please kindly help, advise, and respond.
Thanks in advance,
Scott Chang
P. S. I found 2 mistakes and changed "INDENTITY" to "IDENTITY" in the last code statyement of creating #4 Table (dbo. Orders) and #5 Table (dbo.ItemInfo. I executed the revised code, and I got the following error Messages:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_Item' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "dbo.Orders" because it does not exist or you do not have permissions.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
=============================
I don't know (1) how I can delete the existing object 'PK_Item" that is already in the database (where is located?), and (2) how I can create dbo.Orders table in my EricZhang database successully. The dbo.Orders table is an important table for me to use in the next tasks of (1) Creating XML Schema Collection, and (2) Using PROCEDURE & FUNCTION to Do XML Parameters & Return Values for the dbo.Orders. Please kindly help and give me the instructions to solve this problem.
July 7, 2015 at 2:26 pm
Typonese is causing your issue (look for the bolding):
-- #3 Table ------------------------------------------------------------------#3 Table
/****** Object: Table [dbo].[Orders] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [Int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[OrderNumber] [nvarchar] (50) NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (1, 1, N'10001', '6/15/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (2, 2, N'10002', '6/16/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (3, 1, N'10003', '6/17/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (4, 2, N'10004', '6/18/2011')
SET INDENTITY_INSERT [dbo].[Orders] OFF
Correct the spelling of IDENTITY and the rest should work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 7, 2015 at 2:51 pm
From your post:
-- #3 Table ------------------------------------------------------------------#3 Table
/****** Object: Table [dbo].[Orders] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [Int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[OrderNumber] [nvarchar] (50) NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED -- <<< Look here, the constraint name is the same as the constraint on the table Item
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (1, 1, N'10001', '6/15/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (2, 2, N'10002', '6/16/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (3, 1, N'10003', '6/17/2011')
INSERT [dbo].[Orders] ([OrderID], [CustomerID], [OrderNumber], [Orderdate])
VALUES (4, 2, N'10004', '6/18/2011')
SET INDENTITY_INSERT [dbo].[Orders] OFF
July 7, 2015 at 7:08 pm
and one last thing - none of your DROP TABLE statements are firing because there's an extra space in front of the name in the EXISTS.
as in
IF EXISTS (SELECT OBJECT_ID(N'[dbo].[Customer]'),* FROM sys.objects WHERE object_id =
OBJECT_ID(N' [dbo].[Customer]') --<<<<<LOOK - should be N'[dbo].[Customer]'
AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 8, 2015 at 5:51 am
Hi Lynn, Thanks for your nice response.
I know the problem is in that code statement as you pointed out. But I don't know what I should do to correct this problem. Please kindly help, give me the specific instructions for correcting the problem. and respond again.
Thanks again,
Scott Chang
July 8, 2015 at 6:18 am
Hi Lynn, After I posted last thread, I looked at that code statement very closely and I found the mistake in that code staement (i. e. Changed "Item" to "Orders"). I executed my revised code and it worked nicely. Many Thanks to you, Scott Chang
========================================
Hi Matt, Thanks for your nice response. I corrected the mistakes you pointed out too. With your help and Lynn's help, I got my revised code worked for me. Many Thanks to you also, Scott Chang
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply