IDs of updated records

I have to update a table, and after I update I need to insert a record
for each updated record in some other table.
I need to know the IDs of the records which were updated in the first
table, so that when I insert records in the second table then I can put
that ID in a field.
How would I acheive this?
Thanks in advance.With a trigger I suppose.
CREATE TRIGGER dbo.UpdateBaseTableName
ON dbo.BaseTableName
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
INSERT AuditTable(id_column) SELECT id_column FROM inserted;
GO
See the topic "CREATE TRIGGER" in Books Online for more details.
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>|||On 2005, your the OUPUT option of the UPDATE command. If earlier version, do
a SELECT first based on
the WHERE condition to know the ID.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sehboo" <MasoodAdnan@.gmail.com> wrote in message
news:1138209391.561493.167780@.g43g2000cwa.googlegroups.com...
>I have to update a table, and after I update I need to insert a record
> for each updated record in some other table.
> I need to know the IDs of the records which were updated in the first
> table, so that when I insert records in the second table then I can put
> that ID in a field.
> How would I acheive this?
> Thanks in advance.
>sql

Identity Range not being updated

I have an SQL server 2000 environment where 1 server is the
publisher/database server and several clients using desktop edition sql
Server 2000. This environment is using merge replication. Several of my
tables utilize identity columns so I have my articles defined to
automatically manage identity ranges and I kept the default refresh rate at
80%. For users that are local for this application they use a web
application that connects to the database. Remote users use a windows
desktop application to connect to their subscriber database. When a remote
user returns to the office environment they are allowed to synchronize.
The problem I am encountering is that as users are connecting locally using
the web application to the server database, I had an error occur that stated
the identity range was full. The error told me what table/column that was
having a problem and that it had to be corrected by a replication agent. The
error also reported that I could us sp_adjustpublisheridentityrange to
correct the problem.
My question is why didn't the identity range automatically get corrected if
it was set at 80%?
Also, what is the best practice for how to handle this problem. My concern
is that this is a very heavily used system and how should I keep this error
from occurring?
i think sp_adjustpublisheridentityrange it's being called automatically
only when a subscriber connects and starts a merge job. if your
subscribers stay disconneted for a long time,
sp_adjustpublisheridentityrange won't be called, and you'll have to do
it manually
Guy Thornton ha scritto:

> I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally using
> the web application to the server database, I had an error occur that stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent. The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My concern
> is that this is a very heavily used system and how should I keep this error
> from occurring?
|||It only adjust the range if you sync within the threshold. You need to pick
representative thresholds which will allow the maximum number of expected
inserts within syncs.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>I have an SQL server 2000 environment where 1 server is the
> publisher/database server and several clients using desktop edition sql
> Server 2000. This environment is using merge replication. Several of my
> tables utilize identity columns so I have my articles defined to
> automatically manage identity ranges and I kept the default refresh rate
> at
> 80%. For users that are local for this application they use a web
> application that connects to the database. Remote users use a windows
> desktop application to connect to their subscriber database. When a
> remote
> user returns to the office environment they are allowed to synchronize.
> The problem I am encountering is that as users are connecting locally
> using
> the web application to the server database, I had an error occur that
> stated
> the identity range was full. The error told me what table/column that was
> having a problem and that it had to be corrected by a replication agent.
> The
> error also reported that I could us sp_adjustpublisheridentityrange to
> correct the problem.
> My question is why didn't the identity range automatically get corrected
> if
> it was set at 80%?
> Also, what is the best practice for how to handle this problem. My
> concern
> is that this is a very heavily used system and how should I keep this
> error
> from occurring?
|||Hilary,
Thanks for your reply. So is this to say that a user must synchronize
within the 80% threshold in order for it to be updated? If true that will be
hard for me to predict and set the threshold appropriately.
I wonder if it would be better for us to schedule a job to run periodically
to update the identity ranges? any ideas?
Thanks.
"Hilary Cotter" wrote:

> It only adjust the range if you sync within the threshold. You need to pick
> representative thresholds which will allow the maximum number of expected
> inserts within syncs.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:327C95D4-2582-45E5-8E25-3EBACCF3C0FB@.microsoft.com...
>
>
|||No, the user must sync between 80% of the threshold and 100% of the
threshold. Should he/she sync less than 80% of the threshold the range would
not be incremented.
The best approach is to have them sync within this range or set large
ranges.
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
"Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks for your reply. So is this to say that a user must synchronize
> within the 80% threshold in order for it to be updated? If true that will
> be
> hard for me to predict and set the threshold appropriately.
> I wonder if it would be better for us to schedule a job to run
> periodically
> to update the identity ranges? any ideas?
> Thanks.
> "Hilary Cotter" wrote:
|||The problem I am running into is that my identity ranges are filling up at
the publication server. Even if the user synchronizes within the threshold.
If my users are are not in the db_admin role then would that cause the ranges
not to get updated? If so, how can I work around that? My users cannot be
in the db_admin role.
"Hilary Cotter" wrote:

