Conflicts table

Is is safe to delete the records in the conflict_ table for a merge
replication?
I have a lot of conflicts logged there that I just don't care about (I would
like to not record the conflict to start, but i think that's not possible).
Can I just delete the records?
Thanks, Jos Araujo.
These are purged IIRC after the retention period. It is safe to delete them,
but I would not do it - just cause I hate to mess with replication innards.
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
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:uDDhNHTCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Is is safe to delete the records in the conflict_ table for a merge
> replication?
> I have a lot of conflicts logged there that I just don't care about (I
would
> like to not record the conflict to start, but i think that's not
possible).
> Can I just delete the records?
> Thanks, Jos Araujo.
>
|||Great... thanks for the answer.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uS09OCWCFHA.1524@.TK2MSFTNGP09.phx.gbl...
> These are purged IIRC after the retention period. It is safe to delete
them,
> but I would not do it - just cause I hate to mess with replication
innards.
> --
> 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
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:uDDhNHTCFHA.3376@.TK2MSFTNGP12.phx.gbl...
> would
> possible).
>

conflict between (cascade) DELETE Trigger and Foreign Key Constrain

I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):

CREATE TABLE [Users] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FS_Password] [nvarchar] (32) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [UserRights] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
[FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
(
[FS_Username]
) REFERENCES [Users] (
[FS_Username]
)
) ON [PRIMARY]
GO

Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:

CREATE TRIGGER InsertUserRights ON Users
FOR INSERT
AS
BEGIN
INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
END

CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM Users)
END

Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:

Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')

Delete from Users

... first command will succede, but second one will fail with message:

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
The statement has been terminated.

Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).

tnx a lot,
DejanSorry, second trigger should be:

CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM deleted)
END

This is just the correct trigger, it doesn't solve the problem in hand...

Dejan

Conflict

Hello
How can i delete the conflict tables rows? any SP command is there?
Sam
Sam,
there is a stored proc : sp_deletemergeconflictrow
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Configuring Database Mail in SQL Server 2005

Hi

I have created profiles in my database as mentioned below. now i want to delete the profile created in database.

can some one tell me how to proceed furthur.

i need to delete them permenantly.

EXECUTE msdb.dbo.sysmail_add_account_sp @.account_name = 'MyMailAccount', @.description = 'Mail account for Database Mail', @.email_address = 'makclaire@.optonline.net', @.display_name = 'MyAccount', @.username='makclaire@.optonline.net', @.password='abc123', @.mailserver_name = 'mail.optonline.net' --

EXECUTE msdb.dbo.sysmail_add_profile_sp @.profile_name = 'MyMailProfile', @.description = 'Profile used for database mail' --EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @.profile_name = 'MyMailProfile', @.account_name = 'MyMailAccount', @.sequence_number = 1

-

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @.profile_name = 'MyMailProfile', @.principal_name = 'public', @.is_default = 1 ; Regardskrishna

refer the below link which contains the list of stored procedures in database mail,

http://msdn2.microsoft.com/en-us/library/ms177580.aspx

i think you can make use of sysmail_delete_profile_sp stored procedure........

sysmail_delete_profile_sp { [ @.profile_id = ] profile_id | [
@.profile_name = ] 'profile_name' }
[, [ @.force_delete = ] force_delete ]

|||

Hi Deepak,

Thanks for the reply,

how do i configure email notification in SQL 2005 server, so that i get notification on two different mail id,s

ex:

krishna@.abc.com and krishna@.abchelp.com

regards

krishna

|||

you can make use of,

EXEC msdb.dbo.sp_send_dbmail
@.profile_name = 'AdventureWorks Administrator',
@.recipients = 'danw@.Adventure-Works.com',
@.body = 'The stored procedure finished successfully.',
@.subject = 'Automated Success Message' ;

if you want to mail multiple receipients just use semicolon in the

@.receipients='krishna@.abc.com' ;'krishna@.abchelp.com'

refer the below link for further assistance,

http://msdn2.microsoft.com/en-us/library/ms190307.aspx