Alternate Synchronization Server Schema Change Problem

Greetings:
We have set up a test scenario in Sql Server 2000 using merge replication
with:
A primary synchronization server (Publisher A)
An alternate synchronization partner (Publisher B)
Two subscribers to both publishers (Subscriber Y and Subscriber Z)
After some initial difficulties, we were able to get the alternate synch
partner running where both subscribers will successfully synch to either
machine without difficulty. (See thread “Alternate Synchronization Partner
-- Error”, originally posted on March 10.)
The next part of testing phase before going live is to test how schema
changes occur with alternate synch partners. We find NO documentation on
this subject anywhere, so if anyone has resources on how to do schema changes
where an alternate synchronization partner is involved, we would be very,
very grateful.
Our basic question: How are schema changes done where an alternate
synchronization partner is involved? We want to know exact procedures to do
the following:
-- Add a column
-- Drop a column
-- Add a table
We have tried to add a column as follows:
1.Used “sp_repladdcolumn” and added a column to one table in Publisher A,
the primary publisher. (See next paragraph on warning received.)
2.Synchronized the Publisher B, the alternate synch partner and it brought
in the column.
3.Synchronized Subscriber Y to Publisher A, and it brought in the column to
the subscriber.
At #1, when “sp_repladdcolumn” ran, I received the following warning:
“Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_D536588A75244907040F5997233E41C8_pal'.
The stored procedure will still be created.”
The Microsoft knowledge base article 811483 says this warning can be
ignored, but I wonder about being able to ignore this warning because of the
results we received described below.
Results from above process:
-- NO DATA UPDATES FROM PUBLISHER A WILL PROPAGATE TO EITHER PUBLISHER B,
THE ALTERNATE, OR TO SUBSCRIBER Y WHEN SYNCRHONIZING. New records added at
Publisher A do propagate, but no updates of existing data will. Inserts
propagate – updates do not propagate.
-- In addition, all data changes, inserts, and deletes made at either
Publisher B, the alternate synch partner, or at Subscriber Y, DO propagate
back to the primary Publisher A.
When I found that no data updates would propagate to the alternate synch
partner or the subscribers, I then
1.Used “sp_repldropcolumn” to drop the column I had added on Publisher A,
the primary.
2.Synchronized Publisher B, the alternate synch partner and it dropped the
column.
3.Synchronized Subscriber Y and it dropped the column.
I then tested data updates from Publisher A, the primary, and the updates
all began to propagate to Publisher B, the alternate and Subscriber Y once
again.
Summary:
-- A column was added to the Publisher A and the schema change propagated
to Publisher B and Subscriber Y.
-- After the schema change was propagated, any data UPDATES on Publisher A
would not propagate to the Publisher B or Subscriber Y. INSERTS however,
would propagate.
-- The column was dropped at Publisher A and that schema change propagated
to Publisher B and Subscriber Y.
-- Data updates from Publish A began to propagate again go Publisher B and
Subscriber Y.
What is wrong with the procedures I am using to make schema changes that are
causing the primary synchronization server to stop propagating data updates
after the column is added, but when the column is dropped, updates begin to
propagate again?
Any procedures for schema changes when an alternate synchronization partner
is involved would be very much appreciated.
Thank you.
Bill
Hi Bill,
From your descriptions, I understood that you would like to why
sp_repladdcolumn does not take effect. If I have misunderstood your
concern, please feel free to point it out.
First of all, please understand that replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need detail and
prompt assistance, I recommend that you open a Support incident with
Microsoft Customer Service and Support (CSS) so that a dedicated Support
Professional can work with you in a more timely and efficient manner. If
you need any help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Secondly, based on my knowledge, once the column is added at the original
publisher, the merge agent between the original publisher and the alternate
publisher has to be run so that the new schema is sent to the alternate
publisher. Also, the merge agent between the original publisher and the
subscriber needs to be run so that the schema definition is sent here. As
long as the schema of the replicating pairs match, there are no issues.
It seems your process is OK and it's weird that you are not able to
synchronize the data. Do you find any other error messages in Event Logs or
SQL Logs?
You may generate the file as following Knowledge Base article asked and
then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
remove 'online' in the email address as it's only for SPAM)
HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
http://support.microsoft.com/?id=312292
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Michael:
Thanks for the information. I was preparing to send you the logs when I
suddenly wondered if I needed to reinitialize the pull subscriptions. So I
decided to try that first, and it worked. After reinitializing all
subscriptions, all data changes began to synchronize with no errors or
problems.
I then ran several additional tests with adding and dropping new columns.
When I reinitialized the subscriptions, everything synchronized. When I did
not reinitialize, the schema would propagate, but data changes would not.
This is clearly different from when there is no alternate synch server. We
have added and deleted columns many times before with sp_repladdcolumn or
sp_repldropcolumn when no alternate synch server was involved. Data changes
always continued to synchronize properly in that circumstance. Perhaps some
note needs to be put in BOL about this difference when an alternate
synchronization server is involved? Just a suggestion that might save others
time.
The next test I am going to try is to add a new table (article) to our test
database and see if I can get it to propagate to the alternate synch server
and the two subscribers we have.
You have been a great help in pointing me the right direction. I really
appreciate it.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> From your descriptions, I understood that you would like to why
> sp_repladdcolumn does not take effect. If I have misunderstood your
> concern, please feel free to point it out.
> First of all, please understand that replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need detail and
> prompt assistance, I recommend that you open a Support incident with
> Microsoft Customer Service and Support (CSS) so that a dedicated Support
> Professional can work with you in a more timely and efficient manner. If
> you need any help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Secondly, based on my knowledge, once the column is added at the original
> publisher, the merge agent between the original publisher and the alternate
> publisher has to be run so that the new schema is sent to the alternate
> publisher. Also, the merge agent between the original publisher and the
> subscriber needs to be run so that the schema definition is sent here. As
> long as the schema of the replicating pairs match, there are no issues.
> It seems your process is OK and it's weird that you are not able to
> synchronize the data. Do you find any other error messages in Event Logs or
> SQL Logs?
> You may generate the file as following Knowledge Base article asked and
> then send to me direcly v-mingqc@.online.microsoft.com (plase ensure to
> remove 'online' in the email address as it's only for SPAM)
> HOW TO: Enable Replication Agents for Logging to Output Files in SQL Server
> http://support.microsoft.com/?id=312292
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I searched internal but limited resource on this topic about why
reinitializing make all things go smoothly as I cannot reproduce it on my
side as you described. If you want to fingure out the root cause of this
issue, you'd better send the logs to our PSS guys as Replication issues
might turn out to be very complicated.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael;
With the reinitialization of the subscriptions, the schema changes and data
changes synchronized perfectly on numerous tests. I put together a process
for us to use in these circumstances because it is a bit different between
add/drop a column and adding a new table. In any case, I did get them work
fine with the altternate synch server.
If I did not reinitialize the subscriptions, the schema would propagate, but
the data changes would not from the primary server. I tried this in several
tests and could not get it go, but since I have a procedure that works now,
my business partners and I are happy with it and will go live in the next
month or two with our alternate synchronization partner.
I appreciate all your help and suggestions.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> I searched internal but limited resource on this topic about why
> reinitializing make all things go smoothly as I cannot reproduce it on my
> side as you described. If you want to fingure out the root cause of this
> issue, you'd better send the logs to our PSS guys as Replication issues
> might turn out to be very complicated.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Thanks for your perfect summary and I believe others will also benefit from
your great work
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
Ed
You can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed
|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would b
e
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed

Alternate Key (from good 'ole ISAM file days)

In an ISAM file, you can have a primary key and alternate keys.
In a SQLServer database, you can have a primary key and foreign keys
attached to other tables.
Pardon my ignorance, but is it possible to identify a field in a table as an
alternate lookup? For example, empid is the primary and emplastname would be
an alternate.
--
EdYou can set up additional indexes on your tables. Since your Primary Key is
most likely clustered, these additional indexes will have to be
non-clustered. A good starting point might be to look at which queries are
run the most, and which ones are taking the most time, and index the columns
used in the WHERE clauses of those queries.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Ed|||In a relational database, the term alternate key implies unique values.
Unique constraints are usually defined on alternate keys.
It looks like what you want is an index. You can add an index on your
emplastname column to improve performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:0C0B6107-E67E-4847-BBB0-0DD247DEACC6@.microsoft.com...
> In an ISAM file, you can have a primary key and alternate keys.
> In a SQLServer database, you can have a primary key and foreign keys
> attached to other tables.
> Pardon my ignorance, but is it possible to identify a field in a table as
> an
> alternate lookup? For example, empid is the primary and emplastname would
> be
> an alternate.
> --
> Edsql

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:

> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering unique index to primary key

Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-Joel
I'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon
|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

altering a primary key property

I need to change a primary key from clustered to nonclustered. Is there any
way to do this without completely dropping and re-adding the primary key?
There are several foreign keys throughout the database referencing this
primary key and I was hoping to make this change without having to drop all
those foreign keys and recreate them afterward.
-JoelI'm pretty sure you have to drop it and re-add it
Greg Jackson
PDX, Oregon|||Only way is to drop and recreate the Primary key constrain mentioning
NONCLUESTERED.
Thanks
Hari
SQL SERVER MVP
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e2zJtLWXFHA.632@.TK2MSFTNGP14.phx.gbl...
> I'm pretty sure you have to drop it and re-add it
>
> Greg Jackson
> PDX, Oregon
>

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name'
There is no foreign key constraint built like this with this statement is executed. Any ideas?
This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>
|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, aft
er re-creating a table (afm_groups) with a new primary key constraint, I run
the following line:
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
go
And get the error:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_fld
s_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_na
me'
There is no foreign key constraint built like this with this statement is ex
ecuted. Any ideas?This means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>|||Thanks, Aaron.
You were right. I created an invalid data condition somehow.
Paul Doucette
ARCHIBUS, Inc.sql

ALTER TABLE statement error while rebuilding schema

I am running a product that is rebuilding several tables. On one table, after re-creating a table (afm_groups) with a new primary key constraint, I run the following line
ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name
g
And get the error
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'afm_flds_edit_group'.
The conflict occurred in database 'Hq', table 'afm_groups', column 'group_name
There is no foreign key constraint built like this with this statement is executed. Any ideasThis means that you have data in the edit_group column that doesn't have a
match in the afm_groups table.
(A foreign key's value must exist in the parent table.)
To identify the rows that are violating the foreign key:
SELECT * FROM afm_flds WHERE edit_group NOT IN (SELECT group_name FROM
afm_groups)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"psrd66" <paul_doucette@.archibus.com> wrote in message
news:3B6C2338-2672-4D74-936B-17E3D728BEE4@.microsoft.com...
> I am running a product that is rebuilding several tables. On one table,
after re-creating a table (afm_groups) with a new primary key constraint, I
run the following line:
> ALTER TABLE afm_flds ADD CONSTRAINT afm_flds_edit_group
> FOREIGN KEY (edit_group) REFERENCES afm_groups(group_name)
> go
> And get the error:
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'afm_flds_edit_group'.
> The conflict occurred in database 'Hq', table 'afm_groups', column
'group_name'
> There is no foreign key constraint built like this with this statement is
executed. Any ideas?
>

alter table

Hi, my tb1 already have a primary key, how to drop it and recreate a new one
is auto id primary? Please help...Here is a good post from Itzik Ben-Gan:
http://www.windowsitpro.com/Article...qlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:Ob$nMZCTFHA.3464@.tk2msftngp13.phx.gbl...
> Hi, my tb1 already have a primary key, how to drop it and recreate a new
> one is auto id primary? Please help...
>

Alter a constraint?

Is there a way to add a column to a PRIMARY KEY constraint (without
deleting and recreating it?) Thanks.No.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e319f3d61c2ebb1989918@.msnews.microsoft.com...
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.|||No, there is no ALTER CONSTRAINT. You will need to DROP/CREATE.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1e319f3d61c2ebb1989918@.msnews.microsoft.com...
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Is there a way to add a column to a PRIMARY KEY constraint (without
> deleting and recreating it?) Thanks.
No, for a pure index it is possible by adding the WITH DROP_EXISTING
clause.
If there is a reference to the PK from other tables, it's quite a complex
operation anyway.
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|||What is PK?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974AC0BF1D4DAYazorman@.127.0.0.1...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> No, for a pure index it is possible by adding the WITH DROP_EXISTING
> clause.
> If there is a reference to the PK from other tables, it's quite a complex
> operation anyway.
> --
> 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|||Primary Key

> What is PK?|||duh... I should had knew that.
Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uBvY3THGGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Primary Key
>
>|||Some DBAs prefer this one: http://en.wikipedia.org/wiki/PK_machine_gun
;)
ML
http://milambda.blogspot.com/|||I used that all the time in a game call Battlefield 2 by EA. Its my best
choice of weapon.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:7CFE203C-8622-47BE-B144-F1573326C180@.microsoft.com...
> Some DBAs prefer this one: http://en.wikipedia.org/wiki/PK_machine_gun
> ;)
>
> ML
> --
> http://milambda.blogspot.com/|||The real one is slightly more difficult to handle. :) I prefer the AK. I
guess the US government is now alert to this conversation. ;)
ML
http://milambda.blogspot.com/

Alphanumeric Primary Key

Is it possible to have an auto increment alphanumeric primary key eg A1, A2, A3
Thanks
Paul.

There are two ways to create it and I have posted both in this thread, read my posts except the RDBMS history and you can create one. In ANSI SQL and Oracle it is called SEQUENCE. Hope this helps.
http://forums.asp.net/953564/ShowPost.aspx

Alphanumeric Autonumber Primary Key

Hi there,
The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002

Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?the only sane way of doing it is to have an ordinary integer identity column, then produce the alphanumeric value in a view

create view myview as
select 'ABC'+right(cast(pkey as varchar(9)),4) as myalnumkey ...

Allow database to assume primary role'

Can any one explain this..
Enable the 'Allow database to assume primary role'
Thanks
NOOR
Hi Noor,
This term is used in Logshipping.
Allow database to assume primary role :-
This lets the destination database become a new log shipping source database
and thus permits a possible future role reversal between the primary and
secondary servers. When you select this option, specify the secondary
server's transaction-log file share as the location for transaction-log
backups from the new source database.
Thanks
Hari
MCDBA
"Noor" <noor@.ngsol.com> wrote in message
news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
> Can any one explain this..
> Enable the 'Allow database to assume primary role'
> Thanks
> NOOR
>
|||Thanks Hari.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:#WHrWKHfEHA.2812@.tk2msftngp13.phx.gbl...
> Hi Noor,
> This term is used in Logshipping.
> Allow database to assume primary role :-
> This lets the destination database become a new log shipping source
database
> and thus permits a possible future role reversal between the primary and
> secondary servers. When you select this option, specify the secondary
> server's transaction-log file share as the location for transaction-log
> backups from the new source database.
> Thanks
> Hari
> MCDBA
>
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:O4anFj5eEHA.556@.tk2msftngp13.phx.gbl...
>

All Tasks->Restore Database....hangs forever

We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
two secondary servers. I performed a role change from the primary to the
secondary running script "sp_change_secondary_role" on secondary server.
Everything went fine. I later removed log shipping from all servers.
Now, I try to Restore a backup of the database on the ex-primary server by
right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
db, created an empty instance and tried to restore again. It hangs. I
restored using t-sql, but something is obviously wrong.
For what reason could sql exhibit this behavior?
Thank you for your help
JC
On Tue, 7 Jun 2005 06:47:04 -0700, John C <John
C@.discussions.microsoft.com> wrote:

>We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
>two secondary servers. I performed a role change from the primary to the
>secondary running script "sp_change_secondary_role" on secondary server.
>Everything went fine. I later removed log shipping from all servers.
>Now, I try to Restore a backup of the database on the ex-primary server by
>right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
>db, created an empty instance and tried to restore again. It hangs. I
>restored using t-sql, but something is obviously wrong.
>For what reason could sql exhibit this behavior?
>Thank you for your help
Sorry not a solution but just to confirm I have also seen this
behaviour of restore hanging enterprise manager (although these
servers were not log shipping) - using the SQL command equivalents in
Query Analyzer to restore provide a workaround.
|||John,
Do you ever cleanup the backup entries in tables in MSDB? Your request is
looking at all the entries in MSDB about this database. Probably should not
have the results that you see though.
Chris Wood
"John C" <John C@.discussions.microsoft.com> wrote in message
news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> to
> two secondary servers. I performed a role change from the primary to the
> secondary running script "sp_change_secondary_role" on secondary server.
> Everything went fine. I later removed log shipping from all servers.
> Now, I try to Restore a backup of the database on the ex-primary server by
> right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> the
> db, created an empty instance and tried to restore again. It hangs. I
> restored using t-sql, but something is obviously wrong.
> For what reason could sql exhibit this behavior?
> Thank you for your help
> JC
|||Chris, this might be the problem. I just remembered that I had the same
problem when I tried to Delete the database by right-clicking. When I
selected "Delete backup history" the system hung. When I tried again and
unchecked this selection it did go hrough.
What tables do I have to clean though? Where in msdb is the backup history
stored?
Thank yu for your prompt reply
John C
"Chris Wood" wrote:

> John,
> Do you ever cleanup the backup entries in tables in MSDB? Your request is
> looking at all the entries in MSDB about this database. Probably should not
> have the results that you see though.
> Chris Wood
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
>
>
|||There are 4-6 tables that need to be cleaned up. Backupfile, backupmediaset,
backupmediafamily, backupset. Maybe a couple more, you will see when the
FKeys get in the way.
"John C" wrote:
[vbcol=seagreen]
> Chris, this might be the problem. I just remembered that I had the same
> problem when I tried to Delete the database by right-clicking. When I
> selected "Delete backup history" the system hung. When I tried again and
> unchecked this selection it did go hrough.
> What tables do I have to clean though? Where in msdb is the backup history
> stored?
> Thank yu for your prompt reply
> John C
> "Chris Wood" wrote:

All Tasks->Restore Database....hangs forever

We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
two secondary servers. I performed a role change from the primary to the
secondary running script "sp_change_secondary_role" on secondary server.
Everything went fine. I later removed log shipping from all servers.
Now, I try to Restore a backup of the database on the ex-primary server by
right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
db, created an empty instance and tried to restore again. It hangs. I
restored using t-sql, but something is obviously wrong.
For what reason could sql exhibit this behavior?
Thank you for your help
JCOn Tue, 7 Jun 2005 06:47:04 -0700, John C <John
C@.discussions.microsoft.com> wrote:

>We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary t
o
>two secondary servers. I performed a role change from the primary to the
>secondary running script "sp_change_secondary_role" on secondary server.
>Everything went fine. I later removed log shipping from all servers.
>Now, I try to Restore a backup of the database on the ex-primary server by
>right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted th
e
>db, created an empty instance and tried to restore again. It hangs. I
>restored using t-sql, but something is obviously wrong.
>For what reason could sql exhibit this behavior?
>Thank you for your help
Sorry not a solution but just to confirm I have also seen this
behaviour of restore hanging enterprise manager (although these
servers were not log shipping) - using the SQL command equivalents in
Query Analyzer to restore provide a workaround.|||John,
Do you ever cleanup the backup entries in tables in MSDB? Your request is
looking at all the entries in MSDB about this database. Probably should not
have the results that you see though.
Chris Wood
"John C" <John C@.discussions.microsoft.com> wrote in message
news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> to
> two secondary servers. I performed a role change from the primary to the
> secondary running script "sp_change_secondary_role" on secondary server.
> Everything went fine. I later removed log shipping from all servers.
> Now, I try to Restore a backup of the database on the ex-primary server by
> right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> the
> db, created an empty instance and tried to restore again. It hangs. I
> restored using t-sql, but something is obviously wrong.
> For what reason could sql exhibit this behavior?
> Thank you for your help
> JC|||Chris, this might be the problem. I just remembered that I had the same
problem when I tried to Delete the database by right-clicking. When I
selected "Delete backup history" the system hung. When I tried again and
unchecked this selection it did go hrough.
What tables do I have to clean though? Where in msdb is the backup history
stored?
Thank yu for your prompt reply
John C
"Chris Wood" wrote:

> John,
> Do you ever cleanup the backup entries in tables in MSDB? Your request is
> looking at all the entries in MSDB about this database. Probably should no
t
> have the results that you see though.
> Chris Wood
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
>
>|||There are 4-6 tables that need to be cleaned up. Backupfile, backupmediaset
,
backupmediafamily, backupset. Maybe a couple more, you will see when the
FKeys get in the way.
"John C" wrote:
[vbcol=seagreen]
> Chris, this might be the problem. I just remembered that I had the same
> problem when I tried to Delete the database by right-clicking. When I
> selected "Delete backup history" the system hung. When I tried again and
> unchecked this selection it did go hrough.
> What tables do I have to clean though? Where in msdb is the backup history
> stored?
> Thank yu for your prompt reply
> John C
> "Chris Wood" wrote:
>