Insert query violated the integrity constraints

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert query violated the integrity constraints

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2012-07-09 : 04:23:34

I get error when executed the query. Anyone can share how to solve this?


declare @intReturnValue int
Insert into [ServerName1].[Generic_engineering_Selection].dbo.tbl_Interface_Setting_Label([Component_Name],[Category],[Default_Value],[Enability],[Visibility],[Border_Style],[CSS_Class]) Values ('lblTestType','SPC Facility Data Extraction','* Test Type',1,1,0,'lblStyle') SELECT @intReturnValue=SCOPE_IDENTITY() 	

Insert into [ServerName1].[Generic_engineering_Selection].dbo.tbl_Interface_Setting_Mapping ([Page_ID],[Component_ID],[Category],[Component_Tbl_Name],[Row_No],[Column_No])	
Values (12345,@intReturnValue,'SPC Facility Data Extraction','tbl_Interface_Setting_Label',10,1)

(1 row(s) affected)
OLE DB provider "SQLNCLI10" for linked server "ServerName1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7344, Level 16, State 1, Line 4
The OLE DB provider "SQLNCLI10" for linked server "ServerName1" could not INSERT INTO table "[ServerName1].[Generic_engineering_Selection].[dbo].[tbl_Interface_Setting_Mapping]" because of column "Component_ID". The data value violated the integrity constraints for the column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 09:54:33

i cant see where you're assigning @intReturnValue variable a value. By default it will have NULL as value. So if Component_ID is NOT NULL field then you will error like below

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-09 : 13:04:16

SCOPE_IDENITY() is local to the calling environment. I think you'd have to do a remote call or use openquery. Here is a link that might help:
http://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources