Merge Replication Filtering Columns
May 25, 1999I think I want to use merge replication but filter out one column but when I'm setting up the publication I only see row filtering available?
Is this just not possible?
Thanks for any help
Dan
ADVERTISEMENT
Using Row Filtering In SQL Mobile Merge Replication
Oct 25, 2007Hi,,
I understand that it is possible to set filters dynamically using functions 1) SUSER_SNAME() 2) and HOST_NAME().
SUSER_SNAME() returns the login credentials used in the subscritption.
HOST_NAME() returns the host machine and can be overloaded with buisiness information.
My application should work as below;
1) User enters the login credentials.
2) Some information as the User name passed to server and if the user name is valid , the rows related to this particular user get downloaded to device.
A new user is added directly in the Users table in master database.
My questions are :
1) If I have 3000 users , should I create 3000 subscriptions with 3000 HOSTName or Login credential information to differentiate btween users?
2) If yes , other than using the wizard is there any scripts available to create large number of subscription?
3) If add subscription programatically , should I re-initialise subscription for each new user which is assigned with a diferrent host name value?
my current code looks like this
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @"http://100.000.000.000/test/sqlcesa30.dll";
repl.Publisher = @"something";
repl.PublisherDatabase = @"something";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"something";
repl.Publication = @"somethingPublisher";
repl.Subscriber = @"something";
repl.HostName = @"newuser"; // The user name entered in the login form assigned to Hostname property.
repl.SubscriberConnectionString = @"Data Source=""Program Files esthost.sdf"";Max Database Size=128;Default Lock Escalation =100;";
try
{
repl.ReinitializeSubscription(true);
repl.Synchronize();
repl.Dispose();
}
Please advise
-Aneesh
Merge Replication Columns Limit??
Dec 4, 2006Hi,
I am experiencing a wired problem withe merge replication and SQL 2000 SP4.
We had replication working for 4 years without a problem, one of the table that we replicate has been growing in columns, now it has 55 columns.
I have noticed a problem, when I update the last column on the table and waits for the change to be updated on the subscriber the change is not there. But if I replicate the first 40 columns it works.
Changes made:
1) Upgraded to SP4
2) Added some extra fields using sp_addreplcolumn
Is this a known bug? Any ideas?
Regards
Addition Of Columns In Table, Which One Use In Merge Replication.
Nov 29, 2004Dear all participants,
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
What is impact on replication?
Let me know step by step.
Thanks in advance.
Thanks R.Mall
SQL 2012 :: Merge Replication - Identity Columns
Nov 3, 2015I was reading this:
"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."
I don't understand...
If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?
Suppose I have a this table:
1 Name1
2 Name2
3 Name3
Column 1 is identity field and column 2 the name of employees.
If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?
What about if the identify fields is a primary key?
Merge Replication With Anonymous Subscribers And Identity Columns
Dec 21, 2005Hi,
I read the BOL on how the publisher will had out identity ranges to subscribers, but it was not clear if this was also the case for anonymous subscribers. Will merge replication with identity columns work with anonymous subscribers that sync via HTTPS?
Thanks,
Darrell Young
Filtering Individual Columns In A Table
May 5, 2008I'm designing a sales report. The user would like to be able to look at individual customers against total sales in a report.
I have a parameter that will allow the user to select from a list of customers. Then I need a column that will show that customers sales and the next column will show total sales. If I filter the data set by Customer I will only get that customer's sales. Can I filter on individual columns?
I tried something like
=Sum(IIF(Fields!Name.Value=Parameters!VendorName.Label,Fields!Coil_Weight.Value/code.Expon(Parameters!Units.Value),0))in the expression but that doesn't seem to work.
Filtering In Merg Replication
Feb 17, 2007Dear ...
This is one of sql server 2005 's users. I put filter for one of my merge replicate articles .But it dosent work correctly .plaese help me to set my article correctly.
Best regards
R.Marandi
Query Questions, Filtering Or Sorting Of Columns
Oct 26, 2004I have a database of automobiles. I have many many columns, it is blank of course until I can start filling it with information.
I will have four main rows. Yearnum, Make, Model, and VehicleStyle columns
I will use Honda Accord as an example.
Honda has made the Accord since probably the 80's
I know that if I
SELECT yearnum
FROM YearNum
ORDER BY yearnum
I am using C++ Builder too..
it will put all my years in order, in a combo box. But I believe it will also have duplicates, like for example They may have made a 1995 Honda Accord and then made a 1995 Honda accord LS which may have different wiring colors and speakers sizes than the regular accord.
Is there anyway to filter out multiple years, so I could just have the regular order of years?
Replication - Column Filtering Confusion
Jul 3, 2003I'm learning replication (snapshot for now) and was trying filtering & got strange results.
SQL 2K sp2
Publisher: Table1
FieldA
FieldB
FieldC
Subscriber: Table1
FieldA
FieldB
FieldC
But now, I decide after the fact to filter FieldC. The subscriber Table still has 3 fields, but the publisher data from Field 1 + 2 gets shifted across all 3 fields at the subscriber.
If I manually drop the table at the subscriber, then next time the job runs, it recreates the table with just 2 fields and looks good.
Is there some way to set replication so that it will drop & re-create the subscriber table automatically if the filter changes ? Or am I missing something else in my understanding ?
Under "Default Table Article Properties - Snapshot" the option "DROP the existing table & recreate it" is checked. When does that apply ?? Just when first set up ?
Any enlightenment is appreciated.
Trans Replication - Filtering && Deleting
Jul 14, 2003Here's what I'm trying to do (Maybe there's a better way)
I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"
That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.
Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot
Replication :: Difference Between Snapshot And Transaction And Merge Replication?
May 26, 2015What is the main difference between snapshot and transactional and merge replication?
View 5 Replies View RelatedTransact SQL :: Filtering Rows Based On Multiple Values In Columns?
Sep 22, 2015In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
Complex Join Replication Filtering Is Not Working In 3.5 Beta
Sep 11, 2007Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?
Aug 30, 2007Hi 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).
Merge Replication Set Off Transactional Replication
Oct 9, 2007I 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...
Identity Columns And Date Columns On Transactional Replication
Sep 16, 2006Hi,
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Any help would be appreciated.
Thanks,
-Niraj
Merge Columns
Jun 20, 2006I have 2 columns 'Col1' (char) and 'Col2' (integer)
I'd like to combine these 2 columns and put the result in 'Col3' (char)
please help!!!
Merge Two Columns
Jun 16, 2007I want to combine a column with Id and other with Name.
For eg: If Id is 01 and Name is 'Tom', the output should be 01-Tom.
I am not able to execute it using || or CONCAT since Id has the data type int and Name is a string.
Do you have a solution?
`RG`
Merge 2 Columns Into 1
Dec 7, 2007hi guys,
i have a table with 2 columns say:
First_Name Last_Name
David Reinjal
i need to merge these 2 columns and put into a single column with a ','(comma) seperated. it will look loke this:
column_name
David, Reinjal
how can i do this? can anybody help me in this?
thanks,
David
How Can I Merge Both Columns?
Aug 3, 2007I have a column called email_address in table: mailing_list and another column called member_email in table: members and I want to do a SELECt to list the result of both columns in a new column
View 8 Replies View RelatedMerge Two Columns
Feb 4, 2008
Hi!
I'm using IS with SQL Server 2005.
I need to know how to merge two columns (or more) into a single on as output.
For example:
#ID#Value1#Value2#
would be the input table. Now I want to map the column Value1 as 'Value' - as well as the column 'Value2' so there is only one output - and two rows generated by one row of the originate table.
I tried to map those columns to the same name, but IS refused that. My aim is to transform data from an old table to a new, relational one where all those values have to fit into relational tables (n:m relations).
How do I do that?
Thank you in advance!
Merge 2 Columns To One?!
Nov 7, 2006Hello everybody!
I´m trying to put together two columns of integer into one. They are coming from the same source table, so the "merge"-Task doesn´t work. This is what they look like:
column1: <123>
column2: <0123456>
The result should be:
merged_column: <1230123456>
Is there a task for that kind of operation or do I have to write a script? I would like do do that within the data flow.
thanks in advance!
Is It Possible To Merge 2 Columns Into 1 In SQL Server
Nov 2, 2007Hi..
is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be
ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ...... until 15
BB 1245.45 CT some amount .....
The 2 letter character may change prob for each file.. at leat i know of somethem may change
So while i transfer the data to the production database can is it possible to do some thing like
COL1 BB 1245.45
COL2 CT 12456.12 etc..
Any help will be appreciated.
Regards
Karen
Merge 2 Columns From The Same Table
Sep 19, 2006Hi *,
I have a table with 2 columns A1 and Firm. A1 holds "dirty data" and there are no NULL values. Firm holds "clean data" for some of the rows in A1 but not for all. So there are quite a few NULL values in this column. I want to replace the value in A1 if there is any data in Firm (value <> NULL).
Rigth now, I solve this issue with a simple VB.NET script:
Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' If Row.Firm_IsNull Then Row.Standard = Row.A1 Else Row.Standard = Row.Firm End If ' End SubEnd Class
Any ideas how to solve this issue without custome code?
Thanks!
One Way Merge Replication
Feb 14, 2002Hi 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
Merge Replication
Aug 12, 2002Hi,
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.
Merge Replication
Jul 31, 2000I 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!
Merge Replication
Jan 22, 2001I 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
Merge Replication
Dec 19, 2000Hi,
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.
Merge Replication
Sep 16, 1999Hi 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
Merge Replication
Sep 12, 2003I 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
Merge Replication (Again)
Feb 23, 2004Hi 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