'Cannot Insert Duplicate Key' Error With Identity Column As PK
I guess there is first for everything...I had never seen error like this before
Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated.
In this case Primary Key is Identity column. I do not include Idenity column in Insert statement.
Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it.
I see this error randomly.
How should I debug it
View Complete Forum Thread with Replies
Related Forum Messages:
Identity Column On Table And Ignore Duplicate On Index
Hello, I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1. So first 5 rows inserted get identity 1 2 3 4 5 If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7. So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes. Is there any way to avoid this? Thanks, John
View Replies !
Insert Into Identity Column
I have migrated my application from Access to sql 2005 express. In access autonumber (identity) field was random number. fter conversion now i want identity field to be "increment". management studio does nt allow. I have created a new database with increment identity column but during importing data using insert into statement it give error Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF please suggest how to do it??
View Replies !
Insert Value In An Auto-identity Column
I'm a bit new to SQL Server, but here is the problem I am trying to getaround:1. I have lets say an Employee table where the emp_id is an identitycolumn that is auto assigned by the db.2. I wish to insert values into the tables, but sometimes I may wish touse the value of the emp_id that i supply while inserting (andsometimes not).Is there any way in MS SQL to do this? For e.g. is it setup like aconstraint that I can temporarily drop. I do not want to alter thetable, so that option is ruled out.Any other ways to do this (I know bcp is one of them that allows me todo this, but looking for ways in the db itself using SQL if possible)Thanks,Bharat
View Replies !
INSERT Into Table With IDENTITY Column
I'm sure this is a common problem but I can't find any relevant info on this site I have a table that I would like to insert values into. I want to take the values from a secondary table using a select statement. e.g. a simplified version... TABLE1 ( Id INTEGER IDENTITY, UserName VARCHAR(50), Description VARCHAR(50) ) TABLE 2 ( UserName VARCHAR(50), Description VARCHAR(50) ) Runing the insert statement INSERT Table1 (UserName, Description) SELECT * FROM TABLE2 results in the error erver: Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'Id', table 'CDS_Live.dbo.Table1'; column does not allow nulls. INSERT fails. The statement has been terminated. I was under the impression that an identity column would be automatically inserted by SQL server. Now I know I could write a piece of anonymous Transact SQL which declares a cursor by selecting all rows from table 2 and inserting rows into table 1 on a row by row basis, but is there any way I could do the Insert with a single INSERT statement? thanks neill
View Replies !
Identity Column Value Increments By 2 Rather Than 1 On Insert
I have an issue with a stored procedure activation on a service broker queue. The activation stored procedure simply RECEIVES the top message and then INSERTs a row into a table with an identity INT column. Each row inserted has the identity column value incremented by 2 rather than 1. Only one row is inserted in the table. If the activation is set to OFF and then manually calling the original stored procedure the insert works fine and the identity column value is only incremented by one. Do you have any suggestions on why the identity column value increments by 2? Thanks.
View Replies !
How To Set Start Identity Column During An Insert
Hi. I am doing a left join insert from two tables, and i know my ID is the Indentity column. I am doing something wrong and it has to do with the identity issue. I have set the identity to on, b/c i currently have data in the table (tblProduct) and I am inserting all the records not in that field from a table named Complete_Products. Can someone please examine this query and tell me what I am doing wrong? I think I need to set a start value for the identity but not totally confident on how to do that. Thanks. Below is my query with error. Set Identity_Insert tblProduct on INSERT INTO tblProduct (productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType) SELECT Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price, Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ FROM Complete_products LEFT OUTER JOIN tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode where tblProduct_1.productCode IS NULL Msg 545, Level 16, State 1, Line 2 Explicit value must be specified for identity column in table 'tblProduct' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
View Replies !
Insert Identity Depending On The Value Of A Column
Would like to have Identity Inserted wrt a date column Eg: ProdDate ID Details ============================ 2008.04.01 1 afafafaf 2008.04.01 2 GAFSGHFGF 2008.04.02 1 GAGJAGSDH 2008.04.02 2 QYTYTT 2008.04.03 3 QYTWRRT At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View Replies !
How To Insert A Numeric Identity Column?
Hi All, I have table called Product with seven columns all of nvarchar type. There are 150,000 records in this table. Also there's no unique identifier column in this table. I want to put a column lets say Column1 which start from 1 to 150000. The Column1 should be a unique column. How can achive this in SQL Server 2005? Looking for a quick help. Thanks a million, Zee
View Replies !
Insert Out Of Range ID Value Into Identity Column?
I need to import some data into a table that is in a merge bi-directional publication. The table has an identity column that is being "auto managed". The ID numbers I need to import are not within any of the assigned "ranges". They are less than any of the current ranges. Turning ident_insert on does not work because of the check constraints error. The merge replication triggers validate the ID range as well. Edit: using SQL 2005 standard publisher and subsciber. Publication is 2005.
View Replies !
BULK INSERT Into Table With Identity Column?
I have a file I'm trying to do some non-set-based processing with. Inorder to make sure I keep the order of the results, I want to BULKINSERT into a temp table with an identity column. The spec says thatyou should be able to use either KEEPIDENTITY or KEEPNULLS, but I can'tget it to work. For once, I have full code - just add any file of yourchoice that doesn't have commas/tabs. :)Any suggestions, folks?--create table ##Holding_Tank ( full_record varchar(500)) -- thisworkscreate table ##Holding_Tank (id int identity(1,1) primary key,full_record varchar(500)) --that doesn't workBULK INSERT ##Holding_TankFROM "d: elnet_scriptspsaxresult.txt"WITH(TABLOCK,KEEPIDENTITY,KEEPNULLS,MAXERRORS = 0)select * from ##Holding_tank
View Replies !
Insert Query On Table With Identity Column
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE.tbl_appointment-------------------1. appt_id (pk) --- identity column, seed 25, increment 12. date_id3. time_start4. time_end5. appt_details6. lkp_emp_idPrivate Sub btnAddAppts_Click()On Error GoTo Err_btnAddAppts_ClickDim strsql As StringDoCmd.SetWarnings Falsestrsql = "INSERT INTO [tbl_appointments] (lkp_emp_id, date_id, time_start, time_end, appt_details) values ('" & txtLkpEmpID & "', '" & txtDateID & "', '" & txtStartTime & "', '" & txtEndTime & "', '" & txtApptDetails & "')"DoCmd.RunSQL strsqlDoCmd.SetWarnings TrueDoCmd.CloseExit_btnAddAppts_Click:Exit SubErr_btnAddAppts_Click:MsgBox Err.DescriptionResume Exit_btnAddAppts_ClickEnd Sub I did check through Access and through Enterprise Manager and it is setup correctly. So I returned all rows in enterprise manager to manually enter an appointment to the table. I get the same error when doing data-entry straight to the table. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'appt_id'. It does not automatically populate the appt_id field the way it's supposed to. When I try to manually set a value in there, i get an error: "Cannot edit this cell."
View Replies !
Insert Only Identity Column Value In SQL Compact Edition
Hi, we are using a custom persistence framework on an SQL Server Compact Edition (3.5) database. It might occur, that a class gets persisted into a table having only a single IDENTITY column. Even worse, there may be additional columns in the database the persistence layer is not aware of. I'm now looking for a way to insert rows into that kind of table without specifying any column values. In SQL-Server 2005 (also Express edition), there is a form INSERT INTO table DEFAULT VALUES; which does the job. Unfortunately, this construct doesn't seem to be supported by the Compact Edition. Any suggestions? Many thanks in advance! Sincerly Martin Stettner
View Replies !
Can't Insert Rows In Table With Identity Column
Hello, I have a problem. I am trying to pull data out of one system and bring it into a SQL Server database for faster retrieval. The original table does not have an identity column and has a composite primary key. The table I am inserting the data into matches the original table exactly except I have an Identity column that I need for the removal of duplicates. The task gets to the final commit and then fails telling me that it can't insert a NULL into an identity field. Why is it trying to insert a NULL, the field should be auto-populating. I did add the Identity field to the table after the SSIS package was already built, but I did go into the destination and fix the column mappings. For the Identity column I just selected the "skip" option or whatever it was. Why are the identities not being auto-inserted and why is SSIS throwing this error? Can anyone help??? Thanks!
View Replies !
SQL Server Everywhere - Retrieve Identity Column After Insert Record
Hello Using Visual Studio 2005 Prof and SQL Server everywhere. How do get the identity column value after insert record. With SQL Server 2005, its quite easy to get by creating and insert statement on the tabledapter ( Insert statement followed by a select statement where identitycolumn = scope_identity()) How do this is sql everywhere?? regards
View Replies !
Cannot Insert Explicit Value For Identity Column In Table 'Clients' When IDENITY_INSERT Is Set To OFF
Code Snippet Private Sub AddClientToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddClientToolStripMenuItem.Click Dim addclient As New newclient() newclient.ClientID = 0 Dim result As DialogResult result = addclient.ShowDialog If result = DialogResult.OK Then Dim Client As clients_and_sitesDataSet1.ClientsRow Client = Clients_and_sitesDataSet1.Clients.NewClientsRow Client.ClientID = addclient.ClientID Client.Clientname = addclient.Clientname Client.Street_and_number = addclient.Street_and_number Client.Zipcode = addclient.Zipcode Client.Place = addclient.Place Client.Phone = addclient.Phone Client.Email = addclient.Email Client.Contact_person = addclient.Contact_person Clients_and_sitesDataSet1.Clients.AddClientsRow(Client) ClientsTableAdapter.Update(Client)------------------------------problem MessageBox.Show("New Client is saved") Else MessageBox.Show("User cancelled operation") End If addclient = Nothing End Sub Code in VB (2005 express edition) Hi all above is aa snipped from the code I am using. all works fine exxept the ------problem line. when i try to run the program, i get an exeption Cannot insert explicit value for identity column in table 'Clients' when IDENITY_INSERT is set to OFF how can i turn this on???? I seem to be stuck, vieuwed the msdn video on the subject and cant find what I am doing wrong. I hope u guys can help me out. And please give me specific guide lines on how to put in the code and code lines. I tried using SET IDENTITY_INSERT Clients ON But than I get all errors bout things not being declared, set not being supported anymore, things like that. Thanks for the effort you will put into helping me out and becomming a "self supported programmer"
View Replies !
Error Message On Attempted Duplicate Insert
Hi All, I have a web form that inserts information into two tables in sql 2005 database on a submit button click. I have a unique key on the tables preventing duplicate information which works fine. The problem I have is that at the minute if a users tries to insert a duplicate row they just get the built in sql error message. Is there a way I can validate the information before if goes into the database and display perhaps a label telling the user of their error or is there a way I can customize the build in sql error message? I've had a search on various forums and sites and can't find any info that would help me. Thanks in advance Dave
View Replies !
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions. 1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 --DECLARE @UserID uniqueidentifier 14 --SELECT @UserID = NULL 15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId 16 INSERT INTO [table] 17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) 18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View Replies !
Bulk Insert, Skip Rows With Duplicate Key Error?
Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?
View Replies !
IDENTITY INSERT Error
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 @CustomerIDnvarchar(5), @EmployeeIDint, @OrderDatedatetime= NULL, @RequiredDatedatetime= NULL, @ShippedDatedatetime= NULL, @ShipViaint, @Freightmoney, @ShipNamenvarchar(60)= NULL, @ShipAddressnvarchar(40)= NULL, @ShipCitynvarchar(15)= NULL, @ShipRegionnvarchar(15)= NULL, @ShipPostalCodenvarchar(10)= NULL, @ShipCountrynvarchar(15)= NULL, @OrderIDintOUTPUT AS DECLARE @InsertedOrderDatesmalldatetime --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
View Replies !
Identity Question: Why Is Value Still Used When There Is An INSERT Error
Hi I'm just wondering why the IDENTITY value is still used when you attempt an INSERT that fails? I placed a Foreign Key Constraint on my table. When I put bogus data in the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used! For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!! why does the Identity get incremented when the Insert fails? Angel
View Replies !
Instead Of Insert - Identity Null Error
I'm atempting to use an Instead of Insert Trigger on a view to insert a record into a base table. The base table boundaryline has an identity primary key defined. The following is the error I'm receiving: The column 'id1' in table 'dbo.ParcelBoundaries1' cannot be null. Could not insert a record in the database. The view is as follows: SELECT IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION, IS_CONFLICT, TYPE, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI, GEOMETRY_YLO, GEOMETRY_YHI, id1 FROM dbo.BoundaryLine The trigger is as follows: CREATE TRIGGER TR_ParcelBoundaries1 on ParcelBoundaries1 INSTEAD OF INSERT as BEGIN INSERT INTO BoundaryLine (IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION, IS_CONFLICT, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI, GEOMETRY_YLO, GEOMETRY_YHI) Select 'YES', 'NO','NO','NO','NO','NO','NO','NO','NO', GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI, GEOMETRY_YLO, GEOMETRY_YHI from inserted END Any thoughts would be greatly appreciated?
View Replies !
Error In Identity Column
For some reason my primary key, identity column skipped a couple of numbers. It went from row 734 to 736, 737, 739 Any ideas why this would happen? thanks
View Replies !
Error With Identity Column
hi, As i have created a table with a identity column and now i am using a procedure to insert the values into that table....but when i execute it it gives an err saying err converting varchar data to int and also on the asp.net ......plz help me out for the procedure and the using it with sqlcommand in .net. create table demo2(id int identity(1,1),name varchar(20)) create procedure sp_demo2 (@id int,@name varchar(20))as begin insert into demo2(name) values(@name) set @id=@@identity end exec sp_demo2 'j' thanks, rajiv
View Replies !
'Identity Insert Off' Not Detected, Validation Error
I am trying to capture the rows that fail during an insert. The insert is an OLE DB command component. I have config'd errors to redirect to a flat file. My problem is this I need to override the identity, fine if you are using 'fast load' but then you can't capture the redirected rows. So I am using the regular 'table or view' data access mode, but then I lose the 'keep identity' checkbox. At this point I add in another OLE DB command "SET IDENTITY_INSERT table OFF" before the actual insert ODB component. But now it won't validate. I changed the task 'delay validation' property to true, but still it is failing. It seemingly cannot detect this statement. So how then can I capture error rows from an insert where I need to switch the identity off? [This is a one off historic load I am working on]
View Replies !
SELECT @@IDENTITY During INSERT STATEMENT Error
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005: "INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;" Compact 3.5 doesnt like the SELECT statement claiming that: There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ] Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement? I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View Replies !
Error Finding Identity Column
I'm using SQL 2005 Server Management Studio - I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid However, I get an error message saying:Invalid object name 'sys.tables'. What might I be missing here?
View Replies !
Transaction Identity Column Error
I am calling 2 stored procs within the try catch block. To test,I manually changed the name of one of the stored procs to one that does not exist, so it can give me an error. When I run the page, it does not execute both stored procs and rollsback transaction. This part works.However if I run the page 5 times when one of the stored procs have an error. Then fix the stored proc name in the code. Then rerun it. My identity column is wrong.Ex:news_id12389As you can see after news_id 3, I made it give me an error. It didn't insert the record, but when I corrected the error and it inserted the record - the identity column was off.It should benews_id12345My code: Dim cn As New SqlConnection(ConfigurationSettings.AppSettings("conString")) cn.Open() Dim myTrans As SqlTransaction = cn.BeginTransaction() Dim cmdQuery As SqlCommand = New SqlCommand("usp_Ins_NewsTemp1", cn, myTrans) With cmdQuery With .Parameters.Add("@news_title", SqlDbType.VarChar, 100) .Value = "dddddddd" End With .CommandType = CommandType.StoredProcedure End With Try cmdQuery.ExecuteNonQuery() 'clears all the parameters from cmbQuery cmdQuery.Parameters.Clear() cmdQuery.CommandText = "usp_Ins_NewsTemp2" With cmdQuery With .Parameters.Add("@news_title", SqlDbType.VarChar, 100) .Value = "dddddddd" End With End With cmdQuery.ExecuteNonQuery() myTrans.Commit() Catch ex As Exception myTrans.Rollback() Throw End Try
View Replies !
OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View Replies !
Error 8102:cannot Update Identity Column
I set up replication on my DB between two server. The Publisher will not update tables on the Subscriber that use an identity column and I get an error "8102:cannot update identity column". I have looked around and can't seem to locate any solid fixes. If I remove the identity column from the subscribers tables the package works, but I am not able to use the DB on the subscriber because I need the Identity columns for my app to function properly. My Publisher and Subscriber are both running SQL Server 2000 Standard Edition SP3. Thanks.
View Replies !
Error 8102: Cannot Update Identity Column
Hi, I'd set up Transactional Replication btw 2 servers running on MSSQL 2000. However, from time to time, I encounter Error 8102. I try to reinitialise the subscription but failed. After I delete the current subscription and PUSH a new one, it will works fine again. I'm curious why this is happening as I'd already set all the tables (articles) in the subscriber to "YES, not_for_replication". Any idea? Thanks.
View Replies !
Fuzzy Lookup | Mutiple Identity Column Error
Hi, I have a table t1 and t2 with following structure. t1( ID1 int IDENTITY PRIMARY KEY, name1 varchar(20), addr1 varchar(20) ) t2( ID2 int IDENTITY PRIMARY KEY, name2 varchar(20), addr2 varchar(20) ) Objective here is to match name1 and name2 column using fuzzy look up. So, I used t1 as source table and t2 as reference/lookup table and mapped name1 and name2 column in Fuzzy look up editor. As output column I selected "ID2" column from t2. Now when i run the package, it throws error "Multiple identity columns specified for table '##FLRef_070522_14:16:39_5064_c1c6cbbd-5a54-4e36-9154-1371118f0931'. Only one identity column per table is allowed." I suppose that during Fuzzy lookup, SSIS internally created temporary table and thats where this error occurs when adding two columns as identity. Can someone help me in resolving this issue. Thanks Sid PS: I need ID2 column as output for further calculation.
View Replies !
Runtime Error 3622 / Deleting From A Table With An Identity Column
I am trying to delete a row from a linked SQL Server table in Access 2000 which has an Identity column as its primary key. I try and execute the code: sql = "delete from dbo_mass_status_change_history where id = " & .ItemData(intCounter) db.Execute (sql) And I get the following error: Run-time error '3622' You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column. Anyone? TIA!
View Replies !
DTS Table From Access To SQL, Identity Column Error. Should Be Easy, But I Can't Figure It Out.
I am trying to move data from Access to SQL Server 2000 using DTS. I have an Access Source and SQL Server Desitination, My destination table has a field called tableID that is not in the source. TableID is a Primary Key and an Identity column. I have Enable Identity Insert checked in the options of the Transform Data Task. When I execute ythe task, I get the error "Cannot insert the value NULL into column 'TableID'. Does not allow nulls. Insert Fails. Does anyone know why this simple task would fail? Mike
View Replies !
Insert Row In Table With Identity Field, And Get New Identity Back
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table). What is the most direct way to do this in SSIS? TIA, barkingdog P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View Replies !
Getting Duplicate Identity Values In SQL 6.5
We are experiencing a problem at more than one site - it has only just started happening. We are actually getting duplicate Identity column values in various tables and the values seem random - its not like the counter just gets wound back. We have used dbcc checkident and also bcp out and in the data, which of course corrected the tables but only temporarily. Our application is not doing any select into`s - just plain old inserts which for some reason are allowing dups to be inserted into the tables. problem sites are either sp3 or sp4. There are thousands of sites (including most of ours) where there are no problems like this with this particular app. Any help from anyone would be most appreciated.
View Replies !
CREATE TABLE DUPLICATE OBJECT/DUPLICATE FIELD NAME ERROR Msg 2714
Hello Everyone: I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer. When I try to create the tables, for the query, I am getting the following error: Msg 2714, Level 16, State 4, Line 12 There is already an object named 'UserID' in the database. Msg 1750, Level 16, State 0, Line 12 Could not create constraint. See previous errors. I have duplicated this error with the following script: USE [testing] IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users1] CREATE TABLE [testing].[dbo].[users1] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users2] CREATE TABLE [testing].[dbo].[users2] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL DROP TABLE [testing].[dbo].[users3] CREATE TABLE [testing].[dbo].[users3] ( [UserID] bigint NOT NULL, [Name] nvarchar(25) NULL, CONSTRAINT [UserID] PRIMARY KEY (UserID) ) I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database. I think that the schema is only allowing a single UserID primary key. How do I fix this? TIA
View Replies !
|
TRACKER
