.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to drop identity property of Column

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server


Hi all,



Can any one help me or guide me how to drop identity property of a column with help of smo or TSQL ?



Any help is appreicated.

View Complete Post

More Related Resource Links

DataGridView>Edit Columns> Column Read Only property is unwilling to change

Hello, I am facing the following mystery: When changing the read only property of a column to false and reopening the "edit columns" window, it mysteriously turns back to true. Even when closing the designer and changing it in the designer code, the resulting execution does not allow edit, as well as when re-opening the designer you see true again.             this.numberDataGridViewTextBoxColumn1.DataPropertyName = "Number";             this.numberDataGridViewTextBoxColumn1.HeaderText = "Number";             this.numberDataGridViewTextBoxColumn1.Name = "numberDataGridViewTextBoxColumn1";             this.numberDataGridViewTextBoxColumn1.ReadOnly = true;//False is required We thought it might be related to data binding. rebinding the grid did not help as well. The is no other place in the code where it changes. Please advice, Thanks

Fetch Identity column just after inserting a row in table

Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

SSIS Ado >NET Destination Identity column inserts

Hi I am trying to build an SSIS package that imports the identity values from one SQL table to another.  I have an ADO .NET destination connection as the import is for SQL Azure although Im currently testing it in SQL 2008.  The connection has RetainSameConnection set to True.  Before the import I have a SQL task that runs SET IDENTITY_INSERT  activityDisplayGroup ON.  This executes but then I get an error message at the data flow task.  [Destination - activityDisplayGroup 1 [1]] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'activityDisplayGroup' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. In the profiler I see insert bulk "dbo"."activityDisplayGroup" ([name] VarChar(200) COLLATE SQL_Latin1_General_CP1_CI_AS) Is it at all possible to insert identies using the ADO .NET, if so what have I missed?   Thanks Dan      

Drop extended property "MS_Description" of ALL tables and ALL columns


Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?


Data Base Properties column Identity value will not accept value change


In the tutorial I am doing I have been requested to change a field in the colums properties of a table.  The ID column is currentl displaying NO and NO again for "is aIdentity.  I can Highlight the values but cannot change to yes.  either in the properties talbe or in the properties window on the right hand side of IDE interface.  How do I change that value?


Unable to use Managed Property mapped to Choice Dropdown list column in MOSS 2007


I have a choice list column containing dropdown values. I crawled the content source and created Managed Property to map it.

Also Configured Advanced Search webpart to display the property. But when I pick the property and perform search with values then no results are returned.

Can any one can help me out.?

Thanks in Advance.!

transactional replication with identity column


Dear friends,

I have a transactional replication between A and B where A replicates its data into B . the schema of A and B are EXACTLY same. I have a table "t" in both databases which has an IDENTITY column with name "c" and it is set to be NOT FOR REPLICATION in both databases. Indeed I have "A.t.c " and "B.t.c " columns. Also I should mentioned that I never do any changes in B.t table. It should just get the last data from A.t (but because of some reasons I need to have a same schema).

I do the following steps :

1- Add a new row to A.t . (and see the added row in B.t after a few seconds either )

2- update this added row's c2 column (not the c which is  the identity and PK column)

