IDW15: DCR: Make precedence constraint editor bigger

Hello,
If I have a fairly large expression then the box to display it isn't big
enough.

Please can you let us resize the precedence constraint editor?

DCR?
Thanks.

-JamieYes, please open a DCR Jamie. Thanks for the feedback.
K|||OK. Track ID 375512625

identity range check constraint

SQL 2005 merge replication.
I took the defaults on an article for publication - auto identity
management and the default ranges. If I attempt to insert >2000 rows
(the size of the 2 ranges assigned), I recieve the following error:
The insert failed. It conflicted with an identity range check
constraint in database 'DHD_73', replicated table
'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
column is automatically managed by replication, update the range as
follows: for the Publisher, execute sp_adjustpublisheridentityrange;
for the Subscriber, run the Distribution Agent or the Merge Agent.
Does this mean I cannot do an insert of more than 2000 rows at a time
to this particular article?
TIA,
john g.
Sorry, I forgot to add, I am doing the inserts on the publisher
jg
jgmein...@.gmail.com wrote:
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
|||You can batch up the inserts, or increase the size of the assigned rabge,
otherwise you're stuck.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Check the constraint to see what the range is. You can insert up to this
value and then run a sync. This should update the range.
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
<jgmeinder@.gmail.com> wrote in message
news:1167937361.253647.109410@.51g2000cwl.googlegro ups.com...
> SQL 2005 merge replication.
> I took the defaults on an article for publication - auto identity
> management and the default ranges. If I attempt to insert >2000 rows
> (the size of the 2 ranges assigned), I recieve the following error:
> The insert failed. It conflicted with an identity range check
> constraint in database 'DHD_73', replicated table
> 'dbo.tblAlaska_Facility_Manager', column 'facilityID'. If the identity
> column is automatically managed by replication, update the range as
> follows: for the Publisher, execute sp_adjustpublisheridentityrange;
> for the Subscriber, run the Distribution Agent or the Merge Agent.
> Does this mean I cannot do an insert of more than 2000 rows at a time
> to this particular article?
> TIA,
> john g.
>

Identity constraint - Drop Create dynamically?

Greetings all,

I have an identity field that I need to drop the constraint and recreate it inside a stored procedure. Actually if there is a better way to do what I'm trying to do, I'm all ears.

My concern is that the 2^31 will eventually be exceeded on the int declaration for this field and I'm not using the field for anything other than to identify uniqueness for a specific task.

What I would like to do is re-number this field each time the table is populated. (not remember the last index)Once I'm finished with the table, I'd like the constraint to be removed.

Any thoughts?

Adamus

The only 'real' option is to drop the existing IDENTITY field, and recreate a new one.|||

I was leaning that way.

Thank you,

Adamus

Identity Columns

I had a table with identity column.
started dumping of data into it.
After that i want to drop the identity constraint on that column using t-sql script.

is it possible? if so how can i do it.

Nope, you cannot remove the identity property from a column. What you can do is to transfer the values to a different column and drop the column:

create table testIdentity
(
identityColumn int identity
)
go
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
insert into testIdentity default values
go
alter table testIdentity
add nonIdentityColumn int null
go
update testIdentity
set nonIdentityColumn = identityColumn
go
alter table testIdentity
drop column identityColumn
go
select *
from testIdentity

|||Thanks.

But i would like to know why it is not possible through t-sql while we are able to do it from EM|||

Because EM does something along the lines of what I did. EM actually is pretty inefficient in many cases in how it performs operations (like adding a new column it does a drop column then and adds a new column where an ALTER TABLE would suffice). But it is usually understandable as it takes the easiest, most straightforward path for automation, rather than a method that looks better but cannot be automated quite as easy.

One thing to try is to trace what EM does using profiler. That is a great place to see the queries it does. In Management Studio, I changed the identity property, and these are the action queries that were sent:

CREATE TABLE dbo.Tmp_testIdentity
(
IdentityColumn int NOT NULL
) ON [PRIMARY]

go
IF EXISTS(SELECT * FROM dbo.testIdentity)
EXEC('INSERT INTO dbo.Tmp_testIdentity (IdentityColumn)
SELECT IdentityColumn FROM dbo.testIdentity WITH (HOLDLOCK TABLOCKX)')

go
DROP TABLE dbo.testIdentity

go
EXECUTE sp_rename N'dbo.Tmp_testIdentity', N'testIdentity', 'OBJECT'

go

The method I gave you is far less distructive, but either will work.