Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 RepliesHi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
Hi I have tried to add a new column to a publication table using sp_repladdcolumn and also directly add the column under Publication's properties screen.
I am able to add the column using both method. But changes to the newly added column either at the publisher or subscription databse will not get replicated.
Have I miss out something? or do I need to reinitialise the subscription to make changes in the newly added column to be replicated?
Hi,
I have a table used in Merge Replication. If i add a new column in that table it gives me an error for tables in which either this table is a key or this table has key from other tables "Error validating the default for column 'rowguid'." Also for views i'm getting an error
"View 'dbo.vw_TestCaseBasic': indexes and schema binding will be removed."
If i proceed further i get an error and m not able to add the column.
"It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted."
Is there any solution for the same?
Regards,
Ruchir Jain
Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?
Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
Hi
I'm working with merge replication between Sql Server 2005 and Sql Server 2005 Mobile. I'm using dynamic filtering by function HOST_NAME (... where Table.FilteredColumn = HOST_NAME())
When I'm trying to chagne values from filtered column on the client side (Table.FileteredColumn) I get error message that the column is read-only.
How can I change data in filtered column? Is this possible?
Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedWhat query should I run?
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
Thanks a lot!
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
Hello Everybody ..
Greetings !!
I wanted information on Transactional Push replication between SQL Servers . I have one table on SQL Server 6.5 and there are certain text in the table with single quotes .
When i replicate this table to SQL Server 7.0 the text appears in double quotes on SQL Server 7.0 .
Can anyone help regarding this problem ? Has it something to do with version problem .? Where can i get more information on this issue ?
Many thanks . An early response will be highly appreciated .
Hello Mr. Paul...
Thank you so much for your prompt response . Is it possible to change the settings of the Quoted Identifiers ? How can we do it in SQL Server 6.5 and SQL Server 7.0 ? Do you have any info. on the same . ?
Thanks once again .
Yatin.
Hi, I'm getting this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not retrieve replication information for table 735393739. Verify that the table has a primary key, and then rerun the Log Reader Agent.Please help me!
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
Hi I have to questionsproblems I would need help with. First I am creating a three level merge replication. I mean I have a main
publisher with its distributor, a subscriber/publisher and also with
it's own distributor and lastly a subscriber.
I start by creating the relation between subscriber/Publisher and the
subscriber and than I move up to the main Publisher and
SubscriberPublisher relation. After this second merge I get the
following errors and the articles in my SubscriberPublisher are
lost...
The merge process could not retrieve article information for
publication 'I-Test-Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
---------------------------------------------------------------------------------------------------------------
Cannot insert the value NULL into column 'tablename', table
'@tmp_table'; column does not allow nulls. INSERT fails.
(Source: DEVSERVER3MSDE (Data source); Error number: 515)
---------------------------------------------------------------------------------------------------------------
Failed to allocate new identity range.
(Source: DEVSERVER3MSDE (Data source); Error number: 21197)
---------------------------------------------------------------------------------------------------------------
Secondly
I have set up a two level merge replication with a publisher and a
subscriber and had modified the structure of a table in the publisher
with sp_repladdcolumn. During the day all was fine I continuously
tested it by adding more columns, droping columns and testing the
replication itself with data entry, but when I got back
the next morning my test table and all other table I presume had lost
its data and structure. The conflict table for the corresponding test
table had kept it's structure but no data were present since no
conflict had occured. This is a recuring problem that some colleagues
had.
Does anyone have any idea what might cause this problem and why it
happenned.
Thanx
Hello
We moved SQL Server 2000 Databases to SQL Server 2005 Instance. If we change the Database compatibility level from 80 to 90, shall Replication will Fail? or any consequencies will arise? please advice. Thanks,
Okay, I'll admit it: I am not a Guru. Far from it. So here is one of the zillion tasks I cannot do - unfortunately it is one that I need to have done... :
I need a recordset containing the Name, Datatype, Size, Precision, and Name_of_parent_table of all columns in all non-system tables in a given database.
Most likely this can be accomplished with one or two SPs, but which ones? And how?
Is there any Kind Soul out there, perhaps?
Peter
Hi,
Is it possible to retrieve column information alone in the sp_columns command? I saw that it has an additional option "@column_name". But when I include it in my sql, it doesn't work the way I had expected it to..
I had tried sp_columns @table_name='mytable', @column_name='COLUMN_NAME'
I expect just the column names alone in the resultset. What am I doing wrong here?
Thanks and Regards,
Celia.
This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedThis is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
Hi,
I read some where that replication has two types conflict resolution, 1. row based and 2. Column based...
If I am right...
Can any one point me how to find out this option and how to set it up....
Thanks,
Mohammed.
Hi all,
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
Thank you for all your time in advance.
Aziz
I have successfully tried merge replication on single server with 2 databases.
now i want to do the same with different servers,
when i create pull subcription on server 2 which user account should I use?
it is giving log in failure
i tried using windows admin account and also the 'sa' account.
please help me out
thanks
Hi All,
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices -
1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
Is there any way I can make this work??
Niben
Hi,
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
-whitebelt
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] (
[PHP_Pub_ID] [uniqueidentifier] NOT NULL ,
[PHP_Data_ID] [uniqueidentifier] NOT NULL ,
[PubID] [uniqueidentifier] NOT NULL ,
[UserID] [uniqueidentifier] NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pub_Year] [datetime] NULL ,
[Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD
CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid]
GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY]
GO
Thanks,
Chanthol
Hi
Is it possible to modify or add new fields in sql 2000 when the instance
became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
Thanks
Pheckz