Managed Identity Ranges in Merge Replication

Hi,
Over the weekend I was taking advantage of system down time and made some
changes to my merge replication database.
The changes were all successful until this morning I get users who are
getting duplicate key error messages. I have verified that the duplicate
key is causing the error.
The remote locations have all been assigned there identity ranges, but it
appears that some cross-over has occurred from the previous ranges and
values. I thought SQL would look in the range assigned for the next
available number and use that. However, it appears that each subscriber is
using the next incremental number within their respective range. That is
the problem and I don't know what to do next.....Suggestions?
WB
What changes did you make to your merge replication database?
"WB" <none> wrote in message news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Over the weekend I was taking advantage of system down time and made some
> changes to my merge replication database.
> The changes were all successful until this morning I get users who are
> getting duplicate key error messages. I have verified that the duplicate
> key is causing the error.
> The remote locations have all been assigned there identity ranges, but it
> appears that some cross-over has occurred from the previous ranges and
> values. I thought SQL would look in the range assigned for the next
> available number and use that. However, it appears that each subscriber
is
> using the next incremental number within their respective range. That is
> the problem and I don't know what to do next.....Suggestions?
> WB
>
|||I added a new column to one table and changed the PK on the same table.
other changes included changing the field length on a few columns and
creating a new table
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:e9YK$79MFHA.2680@.TK2MSFTNGP09.phx.gbl...
> What changes did you make to your merge replication database?
>
> "WB" <none> wrote in message
news:%23h30yd8MFHA.2136@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
some[vbcol=seagreen]
duplicate[vbcol=seagreen]
it[vbcol=seagreen]
> is
is
>
sql

manage identity field at subscriber

Anyone has the standard approach for handling the identity column with replication environment.
Currently i am using following process.

http://www.databasejournal.com/features/mssql/article.php/3483421

I want to know if someone have some generic script to do this or much easier way..So your not using Auto_ID's ? & why not ?

Which Version of SQL you using ?

GW|||SQL Server 2000 SP3sql

Making Identity to be Not for replication

Hello there
I have many tables on my database (Approx 500)
Many of them have identity column and none of them are set for Not for
replication
Is there a way to change it by code automaticly?
try this
http://groups.google.com/group/micro...5?dmode=source
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e5gD6%23vAGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I have many tables on my database (Approx 500)
> Many of them have identity column and none of them are set for Not for
> replication
> Is there a way to change it by code automaticly?
>

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
pmud
You could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud
|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>
|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith
|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued incremen
t
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined th
e
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? I
n
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
>|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
..)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
>

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:
> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > Hi,
> >
> > I have a field in my sql table called ORDER_NO which is also the primary
> > key. Now, I want to add amother field called AO_Number whose valued
> increment
> > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> so
> > on...
> >
> > How to have these values for this field coz making it an identity column
> > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> >
> > Is this possible? & how?
> > --
> > pmud
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
> > You could have an identity column AND a calculated column that combined the
> > identity with the value you wanted.
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > Hi,
> > >
> > > I have a field in my sql table called ORDER_NO which is also the primary
> > > key. Now, I want to add amother field called AO_Number whose valued
> > increment
> > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > so
> > > on...
> > >
> > > How to have these values for this field coz making it an identity column
> > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > >
> > > Is this possible? & how?
> > > --
> > > pmud
> >
> >
> >|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
--
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
> > Hi Mike,
> >
> > Where do I have to write the sql code for creating a calculated column? In
> > user defined functions?
> >
> > Thanks
> >
> > "Mike Jansen" wrote:
> >
> > > You could have an identity column AND a calculated column that combined the
> > > identity with the value you wanted.
> > >
> > > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a field in my sql table called ORDER_NO which is also the primary
> > > > key. Now, I want to add amother field called AO_Number whose valued
> > > increment
> > > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > > so
> > > > on...
> > > >
> > > > How to have these values for this field coz making it an identity column
> > > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > > >
> > > > Is this possible? & how?
> > > > --
> > > > pmud
> > >
> > >
> > >

Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

Major problem with @@IDENTITY, Treeview and GUID

Hi,

