All Rows Are Not Replicated Correctly (merge)
Sep 28, 2007
Hi all! Hope you can help us with this one. We installed SP 2 of Sql server 2005 few months ago, and that caused a strange problem with merge replication.
We have about 150 subscribers in one merge publisher, and 200.000 new rows generated each day (retail system). Most tables are filtered.
Replication and filtering works fine, except 0,1% --> 0,2% of rows are not replicated correctly. These "problem-rows" are always replicated correctly to the publisher database, but publisher can't always replicate these rows further to other subscribers. Before SP2 everything worked fine.
Same problem occurs with filtered and non-filtered tables.
SQL Server shows no errors or warnings, everything SEEMS to work fine.
We have to make "dummy"-updates (like update SALES set id=id) in publisher database, to wake up the replicator. And always after these dummy updates all problem-rows will be replicated correctly from the publisher to subscribers.
Why do we have to "wake up" the replicator time to time with dummy updates? Is there a bug in SP2?
View 3 Replies
ADVERTISEMENT
Apr 6, 2004
Is there a way to move a SQL replicated database from one server to another without dropping the publication?
I have SQLCE setup with SQL server 2000; I want to redo the server but needs to move my replication to a temp server and then back.
The problem is that I do not want to drop my subscription and recreate it…it needs to stay the same.
View 2 Replies
View Related
Jun 27, 2007
How can I list all of the Subscriptions in a CE Database that was created with the .Net System.Data.SqlServerCe.SqlCeReplication.AddSubscription method?
After I create the CE database in an sdf file, I can connect to it with SQL Servr Mgmt Studio, then see the list of Subscriptions in the Object TreeView, under "Replication"
But I can't find that Subscription list in any properties or methods of System.Data.SqlServerCe.SqlCeReplication or System.Data.SqlServerCe.SqlCeConnection
Should I be looking in RMO features like Microsoft.SqlServer.Replication??
We want to confirm that a local CE database was last synced from the expected SQL Server database by checking the Publisher property of each subscription in the CE database.
Thanks
View 3 Replies
View Related
May 15, 2007
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
View 1 Replies
View Related
May 15, 2007
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
View 1 Replies
View Related
Mar 31, 2007
And by correctly, I mean the way it *should* match, of course.I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:OLE DB Source 1 (table1)
Sel Name Order JoinKeyno UniqueID 1 yesyes Column1 0 no
...
OLE DB Source 2 (table2)
Sel Name Order JoinKeyyes ID 1 yesyes Columns 0 no
...
There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?
View 6 Replies
View Related
Dec 29, 2004
We are using merge replication and it is working fine. We are looking for increase in on numeric column 10,5 instead of 7,2. How I can do it?
Any suggession highly appreciated by us.
Thanks
View 1 Replies
View Related
Aug 20, 2015
How Do we find how many ROWS REPLICATED to subscriber at specific interval ?
View 3 Replies
View Related
Dec 28, 2006
I have 1 sql server 2005 database in Server A that is publishing a table to another database in Server B using the transactional method.
It is possible for me to delete all the rows in the database located at the publisher at the same time the subscriber pulls them?
Thanks.
View 1 Replies
View Related
Oct 24, 2013
We had an issue recently where a (transactional) replicated table was replicating data as expected.
Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.
We have pretty much confirmed that users did not delete those rows.
Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.
We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.
View 1 Replies
View Related
Oct 22, 2007
I have the following query in an ExecuteSQL Task:
Insert Into Table2
Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
View 4 Replies
View Related
Mar 25, 2008
Does anyone have any idea how to construct a select statement querying a temp table that contains the following data:
Code|Jan-Apr| May-Aug| Sep-Dec
----------------------------------------
ABCD|250.00|NULL|NULL
ABCD|NULL |889.56|NULL
ABCD|NULL |NULL|1233.67
... that will merge the results as follows?
Code|Jan-Apr| May-Aug| Sep-Dec
----------------------------------------
ABCD|250.00| 889.56| 1233.67
Your help would be greatly appreciated!
Thanks.
View 3 Replies
View Related
Jul 25, 2014
I am trying to build a SQL select query, but am struggling a little bit.I have a table with multiple inventory transactions, each with an ID and weight.
For example:
proc_packxacts
--------------
ID SysProg Weight
210359 271 986
210359 238 985
210359 61 984
210359 5 983
954679 271 1000
354852 271 568
354852 238 523
354852 59 521
354852 5 520
What I want to do is write a SQL query that returns records where there is a transaction for SysProg = 238, but return the weights for some of the other transactions in columns on the same row.Basically, from the above data set, what I would like to return is:
ID Weight (271) Weight (238) Weight (61 or 59)
210359 986 985 984
354852 568 523 521
How to start this?
View 2 Replies
View Related
Jul 23, 2005
I want to create a stored procedure that will merge columns from tworows that contain duplicated contacts.I have can easily identify the duplicates and extract the UniqueIDs asparameters, but I can't figure out how to construct the actual updateSQL.@KeeperID int,@DupeID intUpdate ContactsSETa.Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),a.Info2 = LEFT(TRIM(IsNull(a.info2,'') + ' ' IsNull(b.Info2,'')),255),etc, etc...FROM(here's what I can't figure out)Contacts a ID = @KeeperIDContacts b ID = @DupeID
View 2 Replies
View Related
Dec 20, 2007
Hi
I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:
ID,CaseID,Note
each CaseId can have several rows.
Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.
I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?
View 2 Replies
View Related
Aug 29, 2006
Hi Guys,
Can anyone please explain and show an example of how to merge data from one row to another using in SQL 2000
thanks in advance
View 4 Replies
View Related
Apr 6, 2008
I have 3 input sources, date, name and text. What SSIS transformation could I use to merge these 3 rows into one single row. Example:
10/16/2007
John Doe
Text.......................................
Result wanted :
10/16/2007 John Doe Text.......................................
Thank you so much fro your help.
Tara
View 9 Replies
View Related
Aug 2, 2006
I am writing a database system which recieves information parsed from various data formats. These data formats may or may not be complete, and as such some rows in the database can have gaps.
The input formats may contain reference to the same row (in this case the same Company record) but hold different facts about that company.
Eg one message may have name, phone and fax, whereas another message may contain name, address and website.
I need to be able to insert new companies into the database OR update current records with extended data as relevant, ive been looking for UPSERT or MERGE queries in SQL Server but i cant find any useful resources explaining its use.
Alternativly i'd like to be able to condense potential duplicates into single rows, eg:
NamePhoneEmailnullNamePhonenullWebsite
which would combine into one row where the null values get set by values derived from other similar rows:
Name PhoneEmailWebsite
Any help at all would be really appreciated
Thanks,
Toby
View 6 Replies
View Related
Sep 24, 2015
I have a table that has columns like following
trans_code
gl_code
12qw3
a1235
12qw3
a6578
12qw3
t531
e34g6
gt25
e34g6
y7327
e34g6
v23409
Is there an easy way to turn this into format below ??
trans_code
gl_code_1
gl_code_2
12qw3
a1235
a6578
12qw3
a1235
[code]....
View 5 Replies
View Related
Apr 24, 2008
Hi,
I have a problem with a Merge Join providing no output (when it should have 1890 rows). My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination. I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below). I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below). Then the two remaining legs use a Merge to get my destination output (see Level 3 below).
I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation. The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows. Both Merge Joins are identical. The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in. Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join), but C2 baffles me with 0 rows of output (when it too should have 1890). I receive no Ouput errors and the execution completes showing all green.
Level 1
Multicast (output A1, A2) [667 rows]
Multicast (ouput B1, B2) [1890 rows]
Level 2
Merge Join (input A1, B1) --> (output C1) [1890 rows]
Merge Join (input A2, B2) --> (output C2) [0 rows]
Level 3
Merge (input C1, C2) --> (output D1) [1890 rows]*
I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck. Any help/ideas would be appreciated.
Thanks,
Devin
* Should be 3780 rows
View 4 Replies
View Related
May 13, 2008
Hi,
I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3
NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2
Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW
Thank you.
View 2 Replies
View Related
May 15, 2015
I have this stored procedure which generates the following output.
The query which generates the output from the stored procedure is as follows:
SELECT tat.[agent-id],
tat.[agent-guid],
tat.[agent-name] AS [AgentName],
tw.TagWidgetID,
tw.TagWidgetName,
t.TagID,
core.fnInitialCapitalization(t.DisplayName) AS TagName,
[Code] ....
How can I join the output rows only if the TagWidegetIds are same but the TagValues, TagIndex and ValueIndex columns are different?
View 3 Replies
View Related
Nov 26, 2014
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
View 3 Replies
View Related
Dec 24, 2014
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
View 2 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[Code] ....
View 7 Replies
View Related
Aug 23, 2006
I am running a simple merge replication in SQL Server 2000. I have one database that is the publisher, and a second database that is the subscriber. When I add a new row to the subscriber it will replicate to the publisher as expected. However, the new row at the subscriber will then be deleted without explanation. The row will remain at the publisher though.
Does anyone know why it is doing this?
View 8 Replies
View Related
May 26, 2007
We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.
Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.
I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.
The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database). But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.
This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.
Any ideas?
Thanks,
Matt
View 1 Replies
View Related
Jul 8, 2015
I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.
CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical
I'm using T-SQL to generate the column type. Below is my DDL.
USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)
[code]....
View 2 Replies
View Related
May 21, 2015
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,
[Code] ....    Â
Is it possible to return the data in the following format merging 2 lines of data into one output:
 SiteID  ReadStartDate ReadEndDate    ReadStartIncome    ReadEndIncome