3- an exception I receive in Replication Monitor which says I cannot update the B.t.c column because it is an Identity column!!!... (I checked the related MS's related update stored procedure)...

the question is why it wants to update the Identity column which is not needed indeed!?

how to insert value into a Identity column in temporary table using dataflow task



I have requirement.

I have a temporary table with and identity column,table will get populated with data on the execution of a dataflow task except the identity Column.now I need to insert value into identity column.

How can I acheive this.


Thanks, A2H

Multi-Column ComboBox that binds to it's Value and Display Members for the drop down

I'm looking for a MultiColumn ComboBox solution as well. I've seen a lot of people talking about using Templates and I have to admit I'm not entirely comfortable with using Templates yet in WPF.

What I'm looking for specifically is a ComboBox that simply shows a two column drop down that displays the values resolved from DisplayMemberPath and SelectedValuePath for each item of the bound ItemsSource of the ComboBox. Once one of the items is selected I'm looking for the SelectedValue only to be displayed in the Text area.

Below is a crude diagram to explain what I'm looking for.

|CODE1 | Display value 1 | |
|CODE2 | Display value 1 | |
|CODE3 | Display value 1 | |

I've fumbled around using RelativeSource and a few other things but nothing works so far. If anybody has a clue about different ways to implement this I would love to hear them. A quick example using templates would be wonderful but also note that I'm planning probably to make this a reusable control in our solution since this is a common paradigm in our UI that I wouldn't want to have to repeat on each Window or UserControl in various projects.

Problem with Identity Column


Hi.. my table contains an identity column - QID - (1,1)

the row count starts with 1 and increments by 1.. if i delete any row and then add a new row to the table the QID field is not incrementing properly..

eg:: say i have 50 rows in a table.. QID field (Identity Column) starts from 1 and goes till 50...

now if i delete the 25th row... and then add a new row to the table, the QID field value should be 50 for the new row.. but in my case it is 51...

i doubt that auto commit option must have enabled somewhere.. so it happens like this.. ny wayz i am new to SQL.. pls help me to fix this issue...

Transactional Replication Identity column difference in SQL 2008


Hello All,

I have a Pull Transactional Replication in SQL 2000.

To enable the copying of Identity columns, my subscriber Insert stored procs are modified to mark


In the end, I set it to Off.

The environment is stable for 2 years. Now we are upgrading to SQL 2008.

Do I still need to do the above step?

I have noticed that SQL 20088 is even updating Subscriber table for schema changes also.

I am not sure if MS took care of that issue in SQL 2008.

Please let me know your inputs.



Table with Identity column issue


Hi all

I am using SQL CE 3.5, and I created a table with Identity column as PK (say I have columns ID, Name), but for somehow the table has very strange behavior. When I do insert statement

insert into table_a (Name) values ('Test Name')

I got message "ErrorMessage: The column cannot contain null values. [ Column name = ID,Table name = table_a]".

Then I ran

set identity_insert table_a off

And run the same insert statement above, I got "ErrorMessage: A duplicate value cannot be inserted into a unique index. [ Table name = table_a,Constraint name = PK_table_a ]"

Any idea what did I mess up with the table here? I checked carefully, the column Identitiy attribute is "True", Identity seed is 1, Identity increatment is 1.



Welcome to help me with my open source project at http://code.google.com/p/batch-image-watermark-processor/

Identity column in Management Studio


I am teaching a beginning class on SQL Server 2008 (not Express, and not R2), and we have begun using Management Studio.  One of the exercises included creating a small table with an identity column.  I then asked the students to insert some data just to see how the identity column works.

Strangely enough several of the students discovered that the identity column was increasing not by 1 but by some other integer.  I checked the Increment property and it was 1 in all cases.

What would cause this?  I have never see this before.

problem inserting using a stored procedure and identity column.


I recently moved a .net site from one machine to another, now for some reason one of the stored procedures is throwing an exception when attempting to insert! 

Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'dbo.tbl_Events' can only be specified when a column list is used and IDENTITY_INSERT is ON

BTW, the column in question does have the identity set to Yes in management studio

I was using originally SQL 2005, now its on SQLexpress 2008

stored procedure:



PROCEDURE [dbo].[proc_EventsAddEdit]int,



After Merge Replication Identity column was not generated Properly


HI All,


    I has been implemented Merge replication in sql server 2008, in that some tables Id locums are add 2000 and 20000 no add automatically incremented, why it was happens how to resolved this issues? if i want to get continues number what will it do, please help me in this ..... this is very urgent for me.




SQL Identity column / concurrent insert performance


How does sql handle concurrent insertion of rows into a table that has a integer identity column for a primary key?  Does it deadlock?  Does it just lock / block while inserts are performed one at a time?  My client app may be running twenty concurrent thread resulting in 20 concurrent inserts.  Is this a bad design from a performance perspective?  Is it better to use a different unique ID such as a GUID?




IDENTITY (Property)


How can the identity column be always increased by one even if some rows are deleted.

For example, the Identity property is set for increase by 1 and i added 4 rows, i deleted the 3rd and 4th row, the next one added should have 3 but not 4.

Can any one help me on this.

Thank you in advance for the help.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend