IDENTITY_INSERT persistency

Hi all, quick question:

Is the IDENTITY_INSERT persistent, or only for a single transaction. I'm of course trying to insert into a database that has Idenity, and was wondering if I can just have a stored procedure run at startup to loop through all tables with identity fields and set IDENTITY_INSERT to on.

If not, I'll just have code up scripts to restructure the tables.

Thanks,

CooperThe IDENTITY_INSERT setting is persistant how ever what you want to do won't work. From BOL...

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.sql

IDENTITY_INSERT in transaction?

I have to add some rows into a table that is very busy with user-actions. To add the records, I need to use IDENTITY_INSERT ON to ensure that the records keep their original id. The question is: when I execute the insert-script with IDENTITY_INSERT ON, does this setting effect all users or just my transaction?Refer this : http://msdn2.microsoft.com/en-us/library/ms188059.aspx|||I can't find the anwer to my question there, you?|||

Sorry abt the link,.

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

|||

It is just for your current session/transaction only. It wont affect others

But before exiting form the SP/your Batch SET back the orginal property..

If your apps uses connection pooling it may cause a issue...

Mantra : if you start it, you have to finish it

sql

Identity columns problem in replication among n no of subscribers

I have created Transaction Replication.
Nearly 100's of tables there in my database.
I have created Recordid (identity) column with primary key in each table and
all are incremented by 1.
Front End application in VB6 is 100% dependable on this column. Changes in
this column may spoil my work of VB6.
Synchronisation is done @. subscribers side using below activex component in
VB6
Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
Subscribers side does not show identity columns with primary key. It shows
only name of the column as INT.
Front application is failed to run @. subscribers side.
Pls suggest how to Replicate identity column among n no. of subscribers.
Best Regards
Sanjay
You will have to create different identity seeds and an increments of 2 on
both sides - on the subscriber a seed of 2 with an increment of 2, and on
the publisher an seed of 1 with an increment of 1.
Make sure the identity column has the not for replication property on it.
Then set your articles up to not modify the existing tables on the
subscriber and in your pre-snapshot script have your table creation scripts
(along with their respective indexes).
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>I have created Transaction Replication.
> Nearly 100's of tables there in my database.
> I have created Recordid (identity) column with primary key in each table
> and all are incremented by 1.
> Front End application in VB6 is 100% dependable on this column. Changes in
> this column may spoil my work of VB6.
> Synchronisation is done @. subscribers side using below activex component
> in VB6
> Set objSQLDist = CreateObject("SQLDistribution.SQLDistribution.2")
> Subscribers side does not show identity columns with primary key. It shows
> only name of the column as INT.
> Front application is failed to run @. subscribers side.
> Pls suggest how to Replicate identity column among n no. of subscribers.
> Best Regards
> Sanjay
>
>
>
>
>
|||Dear Friend Hilary
Thanks for the help.
But one more solution can be there if I give different IDENTITY ranges to
each subscribers.
Pls help me If you know how to give diff IDENTITY ranges to each
subscriber.
Thanks in Advance
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
> You will have to create different identity seeds and an increments of 2 on
> both sides - on the subscriber a seed of 2 with an increment of 2, and on
> the publisher an seed of 1 with an increment of 1.
> Make sure the identity column has the not for replication property on it.
> Then set your articles up to not modify the existing tables on the
> subscriber and in your pre-snapshot script have your table creation
> scripts (along with their respective indexes).
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:OUx4ONMBHHA.4472@.TK2MSFTNGP03.phx.gbl...
>
|||You certainly can, so you can go to your subscriber and do a
dbcc('mytablename', checkident, reseed, 10000000) and it will probably work.
However you will always have to monitor the range you assigned and adjust it
as necessary.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> Dear Friend Hilary
> Thanks for the help.
> But one more solution can be there if I give different IDENTITY ranges to
> each subscribers.
> Pls help me If you know how to give diff IDENTITY ranges to each
> subscriber.
> Thanks in Advance
> Best Regards
> Sanjay
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ut$xlYMBHHA.4292@.TK2MSFTNGP02.phx.gbl...
>
|||Why do you need the identity property on the subscribers if you have
transactional replication, where the subscribers are treated as RO?
Perhaps this is being used as a failover server? If this is the case, the
easiest way to set it up is to enable automatic identity range management,
large range sizes and queued updating subscribers.
This way the subscriber can start entering data once the publisher is down
without any meddling with identity ranges on the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear friend Hilary
Thanks for your full co-operation. My all doubts are clear now except below
2.
1. If I am having file called EMP with identity column Recid.
I want to give automatic range control on publisher for n no. subscribers
what will be the command ?
2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
able to store or pull others data range from 1 to 1000 or 2001 to 3000.
Best Regards
Sanjay
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
> You certainly can, so you can go to your subscriber and do a
> dbcc('mytablename', checkident, reseed, 10000000) and it will probably
> work. However you will always have to monitor the range you assigned and
> adjust it as necessary.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
> news:%23e4pfjMBHHA.1220@.TK2MSFTNGP04.phx.gbl...
>
|||For automatic identity range management on the publisher for queued and
merge replication you have to drop your publication and subscriptions and
enable this feature - in the articles tab of the create publication wizard.
A subscriber with a range of 1000-2000 will be able to receive replicated
commands for other ranges if the not for replication constraint is enabled.
Otherwise he will not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SANJAY PAWAR" <sanju@.nisiki.net> wrote in message
news:%239Zlc%23yBHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear friend Hilary
> Thanks for your full co-operation. My all doubts are clear now except
> below 2.
> 1. If I am having file called EMP with identity column Recid.
> I want to give automatic range control on publisher for n no. subscribers
> what will be the command ?
> 2. If I give range 1001 to 2000 to subscriber1, will he (subscriber1) be
> able to store or pull others data range from 1 to 1000 or 2001 to 3000.
> Best Regards
> Sanjay
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5x4t7MBHHA.4428@.TK2MSFTNGP04.phx.gbl...
>