> No, the user must sync between 80% of the threshold and 100% of the
> threshold. Should he/she sync less than 80% of the threshold the range would
> not be incremented.
> The best approach is to have them sync within this range or set large
> ranges.
> --
> 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
>
> "Guy Thornton" <wdonotspamthornton@.incresearch.com> wrote in message
> news:CFADF7BC-E713-46AD-B45B-FA91AFDA41A1@.microsoft.com...
>
>
|||Hi Guy
I have the same problem with the identity range on the publication server
filling up. Have you found a soloution to this yet?
The subscribers get updated when they sync but not the server. How should I
get the server to update?
Thank you
/ Henrik
"Guy Thornton" wrote:
[vbcol=seagreen]
> The problem I am running into is that my identity ranges are filling up at
> the publication server. Even if the user synchronizes within the threshold.
> If my users are are not in the db_admin role then would that cause the ranges
> not to get updated? If so, how can I work around that? My users cannot be
> in the db_admin role.
> "Hilary Cotter" wrote:
|||Hi Henrik,
To correct the problem, we had to adjust the size of our identity ranges to
account for how often our remote users would synchronize with the server.
Adjust the range so that you have enough at the server to accomodate the
number of inserts expected in between when users synchronize with the server.
We also had to add the users to the sysadmin fixed server role to
automatically update the ranges. Otherwise we would have had to setup an
automated job to run periodically to adjust the ranges.
My concern now is what will happen when my ranges run out. How do I
allocate more ranges over time to ensure continued operation of my
application.
"Henrik" wrote:
[vbcol=seagreen]
> Hi Guy
> I have the same problem with the identity range on the publication server
> filling up. Have you found a soloution to this yet?
> The subscribers get updated when they sync but not the server. How should I
> get the server to update?
> Thank you
> / Henrik
> "Guy Thornton" wrote:

Identity range managed by replication is full and must be updated by a replication agent.

Hello,

I'm getting the following error message when I try add a row using a
Stored Procedure.

"The identity range managed by replication is full and must be updated
by a replication agent".

I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )

sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

For Testing:

I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.

I've checked the current Identity values in the Agent Table:

DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.

I check the Table to make sure there will be no conflicts with the
primary key:

SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.

Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:

Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80

In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?

The Identity range for this table cannot be exhausted! I'm not sure
what to try next.

Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:

Quote:

Originally Posted by

I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Identity or unique identifier

Hi there,
I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.
Any help would be great
Cheers
StuFor Identity columns you need to set the IsIdentity property to "Yes" under Identity Specification in the design view of the table. You then set the Identity seed which is the increment you want to have for each value (usually 1).
For Uniqueidentifiers ( I have not used them until now though I have worked on systems that have used them) you could use the NEWID() function.
Check out books on line for more info. Both have performance/efficiency issues that you need to understand before you implement them.|||Thats brill thanks very much for your response.
All the best
Stu

Identity

Hi Guru's
Our transactional replication is causing is problems with
our identity columns not being updated. I have followed
all the advice and zip nada ect.
So I was wondering if its possible to update the value
internally i,e, set the next identity to somthing.
Thanks for you time
RabbitHad the same problem. Fixed it by having the following
command in all the replication created insert scripts
DBCC CHECKIDENT, you can look up the rest on BOL
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
>--Original Message--
>Hi Guru's
>Our transactional replication is causing is problems with
>our identity columns not being updated. I have followed
>all the advice and zip nada ect.
>So I was wondering if its possible to update the value
>internally i,e, set the next identity to somthing.
>Thanks for you time
>Rabbit
>.
>|||Thanks Pete
>--Original Message--
>Had the same problem. Fixed it by having the following
>command in all the replication created insert scripts
>DBCC CHECKIDENT, you can look up the rest on BOL
>Peter
>"Real knowledge is to know the extent of one's
ignorance."
>Confucius
>
>>--Original Message--
>>Hi Guru's
>>Our transactional replication is causing is problems
with
>>our identity columns not being updated. I have followed
>>all the advice and zip nada ect.
>>So I was wondering if its possible to update the value
>>internally i,e, set the next identity to somthing.
>>Thanks for you time
>>Rabbit
>>.
>.
>