IDENTITY INSERT error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IDENTITY INSERT error

Author

Topic

genubath12
Starting Member

2 Posts

Posted - 2007-03-08 : 21:27:34

Hey all, I have been working with Northwind on SQL Server Express
Trying to learn Transact-SQL. I am stuck at this point from code in the instruction manual. I don't know if I have a permission set to something that I need or what but I get the following error that is outlined at the bottom of the code. Any help would be appreciated.

USE Northwind
GO

ALTER PROC spInsertDateValidatedOrder
@CustomerID nvarchar(5),
@EmployeeID int,
@OrderDate datetime = NULL,
@RequiredDate datetime = NULL,
@ShippedDate datetime = NULL,
@ShipVia int,
@Freight money,
@ShipName nvarchar(60) = NULL,
@ShipAddress nvarchar(40) = NULL,
@ShipCity nvarchar(15) = NULL,
@ShipRegion nvarchar(15) = NULL,
@ShipPostalCode nvarchar(10) = NULL,
@ShipCountry nvarchar(15) = NULL,
@OrderID int OUTPUT

AS

DECLARE @InsertedOrderDate smalldatetime

--Test to see if supplied date is over seven days old. If so
--replace with NULL value otherwise, truncate the time to be midnight.

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
BEGIN
SELECT @InsertedOrderDate = NULL
PRINT 'Invalid Order Date'
PRINT 'Supplied Order Date was greater than 7 days old'
PRINT 'The value has been reset to NULL'
END

ELSE

BEGIN
SELECT @InsertedOrderDate = CONVERT(datetime, (CONVERT(varchar,@OrderDate, 112)))
PRINT 'The Time of Day in Order Date was truncated'
END

--create the new record
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@OrderDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)

--Move the identity value from the newly inserted record into output variable.

SELECT @OrderID = @@IDENTITY

Errors received,
Msg 8101, Level 16, State 1, Procedure spInsertDateValidatedOrder, Line 46
An explicit value for the identity column in table 'Orders' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 21:43:20

You should exclude IDENTITY columns from the INSERT list. Which of the column in the ORDERS table is identity column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

genubath12
Starting Member

2 Posts

Posted - 2007-03-08 : 22:10:23

The IDENTITY Column should be being assigned to the orderID variable which isn't in the insert statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources