ALTER COLUMN

I have an access database that I'm splitting its back-end to be located in
SQL server. One column in one of the access tables was autonumber. For
maitenance purposes, some of the rows in this column have been deleted. When
I converted the back-end to SQL, this column is defined as INT and I can't
define it as INT IDFENTITY (1,1) because of the rows that have been taken
out. I need to define this column as identity, so everytime the user adds a
new record, this column will generate an auto number. I tried the following
syntax with no luck. Any ideas?
alter table tbl_Reservation
with nocheck
ALTER COLUMN [reservation #] int IDENTITY (1,1)
constraint PK_ReservationNo primary key clustered([reservation #])
TSYou can't add or drop the IDENTITY column. You can add a new column with
the IDENTITY() property.
Or you can let Enterprise Manager do it, though I don't recommend this if
the table is of any consequential size (see http://www.aspfaq.com/2528 for
an example of the kind of thing Enterprise Manager does behind your back).
A
"TS" <TS@.discussions.microsoft.com> wrote in message
news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted.
> When
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
> a
> new record, this column will generate an auto number. I tried the
> following
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||I have come across the same problem. I cannot get the syntax right to create
an
IDENTITY (1,1) property on an existing INT column.
If it can be done in Enterprise Manager through the GUI, then there HAS to
be a way to do it in T-SQL.
Todd
"TS" wrote:

> I have an access database that I'm splitting its back-end to be located in
> SQL server. One column in one of the access tables was autonumber. For
> maitenance purposes, some of the rows in this column have been deleted. Wh
en
> I converted the back-end to SQL, this column is defined as INT and I can't
> define it as INT IDFENTITY (1,1) because of the rows that have been taken
> out. I need to define this column as identity, so everytime the user adds
a
> new record, this column will generate an auto number. I tried the followin
g
> syntax with no luck. Any ideas?
> alter table tbl_Reservation
> with nocheck
> ALTER COLUMN [reservation #] int IDENTITY (1,1)
> constraint PK_ReservationNo primary key clustered([reservation #])
>
> --
> TS|||> If it can be done in Enterprise Manager through the GUI, then there HAS to
> be a way to do it in T-SQL.
Yes, there is. Run profiler while you do it in EM, and prepare to be
amazed. Memorize the script. Rinse. Repeat. Good luck.
And FWIW, there are a lot of things that can be done through the EM GUI.
Not all of them are good, and not all of them are done the best/right way.
Be careful where you learn from. :-)|||I dropped the identity column with no problems, created another one with the
same name and since this column serves only as unique identifier, this
solution didn't hurt in any way. The problem is the identity column is not
generated in the front-end when adding a new record !! Any idea'
--
TS
"Aaron Bertrand [SQL Server MVP]" wrote:

> You can't add or drop the IDENTITY column. You can add a new column with
> the IDENTITY() property.
> Or you can let Enterprise Manager do it, though I don't recommend this if
> the table is of any consequential size (see http://www.aspfaq.com/2528 for
> an example of the kind of thing Enterprise Manager does behind your back).
> A
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:E29E76A2-4058-4099-A458-E3D600484499@.microsoft.com...
>
>|||On Fri, 5 Aug 2005 12:15:05 -0700, TS wrote:

>I have an access database that I'm splitting its back-end to be located in
>SQL server. One column in one of the access tables was autonumber. For
>maitenance purposes, some of the rows in this column have been deleted. Whe
n
>I converted the back-end to SQL, this column is defined as INT and I can't
>define it as INT IDFENTITY (1,1) because of the rows that have been taken
>out. I need to define this column as identity, so everytime the user adds a
>new record, this column will generate an auto number. I tried the following
>syntax with no luck. Any ideas?
Hi TS,
Create the table with IDENTITY column. Use the SET IDENTITY_INSERT
command to allow specification of the values in the IDENTITY column,
then port your data from Access to SQL Server. Now reset the
IDENTITY_INSERT operation to make SQL Server generate new identity
values for future inserts.
I think that will do the trick.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The identity column will be set appropriately even if not set in the
front-end. Actually, setting it would generate an error.
ML|||What do you mean "generated"? It is supposed to be generated on the
backend. What front-end and can you be more specific?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:DF38835E-69F8-4E35-ADBB-D339A33E910D@.microsoft.com...
>I dropped the identity column with no problems, created another one with
>the
> same name and since this column serves only as unique identifier, this
> solution didn't hurt in any way. The problem is the identity column is not
> generated in the front-end when adding a new record !! Any idea'
> --
> TS
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>