L0020Â Â Â Â 19/05/2015 05:00Â 20/05/2015 05:00Â Â Â 85.98Â Â Â Â 145.98
L0101Â Â Â Â 19/05/2015 22:07Â Â Â 20/05/2015 22:14Â Â Â Â Â Â Â Â Â 1,936.08Â Â Â Â Â Â 1,438.89
L0102Â Â Â Â 20/05/2015 21:16Â Â Â 19/05/2015 21:48Â Â 143.65Â 243.5
I am using SQL 2008 R2.
View 12 Replies
View Related
May 13, 2015
I have a set of data which contains individual logon and logoff data. The table is as follows:
AGENTID, EVENTTIME, CURRENTSTATE
1234,      2015-05-12,   15 (For Logon) or 25 (For Logoff)
I'm hoping to extract this data as follows:
AGENTID, LOGON DATE/TIME, LOGOFF DATE/TIME, DURATION
View 19 Replies
View Related
Feb 19, 2015
I need the requirements of merging multiple rows of same ID as single row.
My Table Data:
IDLanguage1Language2Language3Language4
1001NULL JAPANESENULL NULL
1001SPANISH NULL NULL NULL
1001NULL NULL NULL ENGLISH
1001NULL NULL RUSSIAN NULL
Required Output Should be,
IDLanguage1Language2Language3Language4
1001SPANISH JAPANESERUSSIAN ENGLISH
How to achieve this output. Tried grouping but its not working also producing the same result.
View 1 Replies
View Related
Jul 8, 2015
Merge two rows into one (conditions or Pivot?)
I have Temptable showing as
Columns:EmpName, Code, Balance
Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10
I want to insert the above temp table to another table with column names defined below like this
Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10
Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.
View 3 Replies
View Related
Aug 12, 2015
I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .
CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )
[code].....
View 3 Replies
View Related
