Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

Using SQL Server 2008, why do I get the following error 'Explicit value must be specified for identity column in table 'tbl_CSL_CSLRpts' either when IDENTITY_INSERT is set to ON or when ?

I am using SQL Server 2008.

Why do I get the following error:

Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'tbl_CSL_CSLRpts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

when Creating the following table and inserting the following values?  

CREATE TABLE tbl_CSL_CSLRpts
( ReportID INT IDENTITY(1,1) NOT NULL,
  ReportName nvarchar(50) not null
);  

SET IDENTITY_INSERT tbl_CSL_CSLRpts ON

INSERT INTO tbl_CSL_CSLRpts
VALUES
('Summary for Stop Void Log'),
('Undeliverable Check Log'),
('Stop Payment Log'),
('Void Checks'),
('Manual Stop Items'),
('BOSS Stopped Checks'),
('Items Returned Through Reverse Positive Pay'),
('Payroll Report'),
('A/P Report');
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Since the INSERT statements do not include an explicit value for the IDENTITY column, so this error message is generated.
You may have a look here for sol: http://www.sql-server-helper.com/error-messages/msg-545.aspx
Hello Zimmer9,

Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'tbl_CSL_CSLRpts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Sometimes we have to provide the explicit identity to the table. In that case we can use below. The error is coming because you have not provided the columnName list. If we are providing the explicit value to the identity column then we must provide the column list.

Trial below-

--

SET IDENTITY_INSERT tbl_CSL_CSLRpts ON

INSERT INTO tbl_CSL_CSLRpts(ReportID,ReportName)
VALUES (10,'Pawan10'),(11,'Pawan11')
GO

SET IDENTITY_INSERT tbl_CSL_CSLRpts OFF

--

Open in new window


--

/*------------------------
SELECT * FROM tbl_CSL_CSLRpts
------------------------*/
ReportID    ReportName
----------- --------------------------------------------------
1           Summary for Stop Void Log
2           Undeliverable Check Log
3           Stop Payment Log
4           Void Checks
5           Manual Stop Items
6           BOSS Stopped Checks
7           Items Returned Through Reverse Positive Pay
8           Payroll Report
9           A/P Report
10          Pawan10
11          Pawan11

(11 row(s) affected)

--

Open in new window


Hope it helps !
Avatar of Vitor Montalvão
When you SET IDENTITY_INSERT tbl_CSL_CSLRpts ON it means that you want to explicitly provide the column value for an identity column but your INSERTs doesn't include that value. For that work you'll need to edit your INSERTs to something like:
INSERT INTO tbl_CSL_CSLRpts
 VALUES
 (1, 'Summary for Stop Void Log'),
 (2, 'Undeliverable Check Log'),
 (3, 'Stop Payment Log'),
 (4, 'Void Checks'),
 (5, 'Manual Stop Items'),
 (6, 'BOSS Stopped Checks'),
 (7, 'Items Returned Through Reverse Positive Pay'),
 (8, 'Payroll Report'),
 (9, 'A/P Report'); 

Open in new window

With that you need to be sure that those values explicitly added don't already exists in the table. Now, my question is, why do you want to turn off the IDENTITY feature in this case?
@Zimmer9,
Please check my first 2 comments where I have covered both the cases.

Case 1 - Where you have to pass the identity value explicitly to the table.
Case 2 - Where you don't need to pass the value to the explicit identity value.

Hope it helps

/Pawan
@Zimmer9,

I gave you the perfect and fully tested solutions <Both options> and provided lot of explanation that also before Vitor. You have to explain us why you have not selected my answer.

This kind of behavior is not good.
Avatar of zimmer9

ASKER

You are correct Pawan. How do I reset my answer? I was distracted. My machine was very low on memory and was virtually frozen. I spent 20 minutes deleting memory hogs and when I came back to this question, I had planned to give you credit but I was in the wrong location of the postings.