[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

I need someone elses eyes on this

Mike Labosh

3/18/2007 1:22:00 AM

CREATE TABLE dbo.Orders (
OrderID INT NOT NULL IDENTITY(1, 1)
CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED,
CustomerID INT NOT NULL
-- CONSTRAINT CustomersOrdersFK
-- REFERENCES Customers (CustomerID),
EmployeeID INT NOT NULL
-- CONSTRAINT EmployeesOrdersFK
-- REFERENCES Employees (EmployeeID),
ApproverID INT NOT NULL
-- CONSTRAINT ApproversOrdersFK
-- REFERENCES Employees (EmployeeID),
OrderDate DATETIME NULL,
Status SMALLINT NULL,
OrderTotal MONEY NULL
)
GO

SET IDENTITY_INSERT Orders ON
GO

BEGIN TRANSACTION
INSERT INTO Products
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(38, 1, 4, 4, '07-Feb-2002 23:41:07', 1, 500)
INSERT INTO Products
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(39, 1, 4, 4, '07-Feb-2002 23:41:08', 2, 500)
INSERT INTO Products
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(40, 1, 4, 4, '07-Feb-2002 23:51:09', 2, 500)
INSERT INTO Products
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(41, 1, 4, 4, '07-Feb-2002 23:51:09', 1, 500)
COMMIT TRANSACTION

SET IDENTITY_INSERT Orders OFF
GO

On each insert statement I get the following waring:

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'OrderID'.

As if I had not set the identity insert option. I am logged in as dbo. I
cannot see what I am doing wrong here. I even checked the example in BOL
for set identity_insert, and it looks like they are doing exactly what I am
doing. I bet I misspelled something or have an out of place comma, but I
can't nail it down.

I have tried it with the set statements inside the transaction, and I have
also tried it without the transaction, and gotten the same results, each
time.
--

Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
5 Answers

Plamen Ratchev

3/18/2007 2:19:00 AM

0

I see only two issues:

1. When you commented out the constraints, you took out the comma at the end
of each column definition. Just add it back.
2. Your insert into table Products, and you create table Orders.

If you fix those all works fine. Here is the working version:

CREATE TABLE dbo.Orders (
OrderID INT NOT NULL IDENTITY(1, 1)
CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED,
CustomerID INT NOT NULL,
-- CONSTRAINT CustomersOrdersFK
-- REFERENCES Customers (CustomerID),
EmployeeID INT NOT NULL,
-- CONSTRAINT EmployeesOrdersFK
-- REFERENCES Employees (EmployeeID),
ApproverID INT NOT NULL,
-- CONSTRAINT ApproversOrdersFK
-- REFERENCES Employees (EmployeeID),
OrderDate DATETIME NULL,
Status SMALLINT NULL,
OrderTotal MONEY NULL
)
GO

SET IDENTITY_INSERT Orders ON
GO

BEGIN TRANSACTION
INSERT INTO Orders
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(38, 1, 4, 4, '07-Feb-2002 23:41:07', 1, 500)
INSERT INTO Orders
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(39, 1, 4, 4, '07-Feb-2002 23:41:08', 2, 500)
INSERT INTO Orders
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(40, 1, 4, 4, '07-Feb-2002 23:51:09', 2, 500)
INSERT INTO Orders
(OrderID, CustomerID, EmployeeID, ApproverID,
OrderDate, Status, OrderTotal)
VALUES
(41, 1, 4, 4, '07-Feb-2002 23:51:09', 1, 500)
COMMIT TRANSACTION

SET IDENTITY_INSERT Orders OFF
GO

HTH,

Plamen Ratchev
http://www.SQL...

JohnnyC

3/18/2007 2:22:00 AM

0

Your create statement is for 'Orders'.
Your insert statements are for the 'Products' table.

Could this be it?

"Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
news:O1DHjvPaHHA.3520@TK2MSFTNGP04.phx.gbl...
> CREATE TABLE dbo.Orders (
> OrderID INT NOT NULL IDENTITY(1, 1)
> CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED,
> CustomerID INT NOT NULL
> -- CONSTRAINT CustomersOrdersFK
> -- REFERENCES Customers (CustomerID),
> EmployeeID INT NOT NULL
> -- CONSTRAINT EmployeesOrdersFK
> -- REFERENCES Employees (EmployeeID),
> ApproverID INT NOT NULL
> -- CONSTRAINT ApproversOrdersFK
> -- REFERENCES Employees (EmployeeID),
> OrderDate DATETIME NULL,
> Status SMALLINT NULL,
> OrderTotal MONEY NULL
> )
> GO
>
> SET IDENTITY_INSERT Orders ON
> GO
>
> BEGIN TRANSACTION
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (38, 1, 4, 4, '07-Feb-2002 23:41:07', 1, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (39, 1, 4, 4, '07-Feb-2002 23:41:08', 2, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (40, 1, 4, 4, '07-Feb-2002 23:51:09', 2, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (41, 1, 4, 4, '07-Feb-2002 23:51:09', 1, 500)
> COMMIT TRANSACTION
>
> SET IDENTITY_INSERT Orders OFF
> GO
>
> On each insert statement I get the following waring:
>
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'OrderID'.
>
> As if I had not set the identity insert option. I am logged in as dbo. I
> cannot see what I am doing wrong here. I even checked the example in BOL
> for set identity_insert, and it looks like they are doing exactly what I
> am
> doing. I bet I misspelled something or have an out of place comma, but I
> can't nail it down.
>
> I have tried it with the set statements inside the transaction, and I have
> also tried it without the transaction, and gotten the same results, each
> time.
> --
>
> Peace & happy computing,
>
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
>
> "Escriba coda ergo sum." -- vbSensei
>
>


masri999

3/18/2007 4:23:00 AM

0

On Mar 18, 6:22 am, "Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote:
> CREATE TABLE dbo.Orders (
> OrderID INT NOT NULL IDENTITY(1, 1)
> CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED,
> CustomerID INT NOT NULL
> -- CONSTRAINT CustomersOrdersFK
> -- REFERENCES Customers (CustomerID),
> EmployeeID INT NOT NULL
> -- CONSTRAINT EmployeesOrdersFK
> -- REFERENCES Employees (EmployeeID),
> ApproverID INT NOT NULL
> -- CONSTRAINT ApproversOrdersFK
> -- REFERENCES Employees (EmployeeID),
> OrderDate DATETIME NULL,
> Status SMALLINT NULL,
> OrderTotal MONEY NULL
> )
> GO
>
> SET IDENTITY_INSERT Orders ON
> GO
>
> BEGIN TRANSACTION
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (38, 1, 4, 4, '07-Feb-2002 23:41:07', 1, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (39, 1, 4, 4, '07-Feb-2002 23:41:08', 2, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (40, 1, 4, 4, '07-Feb-2002 23:51:09', 2, 500)
> INSERT INTO Products
> (OrderID, CustomerID, EmployeeID, ApproverID,
> OrderDate, Status, OrderTotal)
> VALUES
> (41, 1, 4, 4, '07-Feb-2002 23:51:09', 1, 500)
> COMMIT TRANSACTION
>
> SET IDENTITY_INSERT Orders OFF
> GO
>
> On each insert statement I get the following waring:
>
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'OrderID'.
>
> As if I had not set the identity insert option. I am logged in as dbo. I
> cannot see what I am doing wrong here. I even checked the example in BOL
> for set identity_insert, and it looks like they are doing exactly what I am
> doing. I bet I misspelled something or have an out of place comma, but I
> can't nail it down.
>
> I have tried it with the set statements inside the transaction, and I have
> also tried it without the transaction, and gotten the same results, each
> time.
> --
>
> Peace & happy computing,
>
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
>
> "Escriba coda ergo sum." -- vbSensei

Also , SET XACT_ABORT ON , to rollback if any error occurs

Mr Tea

3/18/2007 3:20:00 PM

0

oops..

Mr Tea

"Where we're going, we won't need eyes" - Dr Weir

"Jack" <j@jc.com> wrote in message
news:sn1Lh.47504$Cp1.45338@newsfe14.lga...
> Your create statement is for 'Orders'.
> Your insert statements are for the 'Products' table.
>
> Could this be it?
>
> "Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
> news:O1DHjvPaHHA.3520@TK2MSFTNGP04.phx.gbl...
>> CREATE TABLE dbo.Orders (
>> OrderID INT NOT NULL IDENTITY(1, 1)
>> CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED,
>> CustomerID INT NOT NULL
>> -- CONSTRAINT CustomersOrdersFK
>> -- REFERENCES Customers (CustomerID),
>> EmployeeID INT NOT NULL
>> -- CONSTRAINT EmployeesOrdersFK
>> -- REFERENCES Employees (EmployeeID),
>> ApproverID INT NOT NULL
>> -- CONSTRAINT ApproversOrdersFK
>> -- REFERENCES Employees (EmployeeID),
>> OrderDate DATETIME NULL,
>> Status SMALLINT NULL,
>> OrderTotal MONEY NULL
>> )
>> GO
>>
>> SET IDENTITY_INSERT Orders ON
>> GO
>>
>> BEGIN TRANSACTION
>> INSERT INTO Products
>> (OrderID, CustomerID, EmployeeID, ApproverID,
>> OrderDate, Status, OrderTotal)
>> VALUES
>> (38, 1, 4, 4, '07-Feb-2002 23:41:07', 1, 500)
>> INSERT INTO Products
>> (OrderID, CustomerID, EmployeeID, ApproverID,
>> OrderDate, Status, OrderTotal)
>> VALUES
>> (39, 1, 4, 4, '07-Feb-2002 23:41:08', 2, 500)
>> INSERT INTO Products
>> (OrderID, CustomerID, EmployeeID, ApproverID,
>> OrderDate, Status, OrderTotal)
>> VALUES
>> (40, 1, 4, 4, '07-Feb-2002 23:51:09', 2, 500)
>> INSERT INTO Products
>> (OrderID, CustomerID, EmployeeID, ApproverID,
>> OrderDate, Status, OrderTotal)
>> VALUES
>> (41, 1, 4, 4, '07-Feb-2002 23:51:09', 1, 500)
>> COMMIT TRANSACTION
>>
>> SET IDENTITY_INSERT Orders OFF
>> GO
>>
>> On each insert statement I get the following waring:
>>
>> Server: Msg 207, Level 16, State 1, Line 1
>> Invalid column name 'OrderID'.
>>
>> As if I had not set the identity insert option. I am logged in as dbo.
>> I
>> cannot see what I am doing wrong here. I even checked the example in BOL
>> for set identity_insert, and it looks like they are doing exactly what I
>> am
>> doing. I bet I misspelled something or have an out of place comma, but I
>> can't nail it down.
>>
>> I have tried it with the set statements inside the transaction, and I
>> have
>> also tried it without the transaction, and gotten the same results, each
>> time.
>> --
>>
>> Peace & happy computing,
>>
>> Mike Labosh, MCSD MCT
>> Owner, vbSensei.Com
>>
>> "Escriba coda ergo sum." -- vbSensei
>>
>>
>
>


Mike Labosh

3/19/2007 10:00:00 PM

0

> Your create statement is for 'Orders'.
> Your insert statements are for the 'Products' table.
>
> Could this be it?

I can't BELIEVE I did that! LOL!
--

Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei