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.
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?
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).
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.
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".
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.
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.
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?
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
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'
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'.
Ok,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
I'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
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.
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;
I've a slight problem with configuring my Report Server. I have set up everything apart from the initialisation and and the Windows Service Identity, however when I select the panel for the latter I am presented with a message box displaying the following:
" Reporting Services Configuration Manager:
There was an error while switching panels. The most likely cause is an error retreiving WMI properties. The exception details are:
InvalidArgument=Value of '1' is not valid for 'SelectedIndex'.
Parameter name: SelectedIndex "
I have tried re-installing the entire SQL server setup and this made no difference, I followed this by removing then reinstalling just the Report Service. Still nothing.
Any ideas as to what it is doing and how I should go about fixing it?
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
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
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
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?
I am not able to modify(add rows into) a table present in sql server from my vb.net application. I am using sql server 2005 enterprise edition and vs.net 2003. are they compatible?
In my web page(.Net/c#) , I want to update few fields of a database table. The code is fine. The stored procedure through which I perform the execution is fine. I'm confidently saying this is because, if I try to do the update direclty while executing the stored procedure, it gets updated. I'm facing a weird problem while trying to update the fields from my web page. When I make an update and submit it, the first time it shows the updated value in the screen. But if I try to refresh it or redisplay the data ( I guess whenever it makes a trip to the database), the old values gets populated instead of the new values. I tried debugging it, but everything seems to be working fine. I looked up the execution in SQL Profiler, everything seems to be fine. I've no clue what's going on. I've proper permissions to make an update. So there is no permission issue. Could someone tell me how to handle this issue and what might be the possible problem which makes the values not update in the database?
I have a Table which stores data from two different databases. To update it I made a Web Form that opens a single record where you can edit the data (using TextBox). Then there is an Update Button, which is supoused to Update that record in the SQL Database. However, for some reason it does not work. I get no error and every thing seems to work fine, but the data is not updated. When running the same UPDATE statment in the SQL Query Analyzer everything works just fine. Here is the code for the button click (For test purpose I've set a spesific record to update the Task cell to "Test" and I've adde a DataGrid to be able to see if something is happening): Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click Dim objConnection As SqlConnection Dim objDataAdapter As SqlDataAdapter Dim objDataSet As DataSet Dim objDataView As DataView Dim strConnect As String Dim strSQL As String Dim strUpdate As String strConnect = "Persist Security Info=False;Integrated Security=SSPI;server=SRV01;database=DB1" strSQL = "SELECT * FROM Users WHERE UserID ='" & Request.QueryString("UserID") & "'" strUpdate = "UPDATE Users SET Task = 'Test' WHERE UserID = 36" '" & Request.QueryString("UserID") & "'" objConnection = New SqlConnection(strConnect) objDataAdapter = New SqlDataAdapter(strSQL, objConnection) objDataAdapter.UpdateCommand = New SqlCommand(strUpdate) objDataSet = New DataSet objDataAdapter.Fill(objDataSet, "User") objDataView = New DataView(objDataSet.Tables("User")) dgrTest.DataSource = objDataView dgrTest.DataBind() objDataAdapter.ContinueUpdateOnError = True Try objDataAdapter.Update(objDataSet, "User") lblTest.Text = objDataAdapter.UpdateCommand.CommandText Catch ex As Exception lblTest.Text = "Error" End Try End Sub
Be patient with me I am a relative newbie to all this.
Trying to develop a very simple package to update data in daily chunks from one server to another. Now my first step was to obtain the last date in my currenttable which I have a SQL task as my first operation. This brings back the last date for which I have data stored, all fine and dandy. I am then taking the single row result set, datetime data type, and feeding this to a variable.
My problem is my variable does not update, it just sits there and unusually for a variable, doesn't vary at all. Now we have an old hand with SSIS who tells me I'm doing nothing wrong and is equally puzzled. I have tried all datatype combinations and I'm getting nowhere fast.
Any help would be greatly appreciated.
The server is running:
Windows 2003 R2 SP1 SQL Server 2005 SP1
The server has also been configured as the DNS server.
Hi theres, I have installed SP2 on my server (Window server 2003 and SQl Server 2005 standard Edition both are 64 bit). But when i query SELECT @@VERSION. i got that latest installed SP is SP1.Even i have installed SP2 successfully and interactively then where m wrong(Window server have Sp2)
