November 18, 2015 at 12:58 am
Hello all.
I have a procedure inserts into a table which have a column with identity property,but it returns below error:
An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.
How can I work out a solution for this?
thank you
November 18, 2015 at 1:22 am
Without seeing any code for the stored procedure, or DDL for the table I am assuming that the table has a column that has been created with the IDENTITY parameter. This column will therefore have a value inserted into it for each row, generated by SQL Server.
Any insertion into this table would normally not mention this column, because the IDENTITY usage generates a value automatically. If you do need to insert a specific value then this behaviour is overridden by using the SET IDENTITY INSERT <table name> ON/OFF.
The stored procedure probably attempts to insert a value into this column when it attempts to insert a row. Either remove this column from the insert or remove the IDENTITY usage and let the stored procedure assign the value.
Usually, you need to use SET IDENTITY INSERT when correcting identity values in table as part of some corrective action. It shouldn't normally be coded into a stored procedure.
November 18, 2015 at 1:29 am
My procedure is:
create procedure SyncUsersAndRoles (@Type int)
as
BEGIN
declare @DepartmentID int,@OrganizationRoleID int
SELECT @DepartmentID=[Department_ID] FROM [Departments] where [IsReference]=1
select Top 1 @OrganizationRoleID=OrganizationRole_ID from OrganizationRoles
if @Type=1----SyncUsers
Begin
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT INTO [dbo].[Users]
SELECT [Id] as [User_ID]
,[FirstName] as [FirstName]
,[LastName] as [LastName]
,[UserName] as [UserName]
,[Password] as [Password]
,null as [NewPassword]
,[Enabled] as [IsActive]
,[IsOnline] as [IsOnline]
,0 as [IsConfirm]
,[LastLoginTime] as [LastLoginTime]
,(case [Sex] when '???' then 1 when '??' then 0 end)as [Gender]
,0 as [MarriedStatus]
,[BirthDate] [BirthDate]
,[BirthState] as [CountryNo]
,[BirthCity] as [LanguageNo]
,null as [Address]
,null as [E_Mail]
,null as [PhotoPath]
,[CreationDate] as [CreationDate]
,GETDATE() as [EditionDate]
,null as [AcceptXML]
,GETDATE() as [LastRefreshTime]
,GETDATE() as [LastChangePassDate]
,[NationalCode] as [NativeID]
,null as [PersonnelID]
,null as [CRC]
,null as [ActiveSessionID]
,'<SystemProfile>
<CardTable>
<Reminder TimeOut="300" isActive="1" />
<Recycle RemovePeriodTime="60" />
<Message RemovePeriodTime="60" />
<Outbox>
<ConsiderNow authorizedCount="" />
<Considered authorizedCount="" />
<NotConsidered authorizedCount="" />
<Reject authorizedCount="" />
</Outbox>
</CardTable>
<Page>
<DocFlowHistoryView default="Graph" />
<SendService viewInOutbox="1" searchItem="role" searchOption="include" />
<Theme default="silver" />
</Page>
<SecurityPolicies>
<UserAccountStructure minPassLen="-1" maxPassLen="-1" complexRule="-1" />
<ChangeAccountInFirstLogin value="-1" />
<MaxUserAccountAge value="-1" />
<PasswordExpirationAge value="-1" />
</SecurityPolicies>
</SystemProfile>' as [SystemProfile]
,null as IPLocation
,[Department_ID] as [DepartmentID]
,null as [Phone]
,null as [Mobile]
,0 as [UserType]
-----Change Linke server name
FROM [Bpms].[Bpms_Core].[Office].[Users],[Departments]
SET IDENTITY_INSERT [dbo].[Users] Off
End
------Sync Roles
if @Type=2
Begin
SET IDENTITY_INSERT [dbo].[Roles] ON
INSERT INTO [dbo].[Roles]
select
[RoleId] as Role_ID
,@OrganizationRoleID as [OrganizationRoleID]
,[Name] as [RoleName]
,[UserId] as [UserID]
,1 as [CardTableStatus]
,[Code] as [Code]
,[Enabled] as [IsActive]
,ParentID as [ParentID]
,@DepartmentID as [DepartmentId]
,[CreationDate] as [LastRefreshTime]
,null as [IsDefForCardTable]
,null [OriginRoleID]
from
------Change Linke server name
OPENQUERY ( [Bpms] ,
'
SELECT
[Name]
,[Code]
,[Enabled]
,[UnitId]
,[DepartmentId]
,[UserId]
,[RoleId]
,[HID]
,[CreationDate]
,[Bpms_Core].[Office].[GetSuperior]([RoleId]) as ParentID
FROM [Bpms_Core].[Office].[Actors] a
inner join [Bpms_Core].[Office].[Users] u
on u.[Id]=a.[UserId]
inner join [Bpms_Core].[Office].[Roles] r
on r.[Id]=a.[RoleId]
' ) a
End
SET IDENTITY_INSERT [dbo].[Roles] Off
END
GO
November 18, 2015 at 1:46 am
My apologies - I mis-read the error message.
When you use IDENTITY_INSERT you also have to specify the column list for the table you are inserting into.
SET IDENTITY_INSERT [dbo].[Test_Table_1] ON
go
INSERT INTO [dbo].Test_Table_1(TestID, Testvalue)
SELECT 55,44
SET IDENTITY_INSERT [dbo].[Test_Table_1] OFF
The code above works,
The following code doesn't, because it doesn't specify the columns after the table name
SET IDENTITY_INSERT [dbo].[Test_Table_1] ON
go
INSERT INTO [dbo].Test_Table_1
SELECT 55,44
SET IDENTITY_INSERT [dbo].[Test_Table_1] OFF
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply