Merge Large Tables

What is the best way to merge two really large tables (7,000,000 rows each,
and very wide)?
The table definitions are the same for both tables.
Right now I am using a insert into statement with a selection from one table
at a time, but it takes way too long. I don't need it to be logged. A all
or nothing result is fine for me. I don't think DTS is an option because I
need to run this from a C# app. I think my only other option is using the
BCP api to select the data and load it into the new table, but this just
seems like the wrong way to go.
Any other ways to go with this?
Shawn
Select *
into [NewTable]
From
Select * [Table1]
Union All
Select * [Table2]
"Shawn Meyer" <me@.me.com> wrote in message
news:O4GhS6QQFHA.580@.TK2MSFTNGP15.phx.gbl...
> What is the best way to merge two really large tables (7,000,000 rows
> each,
> and very wide)?
> The table definitions are the same for both tables.
> Right now I am using a insert into statement with a selection from one
> table
> at a time, but it takes way too long. I don't need it to be logged. A all
> or nothing result is fine for me. I don't think DTS is an option because
> I
> need to run this from a C# app. I think my only other option is using the
> BCP api to select the data and load it into the new table, but this just
> seems like the wrong way to go.
> Any other ways to go with this?
> Shawn
>

Merge Large Tables

What is the best way to merge two really large tables (7,000,000 rows each,
and very wide)?
The table definitions are the same for both tables.
Right now I am using a insert into statement with a selection from one table
at a time, but it takes way too long. I don't need it to be logged. A all
or nothing result is fine for me. I don't think DTS is an option because I
need to run this from a C# app. I think my only other option is using the
BCP api to select the data and load it into the new table, but this just
seems like the wrong way to go.
Any other ways to go with this?
ShawnSelect *
into [NewTable]
From
Select * [Table1]
Union All
Select * [Table2]
"Shawn Meyer" <me@.me.com> wrote in message
news:O4GhS6QQFHA.580@.TK2MSFTNGP15.phx.gbl...
> What is the best way to merge two really large tables (7,000,000 rows
> each,
> and very wide)?
> The table definitions are the same for both tables.
> Right now I am using a insert into statement with a selection from one
> table
> at a time, but it takes way too long. I don't need it to be logged. A all
> or nothing result is fine for me. I don't think DTS is an option because
> I
> need to run this from a C# app. I think my only other option is using the
> BCP api to select the data and load it into the new table, but this just
> seems like the wrong way to go.
> Any other ways to go with this?
> Shawn
>

Merge Join Problem

Hello,
I've got a problem with the merge join operator.

I'm trying to Join two tables using a left outer join.

Here are my tables :

TABLE AGENT
AGENT_CODE; REGION_ID; COUNTRY_ID
AA;01;01;
BB;01;01;
CC;01;02;
CC;02;02;
DD;01;01;

600 records in this table.

TABLE DELEGUE
AGENT_CODE; FIRST_NAME; LAST_NAME
AA; Maradona;Diego;
BB; Maradona;Diego;
DD; Zidane;Zinedine;

145 records in this table.

This is what i expect in my left outer join:
AGENT_CODE; REGION_ID; COUNTRY_ID;FIRST_NAME; LAST_NAME
AA;01;01;Maradona;Diego;
BB;01;01;Maradona;Diego;
CC;01;02;NULL;NULL
CC;02;02;NULL;NULL;
DD;01;01;Zidane;Zinedine

600 records in the destination table, with only 145 Not null first_name and last_name

The issorted property has been set to true in the 2 sources tables, the numkeyscolumns property is set to 1, and the join keys are both checked.
When i execute the sql query using the editor in sql server, the result is good, i've got what i've expected.
But when i run the dataflow, the destination has got 600 records, but the columns FIRST_NAME and LAST_NAME are not null only 8 times, the 592 other lines are NULL.
Could someone explain to me why i lost 145-8=137 FIRST_NAME and 145-8=137 LAST NAME ?
Thank you in advance.

by left join as the table on left has 600 records, therefore it must result 600 rows, i think so

can you also write your query also here

you can also check by sub query that how many rows exactly match in your child table

|||I changed the table names in first post so that anyone could understand (i'm french), the real table names are :
CODE_AGENT (600 records) and DELEGUE (145).
i want to join these two tables using CODE_AGENT.AGENT_ID and DELEGUE.CODE_AGENT

this is the query i made :
SELECT DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE, CODE_AGENT.NOM_AGENT, CODE_AGENT.CODE_SECTEUR,
CODE_AGENT.CODE_EQUIPE, CODE_AGENT.CODE_USINE, CODE_AGENT.AGENT_ID, CODE_AGENT.REGION_ID
FROM CODE_AGENT LEFT OUTER JOIN
DELEGUE ON CODE_AGENT.AGENT_ID = DELEGUE.CODE_AGENT

The result is exactly what i want, but when i use SSIS, i lost many records of DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE.|||

You said that the IsSorted property is set to true and the sortkeyposition property is set to 1 but you didn't say that the data was sorted. Can you verify that the data is actually sorted. Setting these properties don't do anything except tell the dataflow that the data is sorted. If it actually is not sorted then the results of your MergeJoin are undefined.

Thanks,

Matt

|||Thank you Matt !
That was the problem, i didn't understand that i had to sort the table for each merge join.
The result is correct!
I added a "Sort" data flow transformation and the result is ok!

Merge join more than 2 tables

I need to take certain items of data from four different tables and out them into one table.

Unfortunately my source data's version of SQL does not support the LEFT JOIN keyword which has left me with a bit of a problem.

I saw the merge join in SSIS and used it to get data from two of the tables and stick them in the destination and it all worked fine.

That got me thinking, is it possible to create a second merge join transformation within the same data flow task for the remaining two tables and then join the output of both the merge joins to give me the data I need from all four tables in one output?

I cannot answer your direct question about the merge join, but wanted to make one comment. It is possible to recast a Left Join query as a Union and this might be a viable approach for your problem. The general form is to make a Union of the simple Join and a Not IN query.

Merge filtering HOST_NAME help

Hello
Im trying to create a merge replication.
I've a 2xSubscribers and a distributor/publisher
My published table has fieldnames describing what server this row should be published. When I create my replication a use a filter like this: ... where target_server = host_name()
I want to publish rows to different subscribers, for instance i have a server with hostname 'BOBAFETT' and another with hostname 'R2D2' and a distributor/publisher with hostname 'SKYWALKER'. When I insert a row into the publisher I cannot get it replicate
d unless i use SKYWALKER as target_server, but I will use BOBAFETT or R2D2 as target_server, thereby getting the correct row to the intended subscriber!!
Another strange thing, when I insert a row into the subscriber it gets deleted and nothing is inserted into the publishers table!!
My own thoughts is that the host_name() only applies to the publisher and not the subscriber, therefore im only able to publish tows with the publisher name as fieldvalue - That's not quite what I would expect.
Ohh btw. If I hardcode BOBAFETT in the filter where clause it gets the correct rows!!
Can anybody help me?
Kind regards
Janus, Denmark
Janus,
as SKYWALKER is your publication server, this should amalgamate all the rows. The other servers BOBAFETT and R2D2 will be restricted to having rows with their own server name in the filtering column ie on R2D2 you can add rows with R2D2 but you can't add
a record having the value SKYWALKER.
BTW, there is another way of setting it up which is less restrictive - you can use -HOSTNAME 'value' as a parameter in the merge agent's job. This means that you aren't restricted to having a column with R2D2 or BOBAFETT in it, and instead you could have
some business indicator.
HTH,
Paul Ibison, London, UK

Merge filter inconsistant

We are doing merge replication between a central server and multiple
anonymous clients. We use a filter on one table and joins to related
tables to determine what records to merge. There are two parts to the
filter, one is user, which merges only records assigned to the user
that is replicating. The second is a merge code, which is set to 0 or
1 and basically serves as a completion code: when the user is done
with the record, it is set to complete and is no longer included in
the merge.
The problem has to do with changes to the merge code. As long as it
is set to 1, replication occurs smoothly. After it is set to 0 and
merged so that client and server are 0, changes made to the client
records continue to be merged with the server until a change is made
to the table on the server. So, if there are two tables A & B, when
table A is changed on the server, local changes to A will no longer be
merged but local changes to B will continue to merge until a change is
made to B on the server. Since A & B are related tables, this is a
serious synchronization issue! It is as though a server change has to
be made to each table to force re-evaluation of the filter criteria
for that table, strange.
Here are the details of our setup:
There are 3 tables for which merge replication is defined on SQL
Server 2000 on NT:
1. WI_ASGN
wi_asgn_guid
wi_guid (fk to work_item constraint)
asgn_to_user_id
dnld_merg_cd
2. WORK_ITEM
wi_guid
office_id
3. WI_DETAIL
dtl_guid
wi_guid (fk to work_item constraint)
start_date
A row filter is defined on the WI_ASGN table: WHERE
[WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM
[dbo].[WI_ASGN] WHERE (ASGN_TO_USER_ID =
dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
fGetCurrentUserID is a user defined function which returns a char(8)
user_id.
Join filters are used to filter WORK_ITEM:
SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
[dbo].[WORK_ITEM] ON [WI_ASGN].[WI_GUID] =
[WORK_ITEM].[WI_GUID]
and WI_DETAIL:
SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
[dbo].[WI_DETAIL] ON [WI_ASGN].[WI_GUID] =
[WI_DETAIL].[WI_GUID]
We begin with DNLD_MERG_CD = 1 and merge replication running smoothly.
After the user has completed working on the assignment, the data no
longer should no longer replicate even if the user makes chagnes. We
reset the DNLD_MERG_CD to 0 so that the replication filter condition
would not be met.
After doing this, the problem occurs. The following steps were
executed to test the replication:
Action:
DNLD_MERG_CD is set to 0.
Merge is executed.
Result:
Merge code is 0 on both databases (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local changes are replicated to the Central database. (not expected
since merge filter condition is not met)
Action:
WORK_ITEM is updated on the Central database.
Merge is executed
Result:
The Central change is not replicated to the Local database. (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local change to WORK_ITEM is not replicated (expected, but different
result then the first local change)
Local change to WI_DETAIL is replicated to Central database (not
expected since merge filter condition is not met)
Action:
WI_DETAIL is updated on the Central database.
Merge is executed
Result:
The Central change is not replicated to the Local database. (expected)
Action:
WORK_ITEM and WI_DETAIL are updated on the Local database.
Merge is executed
Result:
Local changes ar not replicated (expected)
Why do the changes to the Local database tables continue to replicate
until the corresponding Central database table is modified?
Sorry for the length of this post, but it seemed necessary. Thanks
for your help.
Kees VanTilburg
VanTilburg Enterprises
Hi Kees,
There is something strange happening here.
Firstly, if a row meets your subset filter requirements it will be
replicated to the subscriber from the publisher. Then, subscriber DML on
that row will be replicated back to the publisher.
If, however, your subset filter excludes the row then the row will not
replicate to the subscriber in the first place. Now, in your case, the
row does at first match the filter's requirements so it is replicated to
the subscriber. But when you change the row data so that the row does
not match the filter anymore (we call that "moving out of partition")
the row at the subscriber should be deleted since it no longer belongs
in the subscriber's partition. Because your row (and all its dependent
rows) does not get removed from the subscriber, it leads me to believe
that something could be wrong with the subset filterclause.
Looking at your subset filterclause, the only thing that struck me as a
bit strange was the unnecessary double select. Could you try use the
following instead:
change:
WHERE [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN] WHERE
(ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
to:
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1)
There shouldn't be a reason to do the first IN selection since the
second WHERE clause is all you're interested in.
Also, you might try (for testing sake) remove the first requirement and
simply state WHERE DNLD_MERG_CD = 1 as your subset filterclause. See if
this works and then add fGetCurrentUserID() back again.
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
kjvt wrote:
> We are doing merge replication between a central server and multiple
> anonymous clients. We use a filter on one table and joins to related
> tables to determine what records to merge. There are two parts to the
> filter, one is user, which merges only records assigned to the user
> that is replicating. The second is a merge code, which is set to 0 or
> 1 and basically serves as a completion code: when the user is done
> with the record, it is set to complete and is no longer included in
> the merge.
> The problem has to do with changes to the merge code. As long as it
> is set to 1, replication occurs smoothly. After it is set to 0 and
> merged so that client and server are 0, changes made to the client
> records continue to be merged with the server until a change is made
> to the table on the server. So, if there are two tables A & B, when
> table A is changed on the server, local changes to A will no longer be
> merged but local changes to B will continue to merge until a change is
> made to B on the server. Since A & B are related tables, this is a
> serious synchronization issue! It is as though a server change has to
> be made to each table to force re-evaluation of the filter criteria
> for that table, strange.
> Here are the details of our setup:
> There are 3 tables for which merge replication is defined on SQL
> Server 2000 on NT:
> 1. WI_ASGN
> wi_asgn_guid
> wi_guid (fk to work_item constraint)
> asgn_to_user_id
> dnld_merg_cd
> 2. WORK_ITEM
> wi_guid
> office_id
> 3. WI_DETAIL
> dtl_guid
> wi_guid (fk to work_item constraint)
> start_date
> A row filter is defined on the WI_ASGN table: WHERE
> [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM
> [dbo].[WI_ASGN] WHERE (ASGN_TO_USER_ID =
> dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
> fGetCurrentUserID is a user defined function which returns a char(8)
> user_id.
> Join filters are used to filter WORK_ITEM:
> SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
> [dbo].[WORK_ITEM] ON [WI_ASGN].[WI_GUID] =
> [WORK_ITEM].[WI_GUID]
> and WI_DETAIL:
> SELECT <published_columns> FROM [dbo].[WI_ASGN] INNER JOIN
> [dbo].[WI_DETAIL] ON [WI_ASGN].[WI_GUID] =
> [WI_DETAIL].[WI_GUID]
> We begin with DNLD_MERG_CD = 1 and merge replication running smoothly.
> After the user has completed working on the assignment, the data no
> longer should no longer replicate even if the user makes chagnes. We
> reset the DNLD_MERG_CD to 0 so that the replication filter condition
> would not be met.
> After doing this, the problem occurs. The following steps were
> executed to test the replication:
> Action:
> DNLD_MERG_CD is set to 0.
> Merge is executed.
> Result:
> Merge code is 0 on both databases (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local changes are replicated to the Central database. (not expected
> since merge filter condition is not met)
> Action:
> WORK_ITEM is updated on the Central database.
> Merge is executed
> Result:
> The Central change is not replicated to the Local database. (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local change to WORK_ITEM is not replicated (expected, but different
> result then the first local change)
> Local change to WI_DETAIL is replicated to Central database (not
> expected since merge filter condition is not met)
> Action:
> WI_DETAIL is updated on the Central database.
> Merge is executed
> Result:
> The Central change is not replicated to the Local database. (expected)
> Action:
> WORK_ITEM and WI_DETAIL are updated on the Local database.
> Merge is executed
> Result:
> Local changes ar not replicated (expected)
> Why do the changes to the Local database tables continue to replicate
> until the corresponding Central database table is modified?
> Sorry for the length of this post, but it seemed necessary. Thanks
> for your help.
> Kees VanTilburg
> VanTilburg Enterprises
>
|||I assume that your subscribers are runnig sql server ce.
Take a look at
http://support.microsoft.com/default...b;EN-US;315758
http://support.microsoft.com/default...b;EN-US;287651
http://support.microsoft.com/default...b;EN-US;827914
Marius
|||Thank you for your reply. No, the subscribers are running MSDE.
Kees
On Thu, 13 May 2004 11:25:32 +0300, "Marius Bucur"
<marius.bucur@.optima.ro.nospan> wrote:

>I assume that your subscribers are runnig sql server ce.
>Take a look at
>http://support.microsoft.com/default...b;EN-US;315758
>http://support.microsoft.com/default...b;EN-US;287651
>http://support.microsoft.com/default...b;EN-US;827914
>Marius
>
|||Reinout,
Thank you for your reply. Please see my comments below.
Kees
On Wed, 12 May 2004 18:02:06 -0700, "Reinout Hillmann [MS]"
<reinouth@.online.microsoft.com> wrote:

>Hi Kees,
>There is something strange happening here.
>Firstly, if a row meets your subset filter requirements it will be
>replicated to the subscriber from the publisher. Then, subscriber DML on
>that row will be replicated back to the publisher.
>If, however, your subset filter excludes the row then the row will not
>replicate to the subscriber in the first place. Now, in your case, the
>row does at first match the filter's requirements so it is replicated to
>the subscriber. But when you change the row data so that the row does
>not match the filter anymore (we call that "moving out of partition")
>the row at the subscriber should be deleted since it no longer belongs
>in the subscriber's partition. Because your row (and all its dependent
>rows) does not get removed from the subscriber, it leads me to believe
>that something could be wrong with the subset filterclause.
>Looking at your subset filterclause, the only thing that struck me as a
>bit strange was the unnecessary double select. Could you try use the
>following instead:
>change:
>WHERE [WI_ASGN].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN] WHERE
>(ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1))
>to:
>WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD = 1)
>There shouldn't be a reason to do the first IN selection since the
>second WHERE clause is all you're interested in.
There's another layer of complexity that necessitates the subquery.
However, I think it would be better to filter on the Work_Item table
as follows:
WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1))
Work item has a one-many relationship with wi_asgn (there may be many
employees assigned to a single work item). If I am assigned to a work
item, I need to replicate all the assignment records for the work item
(including other employees' assignment records). But I don't want any
assignments for work items that I'm not assigned to. So for example:
wi_guidasgn_to_user_iddnld_merg_cd
1me1
1john1
1jane1
2john1
3jane1
If I merge, then I need to replicate the first three rows. If i
filter on Work_item as specified above, then join wi_asgn to work_item
on wi_guid, that should return the proper rows. This is different
from what I was doing prior (the filter was on wi_asgn, which was then
joined to work_item), so I'll test this and report back.

>Also, you might try (for testing sake) remove the first requirement and
>simply state WHERE DNLD_MERG_CD = 1 as your subset filterclause. See if
>this works and then add fGetCurrentUserID() back again.
I'll also try this simpler test and report back.

>
>Hope this helps,
>Reinout Hillmann
>SQL Server Product Unit
|||Hi Kees,
I am just checking on your progress regarding you said you will show us
your report back. I wonder how the testing is going. If you encounter any
difficulty, please do not hesitate to let me know. Please post here and let
me know the status of your issue. Without your further information, it's
very hard for me to continue with the troubleshooting.
Looking forward to hearing from you soon.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Michael,
Based on further testing, it appears to be the subquery in the filter
clause that causes the problem. Please see below for updates on my
test results that led to this conclusion.
How can I restructure the filter to eliminate the problem while still
meeting my requirement?
Thank you.
Kees
On Thu, 13 May 2004 17:09:59 -0500, kjvt <kjvt@.newsgroups.nospam>
wrote:

>Reinout,
>Thank you for your reply. Please see my comments below.
>Kees
>
>On Wed, 12 May 2004 18:02:06 -0700, "Reinout Hillmann [MS]"
><reinouth@.online.microsoft.com> wrote:
>
>There's another layer of complexity that necessitates the subquery.
>However, I think it would be better to filter on the Work_Item table
>as follows:
>WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
>WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
>1))
>Work item has a one-many relationship with wi_asgn (there may be many
>employees assigned to a single work item). If I am assigned to a work
>item, I need to replicate all the assignment records for the work item
>(including other employees' assignment records). But I don't want any
>assignments for work items that I'm not assigned to. So for example:
>wi_guidasgn_to_user_iddnld_merg_cd
>1me1
>1john1
>1jane1
>2john1
>3jane1
>If I merge, then I need to replicate the first three rows. If i
>filter on Work_item as specified above, then join wi_asgn to work_item
>on wi_guid, that should return the proper rows. This is different
>from what I was doing prior (the filter was on wi_asgn, which was then
>joined to work_item), so I'll test this and report back.
>
I retested after moving the filter criteria to the work_item table
WHERE [Work_Item].[WI_GUID] IN (SELECT WI_GUID FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1))
and received the same results (see my note describing the original
problem).

>I'll also try this simpler test and report back.
A simple filter clause (without the subquery)
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =
1)
works correctly. When the dnld_merge_cd is set to 0 and a merge is
executed, the row is deleted from the local (subscriber) database.
(Thus my conclusion that the subquery is causing the problem.)
However, this approach does not return all the records that I need.
[vbcol=seagreen]
|||Hi Kees,
Thanks for your update.
From your descriptions, I understood that
1.
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1)
the WHERE clause works well, why didn't you use this clause only?
2.
WHERE [Work_Item].[WI_GUID]
IN
(SELECT WI_GUID
FROM [dbo].[WI_ASGN]
WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1))
By using WHERE clauses above, could you make the right result back in Query
Analyzer? Could you make all other clause and condition right back from
Query Analyzer? I am not sure what dbo.fGetCurrentUserID() send back, but
based on my testing, the above could work well.
Could you make it without dbo.fGetCurrentUserID()?
Would you please send me some sample data that could reproduce the problem?
Could you ensure dbo.fGetCurrentUserID() send the right result and
condition for WHERE clause?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Kees,
I checked your replication logic, couldn't figure out why the replication
behaves like that.
So I setup a merge replication to test, using one table:
1. Create the merge table:
create table T3 (c1 int, c2 int, c3 char(8), c4 int)
insert T3 values (1, 1, 'one', 0)
2. Checked the filter statement:
Select * from T3 where c1 in (select c1 from T3 where c3 = 'one' and c4 =1)
=> 0 row returned
Select * from T3 where c3 = 'one' and c4 =1 => 0 row returned.
3. Setup a merge replication for this table (T3) with row filter:
SELECT <published_columns> FROM [dbo].[T3] WHERE c1 in (select c1 from T3
where c3 = 'one' and c4 = 1)
4. Run Snapshot and merge agents. Checked the subscriber table, no row was
copied, This is as expected, since no row satisfies the filter condition..
5. updated publisher:
update T3 set c4=1
6. Run merge agent, one row went to the sub, as expected. Now both pub and
sub has one row with c4 = 1
7. Updated subscriber:
update T3 set c4=0
8. Run merge agent. The row at the sub was removed and the row at pub was
updated. This is by design. At any time, as soon as a row becomes
dissatisfied to the filter condition, it will be removed from the
subscriber table.
After Step 6, if I run step 7, before run merge agent, make another update
statement:
update T3 set c1=0, c2=3 where c1=1
Then run merge agent, I see the row in subscriber was removed and the row
in publisher was updated.
In your case, your first action (below) should remove the row from the
subscriber, then you won't have a chance to do the other updates:
Action:
DNLD_MERG_CD is set to 0.
Merge is executed.
Result:
Merge code is 0 on both databases (expected)
Please test again. If confirmed, please let me know all the versions of
your publisher, subscriber and distributor (I am using 818 for all), and if
possible, send your table schemas and replication script to me for further
check.
Regards,
Lan Lewis-Bevan
MS SQL support
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Lan,
Thank you for looking into this problem. There's an extra wrinkle in
my scenario that could affect the result. My filter condition is on
another table. The filter condition excludes rows based on values in
the second table, yet because of the filter join, those excluded rows
are appropriately included in the replication. You may need to add
that second table to see the same results.
I posted my table defns and replication scripts to this newsgroup and
received a reply from Baisong Wei that they would try to duplicate the
error (see 5/18, appears as a separate thread in Agent Newsreader). I
have not heard back from them. I'd love to get this solved sooner,
but don't want to see you duplicating effort.
We have version 8.00.760 on our server and locally. I believe this is
sp3. Where do I obtain v8.18? It would be worth a retest on that
version.
Kees
@.@.version on publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack
4)
@.@.version on subscriber:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack
1)
On Tue, 25 May 2004 01:36:27 GMT, lanlb@.online.microsoft.com (Lan
Lewis-Bevan [MS]) wrote:

>Hi Kees,
>I checked your replication logic, couldn't figure out why the replication
>behaves like that.
>So I setup a merge replication to test, using one table:
>1. Create the merge table:
>create table T3 (c1 int, c2 int, c3 char(8), c4 int)
>insert T3 values (1, 1, 'one', 0)
>2. Checked the filter statement:
>Select * from T3 where c1 in (select c1 from T3 where c3 = 'one' and c4 =1)
> => 0 row returned
>Select * from T3 where c3 = 'one' and c4 =1 => 0 row returned.
>3. Setup a merge replication for this table (T3) with row filter:
>SELECT <published_columns> FROM [dbo].[T3] WHERE c1 in (select c1 from T3
>where c3 = 'one' and c4 = 1)
>4. Run Snapshot and merge agents. Checked the subscriber table, no row was
>copied, This is as expected, since no row satisfies the filter condition..
>5. updated publisher:
>update T3 set c4=1
>6. Run merge agent, one row went to the sub, as expected. Now both pub and
>sub has one row with c4 = 1
>7. Updated subscriber:
>update T3 set c4=0
>8. Run merge agent. The row at the sub was removed and the row at pub was
>updated. This is by design. At any time, as soon as a row becomes
>dissatisfied to the filter condition, it will be removed from the
>subscriber table.
>After Step 6, if I run step 7, before run merge agent, make another update
>statement:
>update T3 set c1=0, c2=3 where c1=1
>Then run merge agent, I see the row in subscriber was removed and the row
>in publisher was updated.
>
>In your case, your first action (below) should remove the row from the
>subscriber, then you won't have a chance to do the other updates:
>Action:
>DNLD_MERG_CD is set to 0.
>Merge is executed.
>Result:
>Merge code is 0 on both databases (expected)
>
>Please test again. If confirmed, please let me know all the versions of
>your publisher, subscriber and distributor (I am using 818 for all), and if
>possible, send your table schemas and replication script to me for further
>check.
>Regards,
>
>Lan Lewis-Bevan
>MS SQL support
>This posting is provided "AS IS" with no warranties, and confers no rights.

merge datasets

Is there a way to merge to datasets in order to view the data in one table. IE:
Dataset1 Dataset2

Customer Q1 Cutomer Q2
A 1 A 5
B 3 B 2

Table
Customer Q1 Q2
A 1 5
B 3 2yes, you can use the Merge method:
Dataset1.Merge(Dataset2)
you need to specify a primary key for each dataset|||

How would this work in a report. ie.. user opens a report and hits refresh. is there a place this code is run so that it executes when either dataset1 or dataset2 is refreshed. I am new to Reporting Services so please excuse.

|||

Couldnt you merger the two datasets on the SQL side?

Put Q1 and Q2 into temp tables, select Q1 into another temp, then left outer join Q2 ON Q1.ID = Q2.ID.
Just a thought, I have had to merge a bunch of datasets on the back end because old procs were returning multiple datasets,...

|||You definitely could do it on the database side. Seems a bit difficult if the tables are in different databases. I am busy doing an evaluation of reporting services 2005. The ability to merge "datasets" is available and very simple to do in our current reporting enviroment.(Business Objects) We are trying to do the same type of things using RS.

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

sql

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

Merge data from two tables into one table - no updates/only insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

merge conflicts resolution en-masse

Recently added table to merge, another process is moving the records between
servers and beating merge causing conflicts. App so big - dev will have to
spend many hours finding cause & resolving. Until then I have to deal w/
conflicts...
How do I resolve conflicts when I have thousands of them? I want to keep the
winning change for all. Is it safe to simply delete all records from the
conflict table:
delete from conflict_MergePubName_MyTableName
Thanks
CB
Chris,
if you're happy with the default conflict resolution that has already
occurred, you should be able to cursor through your records and call
sp_deletemergeconflictrow
eg: exec sp_deletemergeconflictrow 'conflict_NorthwindRegionsMerge_Region',
'[dbo].[Region]', '{9E93B574-55A3-4D72-A108-EED0C8E6B899}',
'UK-3XSW02J.pubs'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge changes

I just want to verify that my plan for making changes to SQL 2000 merge
publication and to ask some basic questions. I need to add a new table,
modify a view, add a column to a table in existing articles and uncheck the
"Enforce relationships for replication" on several tables due to it causing
problems with synchs. Here is my plan.
1. Make all changes in EM (if possible).
2. Create new publications and subscriptions.
3. Have laptops synchronize.
Can I just use the current laptops windows synch manager to have them
recognize and replace their current subscriptions?
Is there any other things I need to be concerned with?
Thanks.
David
David,
if you are wanting to initialize these subscribers, you'll have to
synchronize (to get all the data to the publisher) and then drop the
subscriptions first. After that you make the changes and then add the
subscriptions and run the snapshot agent.
All of these changes could be done without reinitializing in this way
though. Adding a new table is straightforward for an existing publication.
Adding a column is done through sp_repladdcolumn. Changing the FK
relationships and modifying the view could be done using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Are you saying I won't need to drop and recreate the subscriptions if I do
all of this in scripts? I have done sp_repladdcolumn and sp_addmergearticle
before but not sp_addscriptexec. How do I get the script for unchecking the
"Enforse relationship for replication"? Also, should the
@.force_invalidate_snapshot be 1? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e$T2JN0LHHA.448@.TK2MSFTNGP04.phx.gbl...
> David,
> if you are wanting to initialize these subscribers, you'll have to
> synchronize (to get all the data to the publisher) and then drop the
> subscriptions first. After that you make the changes and then add the
> subscriptions and run the snapshot agent.
> All of these changes could be done without reinitializing in this way
> though. Adding a new table is straightforward for an existing publication.
> Adding a column is done through sp_repladdcolumn. Changing the FK
> relationships and modifying the view could be done using sp_addscriptexec.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - yes - this way should be able to avoid the reinitialization. There
is no easy script for the NFR setting - you'd have to drop the Fk constraint
and readd in script eg
ALTER TABLE dbo.Table_2
DROP CONSTRAINT FK_Table_2_Table_1
GO
ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
FK_Table_2_Table_1 FOREIGN KEY
(
id
) REFERENCES dbo.Table_1
(
id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
GO
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about the setting for @.force_invalidate_snapshot on the others?
Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OPDUyx0LHHA.140@.TK2MSFTNGP04.phx.gbl...
> David - yes - this way should be able to avoid the reinitialization. There
> is no easy script for the NFR setting - you'd have to drop the Fk
> constraint and readd in script eg
> ALTER TABLE dbo.Table_2
> DROP CONSTRAINT FK_Table_2_Table_1
> GO
> ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
> FK_Table_2_Table_1 FOREIGN KEY
> (
> id
> ) REFERENCES dbo.Table_1
> (
> id
> ) ON UPDATE NO ACTION
> ON DELETE NO ACTION
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Paul,
Also, will I need to run sp_addmergearticle for the new table and view? I
thought there was a checkbox option to make it repl if you added it in EM.
Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OPDUyx0LHHA.140@.TK2MSFTNGP04.phx.gbl...
> David - yes - this way should be able to avoid the reinitialization. There
> is no easy script for the NFR setting - you'd have to drop the Fk
> constraint and readd in script eg
> ALTER TABLE dbo.Table_2
> DROP CONSTRAINT FK_Table_2_Table_1
> GO
> ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
> FK_Table_2_Table_1 FOREIGN KEY
> (
> id
> ) REFERENCES dbo.Table_1
> (
> id
> ) ON UPDATE NO ACTION
> ON DELETE NO ACTION
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - the existing snapshot will become invalid for any other new
subscribers so set this to 1.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||In EM you can select the checkbox to "Show unpublished objects" then select
the new table to have it added. A snapshot will need to be generated but
only the new table will be taken.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
One of the changes is a scripting of a new trigger on a frequently used
table and I prefer to do this during the day when users are in it (approx.
20 users). Will this be a problem? and if so, are there any ways around
this? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OJjQpD1LHHA.3424@.TK2MSFTNGP02.phx.gbl...
> In EM you can select the checkbox to "Show unpublished objects" then
> select the new table to have it added. A snapshot will need to be
> generated but only the new table will be taken.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||Sp_addscriptexec for this will be running a create trigger script which will
then run immediately after synchronization. Assuming you have > 1
subscriber, there could be a disparity of data and lack of synchronization.
Ideally, you could stay late and run the command interactively once
everyone's off the system. Alternatively I'd set up the sp_addscriptexec
statement as a step in a job that runs once only and schedule it for during
the downtime - you obviously have to be sure that it runs fine in your test
environment for this though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Merge cells vertically

Hello
Is it possible to merge cells of a table control vertically?
See the second column in this example:
+--+--+
| | |
+--+ |
| | |
+--+--+
Ciao,
JensJens,
I tried doing that a few times with no success, I don't believe it can
be done any simple way. Unless someone has some insider information :)

Merge cells

Can I merge two vertical cells in a table?
ThanksNo. Merging requires 2 or more columns.
From MSDN Reporting Services Books Online:
Merging Cells
Multiple contiguous cells within a table can be combined into a single cell.
This is known as a column span or a cell merge. Cells can only be combined
across columns. When you merge cells, only the data in the first cell is
preserved. If data exists in other cells, the data is removed. Merged cells
can be split into their original columns.
See http://msdn2.microsoft.com/en-us/library/ms159169.aspx for more
information.
"Mark Goldin" wrote:
> Can I merge two vertical cells in a table?
> Thanks
>
>

merge cell vertically

We can merge the cells in Table horizontally.

Can we merge cell vertically?

Do you mean rowspan?|||is there any way to have rowspan in SSRS 2005?

Merge Application Conflict Log

Is it possible to write conflict sql in some persistent table.
I know that they a writen in merge replication tables and can be viewed via
conflict viewer
but they a lost after the snapshot run.
I need to keep them forever.
Probably i can write custom resolver who will do thta - but is there any
'out of the box' solution?
Grigoris - as far as I know there isn't any out-of-the-box solution but
perhaps the easiest solution would be to regularly poll the conflict table
and to write the values into an audit table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Use perfmon to watch the conflicts per second counter of SQL
Server:Replication Merge and run a job which reads the conflict tables when
conflicts occur.
You can also use sp_helpmergearticleconflicts and run it on the publisher
and subscriber. It will return a list of articles with conflicts and you can
then manually or programmatically retrieve them.
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
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:%237acphUZHHA.588@.TK2MSFTNGP06.phx.gbl...
> Is it possible to write conflict sql in some persistent table.
> I know that they a writen in merge replication tables and can be viewed
> via conflict viewer
> but they a lost after the snapshot run.
> I need to keep them forever.
> Probably i can write custom resolver who will do thta - but is there any
> 'out of the box' solution?
>

Merge always deletes inserts on subscriber

Hey.
Table1 replicates to table3
Table2, just another table with the same schema as Table1 and 3.
In my setup, I created a copy table, copied 66 rows from table1 on publisher
to table2 on subscriber and deleted all 66 rows. The changes were replicated
to the subscriber to Table2(66 deletes made). Now i insert all 66 rows from
Table2 to table3 on the subscriber side. The change should have gone to
publisher. Instead, the inserts get deleted on the subscriber... Why would
SQL do this?
The inserts done later should be taken as new changes on subscriber and
should be replicated to publisher, isn't it?
Forgot to add, it took it as a conflict between pub and sub and for me, by
default, the pub wins... The problem with this is though, it shouldn't be a
conflict. I've ADDED these rows right now and ONLY to the subscriber...
Any clues.

Merge a graphic image with a result set via SQL/Stored Proc

SQL Server 2000
================================== Howdy All -
I have a table of inventory items with a PK of itemnum (varchar(30)).
I have very small image files (gifs) that I wish to merge with the
resultset of a query to use in reporting. Each image file has a name
that matches extactly with itemnum PK it belongs to.
How can I load the images and return the data nd images to my
reporting system?
Yes, I know it is not pretty. I know how to do it in concept but what
do I use to read in the file image?
Thanks,
CraigHi Criag
You may find this useful
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
and http://support.microsoft.com/default.aspx?scid=kb;en-us;258038
The displaying of the images is dependent on what your client is, for
instance Reporting services has an image control.
John
"Craig" wrote:
> SQL Server 2000
> ==================================> Howdy All -
> I have a table of inventory items with a PK of itemnum (varchar(30)).
> I have very small image files (gifs) that I wish to merge with the
> resultset of a query to use in reporting. Each image file has a name
> that matches extactly with itemnum PK it belongs to.
> How can I load the images and return the data nd images to my
> reporting system?
> Yes, I know it is not pretty. I know how to do it in concept but what
> do I use to read in the file image?
> Thanks,
> Craig
>

Merge 2005 - A row from this table will go to only one subscription

Hello everyone.

We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.

We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.

I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.

Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.

Any helps would be appreciated. Thanks.

BUMP!

Merge 2005 - A row from this table will go to only one subscription

Hello everyone.

We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.

We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.

I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.

Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.

Any helps would be appreciated. Thanks.

BUMP!

sql