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.
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.
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
hi,
i got a little problem. when i insert values into a table a trigger is launched wich updates something in the table too.
CREATE TRIGGER T1 ON Tbl
FOR INSERT AS
BEGIN
IF (UPPER(USER) <> 'CONVERSIE')
BEGIN
UPDATE Tbl SET
Datum = GetDate(),
User = USER,
WHERE Tbl.ID= (SELECT ID FROM inserted)
END
END
the error i receive is this: 'can not update identity column id' (id being pk).
does anyone know how to deal with this?
tnx,
erik.
Hello,
I have a frustrating problem occuring in Sql 2005. We have the need
in a few client upgrade situations to update the indentity column of a common
resource table. It is populated each release with common records and the
keys got out of sync for two releases due to developer error.
In Oracle this is not a problem. In SQL we get the message "Cannot
update identity column 'mycolumn'". To get around this in SQL 2000 we
updated the table by running the following.
exec sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = colstat - 1
where id = object_id(Event')
and name = 'EventKey'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
In SQL 2005 this gives us the error
'Ad hoc updates to system catalogs are not allowed'.
Now I understand the need to keep people out of the system tables.
However, can I have some sort of solution to this problem. I'm running a
bunch of ad hoc queries to update the values and I don't want to create an
exact duplicate of the table and copy over the information with the changes.
It is much easier to run a simple update.
Please advise...
Thanks,
Russ
We need to import data from flat/xml files into our database.
we plan to do so in bulk as amount of data is huge, 2GB+
we need to do some validation checks in our code after that we create insert queries.
We have identity columns that are used as foreign keys in child tables. Question is how can I write a bulk/batch insert statement that will propogate the identity column to the child, as for all other we are creating the query in the application memory.
there are 2 parent tables and 1st table value needs to be referred to in 7 tables and second table's value in 6.
Thanks much for your help.
By mistake i deleted a record form prod table.
how can i enter that record back? i get this error.
INSERT INTO item_details(ItemID,Item_Name,price) VALUES(201, bag,10)
ItemID is  identity column. can i disable  identity column and enter and then enable using sql statement?
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.
Now, I was asked to log the specific record where the failure was occured.
Something like log the identity column value or primary key value which record has failed.
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
Best regards,
George
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
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
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
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
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?
Cannot insert explicit value for identity column in table 'tblUsed' when IDENTITY_INSERT is set to OFF.
:confused:
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
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.
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
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View RelatedOk,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedI'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK or it will bring some problems?
Thank you in advance for your help
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
Hi,
I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.
I tried
alter table <table_name>
alter column <col_name> int Identity (1,1)
but it fails.
Ajay
WORD4LIFE
(http://www.word4life.com)
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 3 Replies View Related
Hi,
I have two tables table1 and new_table
Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .
And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.
This type of functionality is requirement for my project.
I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;
Please help me out to fix this issue
Thanks
Purnima
I am working on a project to consolidate 3 distinct buildings name code(6 digit character called BUILDING_CODE Primary Key) into one building code. This is an effort to merge/move everyone who is currently coded under the three building codes into one building code. We currently have this column defined on 5 tables as primary key (none identity) in SQL Server 2005.
Out of the five tables one has two store procedure dependencies.
What would be the best practice to update the six digit BULDING_CODE column Primary Key value without causing any issues?
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.
This is what I have:
rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close
Forgive me if this has already been posted, but I couldn't find it.
[edit to add:]
rsRec is an ADODB.Recordset in case that wasn't clear.
hey everyone, I have the following SQL:
CREATE PROCEDURE [dbo].[sp_InsertItem]
@item_channel_id INT, @item_title VARCHAR(75), @item_link VARCHAR(75),
@item_description VARCHAR(150), @item_long_description VARCHAR(1000),
@item_date DATETIME, @item_type VARCHAR(20)
AS
IF (@item_type = 'article')
BEGIN
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
END
IF (@item_type = 'mediaItem')
BEGIN
DECLARE @new_identity INT
DECLARE @new_link VARCHAR(100)
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
SET @new_identity = @@IDENTITY
SET @new_link = @item_link + @new_identity
UPDATE items
SET item_link = @new_link
WHERE item_id = @new_identity
END
GO
Basically, what I am trying to do is this...
IF the item type is article, insert normally... which works fine...
however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id>
however, when the sql runs the mediaItem code, it leaves the item_link field blank.
Why is this doing this?
Thanks all!