I'm trying to insert data into locally stored database (SQL Server).
The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset.
The treeview contains checkboxes allowing a user to select what to install in the locally stored database.

To sum up:

1. Get data from a webservice' not my problem
2. Present data in a Treview control' not my problem
3. Allow to user to select which data to install' not my problem
4. Insert data that the user has selected into my db' MY PROBLEM!!!!

The Treeview looks like this.

- Group1
| | -- Rule1.1
| | -- Rule1.2
|
- Group2
| | -- Rule2.1
| | -- Rule2.2
| | -- Rule2.3
....

The Treeview is generated with DataRelations between Group and Rule.

My locally stored database is designed by a third party provider and therefore the database must not be altered.
The table I want to store data in is called "Groups" and it looks like this:

GroupID uniqueidentifier ' (newid())
GroupName nvarchar(50)
ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value.
GroupType tinyint ' 0 = subgroup, 1 = "top"group
Data in the table "Groups" would look like this:
GroupID GroupNameParentGroupIDGroupType
------------------
{000001...}Group1<NULL>1
{000011...}Rule1.1{000001...}0
{000012...}Rule1.2{000001...}0
{000002...}Group2<NULL>1
{000021...}Rule2.1{000002...}0
{000022...}Rule2.2{000002...}0
{000023...}Rule2.3{000002...}0

The third party also created a stored procedure called pr_AddGroup taking the following parameters:

@.GroupName ' can be both the RuleName and the GroupName
@.GroupType ' can be 0 for subgroup or 1 for "top"group
@.ParentGroup ' GUID

The problem with this stored procedure is that it does not have return value, which is here my problem actually lies.
If it returned @.@.IDENTITY I could use this as the parameter for @.ParentGroup.
Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @.@.IDENTITY to get the newly created record).

My SQL Commands look like this

Dim cmd As SqlCommand
Dim Conn As SqlConnection = New SqlConnection
Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI"
cmd = New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = Conn
cmd.CommandText = "pr_AddGroup"
cmd.Parameters.Add(New SqlParameter("@.GroupName", SqlDbType.NVarChar, 50, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.GroupType", SqlDbType.TinyInt, ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.ParentGroup", SqlDbType.UniqueIdentifier, ParameterDirection.Input))
dim cmd2 as SqlCommand
cmd2 = new SqlCommand
cmd2.commandtype = commandtype.Text
cmd2.commandtext = "SELECT @.@.IDENTITY as ID FROM Groups"
cmd2.connection = Conn
dim ParentGroupGUID as system.guid

To get the data inserted in the Groups table I would something like the following, but the code is very ugly
(and it doesn't work either);

 For Each Group In TreeView1.Nodes ' Loop through Groups
 If Group.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.ExecuteNonQuery()
 ParentGroupGUID = cmd2.executescalar()
 End If
 For Each Rule In Group.Nodes ' Loop through Rules.
 If Rule.Checked Then
 cmd.Parameters("@.GroupName").Value = Group.Text.ToString
 cmd.Parameters("@.GroupType").Value = 1
 cmd.Parameters("@.ParentGroup").value = ParentGroupGUID
 cmd.ExecuteNonquery()
 End If
 Next
 Next

I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)I'd recommend getting the stored proc changes because the time gap between running the stored proc and the select stmt can return a wrong ID for multiple users. So to main integrity of the stored proc and the ID its best to add the select stmt inside the stored proc itself. Also use SCOPE_IDENTITY() instead of @.@.IDENTITY.

hth|||Hi ndinakar,

Thanks for replying - but what do mean by:
"I'd recommend getting the stored proc changes"

I'm not allowed to change the stored proc because it's provided by a third party and if I modify the stored proc, the third party application is likely to break (and I don't wanna risk this).

??|||I meant the change be made in the stored proc it self to get accurate results. Even if you make a call to the table to return the latest Id there is no guratantee you would get the id for the isnert you did. if there are 5 ppl doing the insert at the same time the id's returned could get mixed up. so if you need to get the accurate id that was generated due to your insert..do the select immediately after the insert IN the stored proc itself. you need to xplain to the third party people and sort it out with them. else take the risk of not getting the right value.

hth