identity column rollback?

I am using a stored procedure to insert data to a table.

If there is any error then i rollback the transaction. this works fine.

but the identity column gets incremented, i dont want any of the values to be skipped due to an error as that number has to be accounted for.

do you know anyway in which this is possible to rollback the identity value from the DB?nihar,

I don't know your stored procedure processing fully, but I think using DBCC CHECKIDENT() will help you out.

Depending on what the current identity value is for the table in question in relation to any record gaps, you may want to examine IDENT_CURRENT() too.

Check BOL for details...

Hope this helps!

Kael|||thanks Kael,

that worked fine. dbcc checkident

heres what i was doing

.
.
begin transaction
insert into sometab values (somevalues)
set @.outparam = @.@.identity

insert into someother tab values (@.outparam...)
if @.@.error <> 0
begin
rollback transaction
dbcc checkident('sometab') --this is what i have added now
end
else
begin
commit transaction
end

this i have done as due to rollback the identity should not have increased.

thanks again.|||Hmmm...that's funny. I'm trying to image by looking at your code how the identity would increase, even though the transaction is being rolled back, but I can visualize it. Oh well. Looks like that DBCC command worked for you, so I'd quit while I'm ahead!

Kael|||hey it increase the value as soon as i insert into the table.

try this:
begin trans
insert into a table with identity column
select @.@.identity
rollback
insert again
check identity column it will have increased skipping the one which rolled back.

if u want i can give u the entire stored proc attached: its 200 odd lines :D|||Identity is designed for multitasking.
If you insert values 1-3, others can insert 4-5. If you rollback and they don't, they must have values 4-5. So you must write your own multitasking code for values without gaps.

Good luck!|||If that is the case then what is the purpose of identity.

Can you tell me what will happen if i insert values 1-3 then rollback, others insert 4-5 and save.
after rollback i call the 'dbcc checkident' proc. what happens then? Is it correct to do that?

if not proper what alternatives do I have to consider?|||You can use dbcc checkident reseed, but only if you are ADMISTRATOR.
No user can use it even by trigger. I recomend something like SP with

begin tran
INSERT TABLEX(XID)
select max(XID)+1
from TABLEX (XLOCK)
.
.
.
if ...
ROLLBACK
else
COMMIT

I am not sure about the level of locking used. I cannot use BOL now.

Good luck!|||Tell me what happens when u lock the insert max, and someone else call the max of whatever.. if u get 3, he will also get 3 since u havent committed yet.

in this case what happens, you have to trap a primary key violation and call insert again?

u will have to lock the table in that case..

or what else?|||He must wait, the same for dbcc checkident.|||it Wouldnt be ideal as conflicts will also arise when someone is editing the table.

where time would be the essence this wont really work. i have seen it happen.. even though the lock is for the minimal of time, any procedure which has to wait for another to release isnt the ideal construct.|||A. 1, 2, 3 -> one user or else high locking
B. 1, 7, 45 -> an identity and no special problems

You must decide. Sometimes you must choose A :)