Can not make autogenerate column in Sql server 2008

Hello,

I am using SQL server.  I am trying to make one of the columns as an autogenerate one.  But when I go to do that (Is Indentity), it is not allowing me to put value to Yes. it's greyed out.  I am new to the development of SQ server 2008.  Is this method right?  What else I have to do to make it autogenerated.  
I am using Sql server 2008 with asp.net.  On the screen top left corner it says, Administrator. But then why cannot I change the previllges?

Thanks,
armbsuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
You can't change an existing column to an identity.  You can ADD an identity column, then use "set identity_insert YourTableName on" to update the value of the identity column, then run "set identity_insert YourTableName off".

Now.  I can't GUARANTEE that you can update an identity with identity insert, but it would seem to make sense that you could.  I have only ever used it to "INSERT" into the identity column.
0
armbsuAuthor Commented:
Thank you for your comment.
This table does not contain any data as of now.  So I can add a new column as you suggested but how do I specify isIdentity as true or yes.  I tried with one test col but  again I cannot mark that col as Yes.
Also, do you mean by set identity_insert YourTableName on" and set identity_insert YourTableName off?
We do not have any DBA here. So please can you explain in detail?
0
BrandonGalderisiCommented:
If the table is empty, then adding a new column through SSMS you should be able to add the identity property.  Alternatively, through the query tool you can add it by running the following query.
alter table [YourTableName]
Add [YourNewColumnName] int identity(1,1)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark WillsTopic AdvisorCommented:
Are you talking about an automatically assigned value to a column for each insert ? then an identity column or a row GUID is the correct thing to do, but "autogenerate" is also a term often used in a datagridview in programming parlance, and is a different thing.

Brandon has given you the code for an Identity column that will start at 1 and increment by 1 for each new inserted row. For the other definition for autogenerate, please have a quick look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=650217&SiteID=1   

and you might like to describe what you need in a bit more detail...
0
armbsuAuthor Commented:
Mark and Brandon,

I a, talking about SQL server autogenerate colums and not gridview autogenrate.

I tried what Bradon suggested-
alter table [person]
Add [person_id] int identity(1,1)

I already have person_id col in the table and table has no data.
It gives an error column name in each table must be unique. Col name person_id in table person is specified mora than once.

I just have one col call ed person_id in person.   What I am doing wrong?


sql-error.jpg
0
BrandonGalderisiCommented:
Then drop the table, then add it.  You will have to add any indexes or constraints back on.
0
BrandonGalderisiCommented:
also, you should do it with SQL Server Management Studio, not Vis Web Dev.
0
armbsuAuthor Commented:
Worked!!
0
armbsuAuthor Commented:
Brandon,

Your query worked!!
Thanks,
But now I have another problem.  My another webform inserts person_id columns from a text file. Since this column is autogenerated, that programe is failing saying that it has to be OFF.  Is there any way that I can work this up?
0
BrandonGalderisiCommented:
Say again.  Your other thing wants to insert the person_id.  You really shouldn't have it both ways.  You either do or don't want identity.
0
Mark WillsTopic AdvisorCommented:
>> Since this column is autogenerated, that programe is failing saying that it has to be OFF <<

does not sound like identity, sounds like autogenerate ;)

0
Mark WillsTopic AdvisorCommented:
Either that, or you are maintaining multiple tables, and have to apply the same identity type process to you "people" table as well...
0
armbsuAuthor Commented:
Thank you all for commenting
I am working with my business reqs and will come up with the different design.  I wanted that when text file gets inserted along with other table,  it will insert in Person table as well , that way I could query the Person table and see all the person id taken.  I know it's an autogenerate col, but did not know that I will not be able to add into this. If while inserting text file,  if there is any duplicate entry into person table, I wanted to throw an error.

Anyways thanks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Remote Access

From novice to tech pro — start learning today.