Prblem With Inserting Data With Identity

Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

For Example:

I Have 2 Columns And 3 Rows..
1st Col Is The Identity, 2nd Is Some Data...
The Rows Has Identities Which Are 1,2,3 Respectively..
Then I Manually Deleted The 3rd Row Which Has The ' 3 ' Value In The First Col... Now When I Inserted Data, The Value Becomes ' 4 ' In The First Col, Becuase Obvioulsy It Is An Identity Column..

My Question Is, Is It Possible To Still Have The ' 3 ' Value When I Insert A New Value?

Or Maybe Just Reset The Table To Start With The Last Identity In The Table... Just Like With My Example...
(some Scripts Maybe?)Follow the steps
1. Drop Identity Column from table and Save
2. Re Create Identity Column.

Hope fully your problem will be resolve.

Regards|||[QUOTE=vinci]Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

You can use this:

SET IDENTITY_INSERT TableName ON

and then attempt to insert with an identity number that has been deleted.

POSTING AGAIN Transaction Replication - Identity column

Hi,
I am using transaction replication and the identity column is not included
for replication.
After creating the snapshot, i manually copying the data through stored
procedure from publishing database to subscription database. Then,if we run
the distribution agent,i am getting the following error. what will be the
process involved in applying the initial snapshot since i have already
copied the data. Will SQL server do bcp again? How can i avoid it?
Error Message: The process could not bulk copy into table '"hsassbtr"'.
Error Details:
Numeric value out of range
(Source: INFOSYS6 (ODBC); Error number: 22003)
Numeric value out of range
(Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
key in object 'hsassbtr'.
(Source: INFOSYS6 (Data source); Error number: 2627)
Aritcle is added as follows:
--added all the columns
exec sp_articlecolumn @.publication = 'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = NULL,
@.operation = N'add',
@.force_invalidate_snapshot = 1
--dropped identity('DEX_ROW_ID') columns
exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = N'DEX_ROW_ID',
@.operation = N'drop',
@.force_invalidate_snapshot = 1
I have created the subscription as follows:
exec sp_addpullsubscription
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.independent_agent = N'true',
@.subscription_type = N'anonymous',
@.description = N'InfoSys POC Setup Table Publication',
@.update_mode = N'read only',
@.immediate_sync = 1
exec sp_addpullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.distributor = @.distributor,
@.subscriber_security_mode = @.security_mode,
@.distributor_security_mode = @.security_mode,
@.frequency_type = 8, --Weekly
@.frequency_interval = 1, --Sunday
@.frequency_recurrence_factor = 1, --Every Week
@.frequency_subday = 1, --Once a Day
@.active_start_time_of_day = 220000, --10 PM
@.enabled_for_syncmgr = N'false',
@.use_ftp = N'false',
@.publication_type = 0,
@.offloadagent = N'false'
Any help?
Thanks,
Vijay
Can you check and make sure the subscribing table is empty?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Vijay" <vijay@.infosysusa.com> wrote in message
news:OEiWoNSkFHA.2852@.TK2MSFTNGP15.phx.gbl...
Hi,
I am using transaction replication and the identity column is not included
for replication.
After creating the snapshot, i manually copying the data through stored
procedure from publishing database to subscription database. Then,if we run
the distribution agent,i am getting the following error. what will be the
process involved in applying the initial snapshot since i have already
copied the data. Will SQL server do bcp again? How can i avoid it?
Error Message: The process could not bulk copy into table '"hsassbtr"'.
Error Details:
Numeric value out of range
(Source: INFOSYS6 (ODBC); Error number: 22003)
Numeric value out of range
(Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
Unexpected EOF encountered in BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
key in object 'hsassbtr'.
(Source: INFOSYS6 (Data source); Error number: 2627)
Aritcle is added as follows:
--added all the columns
exec sp_articlecolumn @.publication = 'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = NULL,
@.operation = N'add',
@.force_invalidate_snapshot = 1
--dropped identity('DEX_ROW_ID') columns
exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
@.article = @.table_name,
@.column = N'DEX_ROW_ID',
@.operation = N'drop',
@.force_invalidate_snapshot = 1
I have created the subscription as follows:
exec sp_addpullsubscription
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.independent_agent = N'true',
@.subscription_type = N'anonymous',
@.description = N'InfoSys POC Setup Table Publication',
@.update_mode = N'read only',
@.immediate_sync = 1
exec sp_addpullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publisher_db,
@.publication = N'InfoSys_Point_of_Care_Master_Tables',
@.distributor = @.distributor,
@.subscriber_security_mode = @.security_mode,
@.distributor_security_mode = @.security_mode,
@.frequency_type = 8, --Weekly
@.frequency_interval = 1, --Sunday
@.frequency_recurrence_factor = 1, --Every Week
@.frequency_subday = 1, --Once a Day
@.active_start_time_of_day = 220000, --10 PM
@.enabled_for_syncmgr = N'false',
@.use_ftp = N'false',
@.publication_type = 0,
@.offloadagent = N'false'
Any help?
Thanks,
Vijay
|||Vyas,
Subscribing table has data because i have manually copied the data through
stored procedure from Publising database.
Thanks,
Vijay
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OFnpAcSkFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Can you check and make sure the subscribing table is empty?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Vijay" <vijay@.infosysusa.com> wrote in message
> news:OEiWoNSkFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am using transaction replication and the identity column is not
included
> for replication.
> After creating the snapshot, i manually copying the data through stored
> procedure from publishing database to subscription database. Then,if we
run
> the distribution agent,i am getting the following error. what will be the
> process involved in applying the initial snapshot since i have already
> copied the data. Will SQL server do bcp again? How can i avoid it?
>
> Error Message: The process could not bulk copy into table '"hsassbtr"'.
> Error Details:
> Numeric value out of range
> (Source: INFOSYS6 (ODBC); Error number: 22003)
> ----
--
> --
> Numeric value out of range
> (Source: ODBC SQL Server Driver (ODBC); Error number: 22003)
> ----
--
> --
> Unexpected EOF encountered in BCP data-file
> (Source: ODBC SQL Server Driver (ODBC); Error number: S1000)
> ----
--
> --
> Violation of PRIMARY KEY constraint 'PKhsassbtr'. Cannot insert duplicate
> key in object 'hsassbtr'.
> (Source: INFOSYS6 (Data source); Error number: 2627)
> ----
--
> --
> Aritcle is added as follows:
> --added all the columns
> exec sp_articlecolumn @.publication =
'InfoSys_Point_of_Care_Master_Tables',
> @.article = @.table_name,
> @.column = NULL,
> @.operation = N'add',
> @.force_invalidate_snapshot = 1
> --dropped identity('DEX_ROW_ID') columns
> exec sp_articlecolumn @.publication =
> 'InfoSys_Point_of_Care_Master_Tables',
> @.article = @.table_name,
> @.column = N'DEX_ROW_ID',
> @.operation = N'drop',
> @.force_invalidate_snapshot = 1
>
> I have created the subscription as follows:
> exec sp_addpullsubscription
> @.publisher = @.publisher,
> @.publisher_db = @.publisher_db,
> @.publication = N'InfoSys_Point_of_Care_Master_Tables',
> @.independent_agent = N'true',
> @.subscription_type = N'anonymous',
> @.description = N'InfoSys POC Setup Table Publication',
> @.update_mode = N'read only',
> @.immediate_sync = 1
> exec sp_addpullsubscription_agent
> @.publisher = @.publisher,
> @.publisher_db = @.publisher_db,
> @.publication = N'InfoSys_Point_of_Care_Master_Tables',
> @.distributor = @.distributor,
> @.subscriber_security_mode = @.security_mode,
> @.distributor_security_mode = @.security_mode,
> @.frequency_type = 8, --Weekly
> @.frequency_interval = 1, --Sunday
> @.frequency_recurrence_factor = 1, --Every Week
> @.frequency_subday = 1, --Once a Day
> @.active_start_time_of_day = 220000, --10 PM
> @.enabled_for_syncmgr = N'false',
> @.use_ftp = N'false',
> @.publication_type = 0,
> @.offloadagent = N'false'
> Any help?
>
> Thanks,
> Vijay
>
>
|||Why did you copy data manually, when you are configuring replication with
automatic sync?
You should either subscrine with nosync option or delete the data from
subscriber before the snapshot is applied (if the snapshot is not configure
to truncate/delete table).
If there are any special requirements for this subscriber, pelase post the
complete info, and someone will be able to assist.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Vijay" <vijay@.infosysusa.com> wrote in message
news:uHJ%23ZgSkFHA.708@.TK2MSFTNGP09.phx.gbl...
> Vyas,
> Subscribing table has data because i have manually copied the data through
> stored procedure from Publising database.
> Thanks,
> Vijay
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:OFnpAcSkFHA.3256@.TK2MSFTNGP12.phx.gbl...
> included
> run
> --
> --
> --
> --
> 'InfoSys_Point_of_Care_Master_Tables',
>

Possible to have 2 Identity columns in a field?

Hi - is it possible to have 2 Identity (auto increment) fields in table?
Each time I try to change the one to an Identity column, it changes the
other to NOT an identity column.
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
No.
You cannot have more than one an identity column as well as updating
(identity) is not allowed.
You can generate your own an autoincrement colunm
create table #t
(
col1 int not null identity(1,1),
col2 as col1
)
insert into #t default values
select * from #t
Note: There are some "divantages" . With an identity you cannot be sure
that there will not be gaps or duplicates
"Mark" <anonymous@.devdex.com> wrote in message
news:uGw12ikyFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***|||No.
Such situation never occurs in proper design
--
Regards
R.D
--Knowledge gets doubled when shared
"Mark" wrote:

> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||No. Please explain why you would want more than one IDENTITY column in
a table.
What can you do with 2 columns that you can't already do with 1?
David Portas
SQL Server MVP
--|||Hi,
Its not possible to have two identity columns in the same table.
However I have never seen it changing an existing identity column into
not an identity column. Can u pls send me the script.
Regards,
Shanmugam
Mark wrote:

> Hi - is it possible to have 2 Identity (auto increment) fields in table?
> Each time I try to change the one to an Identity column, it changes the
> other to NOT an identity column.
> Thanks, Mark
>
> *** Sent via Developersdex http://www.examnotes.net ***

Possible to drop IDENTITY from a column?

I have several tables with the IDENTITY attribute (my problem now!) and I
need to remove the IDENTITY attribute from some of those tables/columns. Is
there a sane way of doing it? If so, could you share it with me?
Thanks!
MichaelUnfortumately not.
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:46DDBEBD-8F2D-41D9-922B-59FF098E88A2@.microsoft.com...
>I have several tables with the IDENTITY attribute (my problem now!) and I
> need to remove the IDENTITY attribute from some of those tables/columns.
> Is
> there a sane way of doing it? If so, could you share it with me?
> Thanks!
> Michael|||Not easy. You could create a new column, populate it with the old data from
the IDENTITY column then change any referencing indexes, constraints, etc
and drop the old column.
Alternatively, if you remove the IDENTITY property using Enterprise
Manager's table designer it will drop and re-create the table for you. Not
something you want to do while the system is in use though.
David Portas
SQL Server MVP
--|||Really not easy, Itzik wrote an article about that, where you can find the
information from behind the scenes:
http://www.windowsitpro.com/Article...22080.html?Ad=1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Snake" <Snake@.discussions.microsoft.com> schrieb im Newsbeitrag
news:46DDBEBD-8F2D-41D9-922B-59FF098E88A2@.microsoft.com...
>I have several tables with the IDENTITY attribute (my problem now!) and I
> need to remove the IDENTITY attribute from some of those tables/columns.
> Is
> there a sane way of doing it? If so, could you share it with me?
> Thanks!
> Michael

Possible to avoid the FILLING of GAP (created by deleting a record) in the Identity Column

Hi all,

(I am using SQL Server 2005)

I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.

Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!

I do not want to re-use the already used Identity value.

I just want to have the last CustomerID to be higher that all the previous ones.

Is there any way to do this?

Thanking you in advance,

Tomy

How are you deleting this record? If you follow this example through, you'll see that new records will get a new ID:

-- CREATE THE TABLECREATE TABLE CUSTOMER (idint IDENTITY(1,1),name varchar(50))-- INSERT TWO TEST RECORDSINSERT CUSTOMERVALUES ('Mark')INSERT CUSTOMERVALUES ('Fred')-- SHOW THE TWO TEST RECORDSSELECT id,name FROM CUSTOMER-- DELETE THE SECOND RECORDDELETE CUSTOMERWHERE id = 2-- INSERT A NEW RECORDINSERT CUSTOMERVALUES ('Joe')-- SHOW ALL THE RECORDS (THE NEW CUSTOMER WILL HAVE A NEW ID)SELECT id,name FROM CUSTOMER-- DROP THE TABLEDROP TABLE CUSTOMER

|||

Hi,

I inserted the records through program, and later, I deleted, the records manually using Visual Development Studio Manager. Later, when I inserted new records programatically, I found that even the deleted IDs were used.

Thanks

Tomy