Click here to Skip to main content
15,354,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a script for table creation here as :
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PluginSettingXrefValue](
	[PluginSettingXrefValueID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_PluginSettingXrefValueID]  DEFAULT (newsequentialid()),
	[PluginSettingInstanceID] [int] NOT NULL,
	[PluginSettingID] [uniqueidentifier]  NOT NULL,
	[PluginSettingValue] [nvarchar](200) NULL,
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_PluginSettingXrefValue_ris_CreatedDate]  DEFAULT (getdate()),
	[CreatedBy] [nvarchar](200) NULL,
	[UpdatedDate] [datetime] NULL,
	[UpdatedBy] [nvarchar](200) NULL,
	[PluginID] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_PluginSettingXrefValue] PRIMARY KEY CLUSTERED 
(
	[PluginSettingXrefValueID] 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


And a stored procedure to insert Values in the table :
SQL
/****** Object:  StoredProcedure [dbo].[usp_CopyPluginSettingToXrefValue]    Script Date: 07/22/2014 11:32:45 ******/

Create Procedure [dbo].[usp_CopyPluginSettingToXrefValue]
(
@InstanceId int
)
As
Begin
declare @result as bit
IF  EXISTS (SELECT * FROM PlugInSettingValues)
BEGIN
IF  NOT EXISTS (SELECT * FROM PluginSettingXrefValue where PluginSettingInstanceId=@InstanceId)
BEGIN
     insert into PluginSettingXrefValue(PluginSettingInstanceID,PluginSettingID,
PluginSettingValue,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,PluginID) 
select @InstanceId,PlugInSettingID,PlugInSettingValue,GETDATE(),CreatedBy,null,null,PlugInID from PlugInSettingValues
set @result=1
END
END
End
RETURN @result
GO



But i am getting an error while executing the stored procedure only and a simple insert query i.e.
SQL
insert into PluginSettingXref values('2365700a-4c0e-e411-a3e4-5c260a0854c3',1,'24065243-d2ed-40f4-a275-021dc14cf746','false','2014-07-18 12:50:49.337',NULL,NULL,NULL)

is giving an error :
An explicit value for the identity column in table 'PluginSettingXrefValue' can only be specified when a column list is used and IDENTITY_INSERT is ON.

What is going wrong here?
Setting Identity_Insert ON is not an option as it might give a room for injection attacks.
Posted
Updated 23-Jul-14 7:23am
v3
Comments
dbrenth 23-Jul-14 13:21pm
   
So, what is RegistrationInfoSettingXref? Is there an insert trigger that writes to PluginSettingXrefValue?
Anshumaan Chaturvedi 23-Jul-14 13:23pm
   
my bad .... let me update the question
Anshumaan Chaturvedi 23-Jul-14 13:25pm
   
It is actually PluginSettingXrefValue and no , there is no trigger, the values are populated from code side. Code side implementation is correct.
Maciej Los 23-Jul-14 13:29pm
   
The proper INSERT statement is: INSERT INTO TableName (Col1, Col2, ..., ColN) VALUES(Value1, Value2,..., ValueN)
Change it, check it and let me know if it was helpful or not.
Anshumaan Chaturvedi 23-Jul-14 13:52pm
   
The statement in Procedure was working fine with previous table. Same Schema, Same Structure
PIEBALDconsult 23-Jul-14 13:44pm
   
Why do you have
[PluginID] [int] IDENTITY(1,1) NOT NULL
? That seems wrong.

You must exclude [PluginID] from the insert statement.

SQL
insert into PluginSettingXref([PluginSettingXrefValueID],[PluginSettingInstanceID],[PluginSettingID],[PluginSettingValue],[CreatedDate],[CreatedBy],[UpdatedDate],[UpdatedBy])
values('2365700a-4c0e-e411-a3e4-5c260a0854c3',1,'24065243-d2ed-40f4-a275-021dc14cf746','false','2014-07-18 12:50:49.337',NULL,NULL,NULL)
   
You need to specify your columns in your insert statement. With what you have it thinks you want to put '2365700a-4c0e-e411-a3e4-5c260a0854c3' into PluginSettingXrefValueID which is illegal. Hence the message.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900