Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Friday, March 30, 2012

Merge rows

I've been tearing my hair out for the past few days trying to get a merge across rows in SQL SERVER 200. Here is an example result set that I have so far:

Code Snippet

zzzz_X@.X_orange.net 0 0 1
zzzz_X@.X_orange.net 0 1 0
zzzz_X@.X_orange.net 1 0 0
zztoproadking_X@.X_yahoo.ca 0 0 1
zztoproadking_X@.X_yahoo.ca 0 1 0
zztoproadking_X@.X_yahoo.ca 1 0 0
zztoponly_X@.X_yahoo.com 0 0 1
zztoponly_X@.X_yahoo.com 0 1 0
zztoponly_X@.X_yahoo.com 1 0 0
zzjozz_X@.X_aol.com 0 0 1


What I want to do ultimately is merge those repeated email addresses into a single row, such as:

Code Snippet

zzzz_X@.X_orange.net 1 1 1
zztoproadking_X@.X_yahoo.ca 1 1 1
zztoponly_X@.X_yahoo.com 1 1 1
zzjozz_X@.X_aol.com 0 0 1


I realize this is possible using cursors, but is there a simple SQL way to achieve the same results?

Here's my SQL for the first result set:

Code Snippet

SELECT DISTINCT TOP 10 C.EmailAddress,
CASE C.CampaignID WHEN '1' THEN '1' ELSE '0' END AS travel_deals,
CASE C.CampaignID WHEN '2' THEN '1' ELSE '0' END AS contests,
CASE C.CampaignID WHEN '3' THEN '1' ELSE '0' END AS picks_of_the_week
FROM dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email
GROUP BY C.EmailAddress, C.campaignID, M.memberID
ORDER BY C.EmailAddress DESC

You can group them using "group by" clause.

SELECT TOP 10

C.EmailAddress,
max(CASE WHEN C.CampaignID = '1' THEN '1' ELSE '0' END) AS travel_deals,
max(CASE WHEN C.CampaignID = '2' THEN '1' ELSE '0' END) AS contests,
max(CASE WHEN C.CampaignID = '3' THEN '1' ELSE '0' END) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress

ORDER BY C.EmailAddress DESC

go

AMB

|||What is the MAX() doing in this case? The columns after the email address are booleans (or bit in SQL SERVER dialect).

While your way does seem to do an email address merge, I'm getting 1,1,1 for every email address.
|||

You are in the rite track, but missed the group by function which group all your row wise data into single row. You can use max/sum as per your requirement.

This is the legacy approach to get the Pivot data(swaping row based values into Columns),

Code Snippet

SELECT DISTINCT TOP 10

C.EmailAddress,

C.campaignID,

M.memberID,

Isnull(Max(CASE C.CampaignID WHEN '1' THEN '1' END),0) AS travel_deals,

Isnull(Max(CASE C.CampaignID WHEN '2' THEN '1' END),0) AS contests,

Isnull(Max(CASE C.CampaignID WHEN '3' THEN '1' END),0) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C

LEFT OUTER JOIN Members M

ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress,

C.campaignID,

M.memberID

ORDER BY

C.EmailAddress DESC

|||

I really don’t know how you got all 1.

Here the sample,

Create Table #marketingemailcampaignaddresses (

[EmailAddress] Varchar(30) ,

[CampaignID] int

);

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','1');

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','2');

Insert Into #marketingemailcampaignaddresses Values('zztoproadking_X@.X_yahoo.ca','3');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','1');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','2');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','3');

Insert Into #marketingemailcampaignaddresses Values('zzjozz_X@.X_aol.com','2');

Select

EmailAddress,

Case When CampaignID = '1' Then 1 Else 0 End AS travel_deals,

CASE WHEN CampaignID = '2' THEN 1 ELSE 0 ENDAS contests,

CASE WHEN CampaignID = '3' THEN 1 ELSE 0 ENDAS picks_of_the_week

From

#marketingemailcampaignaddresses

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzzz_X@.X_orange.net100

zzzz_X@.X_orange.net010

zztoproadking_X@.X_yahoo.ca001

zztoponly_X@.X_yahoo.com100

zztoponly_X@.X_yahoo.com010

zztoponly_X@.X_yahoo.com001

zzjozz_X@.X_aol.com010

*/

Select

EmailAddress,

Isnull(Max(Case When CampaignID = '1' Then 1 End),0) AS travel_deals,

Isnull(Max(CASE WHEN CampaignID = '2' THEN 1 END),0)AS contests,

Isnull(Max(CASE WHEN CampaignID = '3' THEN 1 END),0)AS picks_of_the_week

From

#marketingemailcampaignaddresses

Group By

EmailAddress

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzjozz_X@.X_aol.com010

zztoponly_X@.X_yahoo.com111

zztoproadking_X@.X_yahoo.ca001

zzzz_X@.X_orange.net110

*/

|||You are absolutely correct. I was getting all 1's when trying to run the pivot without creating a temp table with the original results.

I had to do an SELECT INTO and create a temp table, then run a SELECT from that table to achieve the correct results.

Thanks for the help (both of you)!

Merge reversed

Running Win 2000 & SQL 2000
I have a merge publication/subscription (rows are
filtered by location), replicating data between 16
servers (15 remote location & a main office). When data
is changed at the a remote location, the changes gets
propagated to the main office, but at some point, the
change gets reversed.
Any idea what is causing this?
Larry,
when you say it gets reversed, do you mena the row is deleted from the
subscriber? This could be caused if you are partitioning data and a row has
been added on a subscriber which doesn't accord with its filter.
HTH,
Paul Ibison
|||Look in the conflict table, for the article, on the publisher. This might
give you a clue as to what is going on.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Paul,
No the row is not deleted, but changed back to its
original state, prior to being updated at the
subscriber. This happens sporadically, but is causing
MAJOR problems.
Larry...
|||Rand,
There is nothing in the conflict tables for any article
in the publication.
Larry...
|||I saw your reply to Rand and if both filtering and conflicts are ruled out
(and no user on the publisher is altering the data!) then this is very
strange. I'd use profiler or an audit trigger to capture what is going on.
HTH,
Paul Ibison
|||Thanks Paul...will do.
|||Does the MSmerge_history table on the distributor offer any reason why
the updates were not applied?
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
anonymous@.discussions.microsoft.com wrote:
> Rand,
> There is nothing in the conflict tables for any article
> in the publication.
> Larry...
>

Merge replication?

We have a SQL Server db in Atlanta. Rows are added to tables in that
database by a website in Atlanta. We have a number of people who use
that data in Dallas and they need to be able to modify the data. This
scheme works okay except when the Internet connection between the two
sites goes down.
So we are looking to replicate the database in Dallas. From the little
I've read, Merge replication would probably be our best bet. However,
I've never worked with replication so I'm unsure. I'd like some
resources either online or books that would help me understand the
process better and help me in implementing such a scheme. Thanks!
-John
Merge might work, bi-directional transactional is another option.
In general if you go offline frequently, your chances for generating
conflicts increase. Merge has conflict tracking mechanisms, where if a
conflict occurs it is logged to conflict tables, and the publisher or
subscriber (your choice) wins. You can roll back this conflict. With
bi-directional transactional replication your distribution agent will fail
and you will have to manually fix the problem. Depending on your data flow
this can involve a single row or thousands.
Bi-directional transactional replication is not resilient to schema changes.
Merge replication is to a degree. Transactional replication is also easier
to troubleshoot. Merge will add a tracking columns to each table you are
replicating. Bi-directional transactional replication does not.
Most dba's would pick merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:n5hus0tfqmr4sgudepget0o5ebhaer8nel@.4ax.com...
> We have a SQL Server db in Atlanta. Rows are added to tables in that
> database by a website in Atlanta. We have a number of people who use
> that data in Dallas and they need to be able to modify the data. This
> scheme works okay except when the Internet connection between the two
> sites goes down.
> So we are looking to replicate the database in Dallas. From the little
> I've read, Merge replication would probably be our best bet. However,
> I've never worked with replication so I'm unsure. I'd like some
> resources either online or books that would help me understand the
> process better and help me in implementing such a scheme. Thanks!
>
> -John
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

>Merge might work, bi-directional transactional is another option.
>In general if you go offline frequently, your chances for generating
>conflicts increase. Merge has conflict tracking mechanisms, where if a
>conflict occurs it is logged to conflict tables, and the publisher or
>subscriber (your choice) wins. You can roll back this conflict. With
>bi-directional transactional replication your distribution agent will fail
>and you will have to manually fix the problem. Depending on your data flow
>this can involve a single row or thousands.
>Bi-directional transactional replication is not resilient to schema changes.
>Merge replication is to a degree. Transactional replication is also easier
>to troubleshoot. Merge will add a tracking columns to each table you are
>replicating. Bi-directional transactional replication does not.
>Most dba's would pick merge replication.
We do not go offline frequently, except the past 2 weeks which has
made people scream. I don't think that we will have too many conflicts
because the one location mostly adds records and the other modifies
existing records. Is there a book that you would recommend?
-John
|||I'd probably use bi-directional transactional replication, as long as you
are going to be modifying the schema much.
It is more difficult to set up, and if you are a high volume database you
will have to watch your transaction log growth.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:um90t0de0n4rp3dp2fsu86t988sdhnm3i2@.4ax.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
fail[vbcol=seagreen]
flow[vbcol=seagreen]
changes.[vbcol=seagreen]
easier
> We do not go offline frequently, except the past 2 weeks which has
> made people scream. I don't think that we will have too many conflicts
> because the one location mostly adds records and the other modifies
> existing records. Is there a book that you would recommend?
> -John
>

Merge Replication: Missing rows at Subscriber with no conflicts

Hi all,

We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).

Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.

We are using Merge Replication, with no filters and published every 2 minutes.

Any ideas?

TIA,

Michael

I have seen this before with SQL 2000 replication. It not uncommon especially for very high volumn data transfer. Unfortunately I cannot tell you what the issue is or why it happens.

Although I would advise that a 2 minute sync interval is probibly not useful for you at all. You should run the merge agent continuiously with the -continuious flag if you want it to sync this often.

I would change to continuious, reconcile the data then hopefully it doesnt happen again. If you can you would be better moving to an all SQL 2005 scenario.

Martin

sql

Monday, March 26, 2012

merge replication where deletes are required

Hello all,
I am desperatly need help, our setup is a SQL 2005 server, with merge
replication.
Filtered rows and 120 pocket pcs (subscribers).
We need to delete 100000 rows each night, plus minor inserts, and every time
each pocketpc gets all deletes which kills me and them. I have searched the
net how to achive this without any luck.
I have tried to "reinitilize all" with upload changes, + create new snapshot
+ delete the subscriber and nothing help, all deletes get sent to the
subscriber.
I cannot believe that there are no solution for this common problem, and why
does all the deletes get out to all the subscribers when we have filtered
rows and it works fine with inserts or update?
Please help, anything is better than what we have, and the nightly deletes
are required.
Perhaps you could temporarily turn off the delete tracking:
http://msdn2.microsoft.com/en-us/library/ms146947.aspx?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Wednesday, March 21, 2012

Merge Replication problem....

I created a new pull subscription and I noticed that the subscriber is downloading changes from the publisher (new rows or changes made on other subscribers) but is not uploading new info back to the publisher.

I have been working with this type of replication for a year now and never have any problem like this.

- The job succeeded always. but no changes are uploaded to the susbsciber.
- Checking the merge agent history has 0 on inserts, deletes and updates on the susbcriber.
- Publisher is running the same merge replication with other 42 subscribers without problems.
- Windows 2000 Server (sp4) , Sql 2000 (sp3)
- Sanpshot size is 12 Gigas , I don't want or can't affort to copy again the snapshot.

Thank you for your help.
:(Does your distribution database have the transactions that have not been applied to the Subscriber? If not, you have no option but to apply a snapshot - how you do that is dependent on what is missing and what is available. You can roll-your-own snapshot.

Test a dummy insert on your Publisher and see what happens from Distribution to Subscription(s). It appears something is not running correctly on the Subscriber, and checking the Distributor will help you determine that.

Finally, you may want to create a job that alerts when the difference between the date of the most recently inserted row in your most active article and GetDate() is > n hours.|||Thank you for your suggestions.

I described my problem incorrectly above in saying that I could upload my new data to the subscriber.

The problem is that one of my 42 subscribers is receiving all and any changers that any of the 42 subscriber makes, but it does not sent it's new info to the publsher.

So, it could receive changes but not send any upward.

Sorry, for the confusion.|||My suggestions still apply - just work from the Subscriber back to the Publisher. Use whatever Distributor that Subscriber is using to communicate Updates back to the Publisher to see where the problem is.|||If you find that any changes at the subscriber are not 'taking' i.e. they get overwritten from the publisher even when there are no changes to those particular records anywhere else, then you should check that you have set the subscription priority appropriately and not just left it as 'use publisher as proxy'. I speak from experience.

Merge replication problem MSDE

Summary:
Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
Topology:
I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
subscriber1: every 15 minutes, starts at 00.00h
subscriber2: every 15 minutes, starts at 00.03h
subscriber3: every 15 minutes, starts at 00.06h
subscriber4: every 15 minutes, starts at 00.09h
Sync lasts for average 15 sec, never 3 min.
Problem details:
Message:
The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
table 'Orders', column 'OrderID'.
Description
CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
from CentPub, which means deleted from all subscribers after syncs.)
The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
Is it a bug, side effect or something I do it the wrong way?

Thanks and regardsThe answer is no. When you change your constraints into "NOT FOR REPLICATION" the merge agent is the only process that will ignore the constraint as it propagates the changes amongst the subscribers. The point at which the data is entered into the tables (whether it be front-end app, sproc, etc.) will still enforce the FK constraints.|||Thanks.
I'll try to remove that option.
Anyway, what is the purpose of this option if merge agents don't care about tables with relations?
It is very strange in my situation because I'm sure that my data is in correct form (master-detail) in every point in time (first master, than details) so I don't expect problems during replication.|||Merge agents do care unless you tell them not to. In previous dealings with M$oft, it appears to be related to the number of transactions and the generations associated with them. The merge agent can be set to transmit up to 2000 generations, but will sometines still split up the parent and child transactions, especially in a heavy OLTP database.

Here is link: http://support.microsoft.com/kb/307356/en-us|||Thanks tomh53.
I'll try to increase generations to 2000 and mark not for replication (it should be enough to avoid the problem)
Regards
g.|||I've tested with max value for generations parameter (2000) and everything work fine.
Thanks and regares
G.

Merge Replication Problem Missing rows on subscriber

I have set up Mege replication where a publishing subscriber
subscribes to a central publisher. The publishing subscriber publishes
to other regional subscribers. All Subscriptions are set up as pull
subscriptions using dynamic snapshots. The problem however is that
when the associated application is used and merge replication running
across all machines at 1 minute intervals occurrs randomly some rows
within some of the articles are not recieved at the leaf node
subscribers (No errors are received or conflicts generated) . The
changes always make it out to the publishing subscriber. This
inconsistant state can be corrected by inititaing a reinitialisation of
the subscription for all subscrptions relating to the publishing
subscriber. When this occurrs the original shapshot is reapplied and
all the changes since the last snap shot are applied. The result is
all the records are now transferred. In addition if i use associated
web application to create records within the database and then manually
run each merge agent the data is successfully transferred.
I have tried a lot of differnet things to correct this problem.
(Monitored locks / deadlocks etc). Has anyone else experienced this
sort of behavior?
Look to see if these missing rows show up in the conflict viewer. If so you
may be able to roll these deletes back.
Are you using join filters? This could be a factor. If a parent row falls
out of the filter on the subscriber, it will take all rows in the child
tables out of the subscriber if keep_partition_changes is set to false.
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
"Davec" <dcowie@.ballsolutions.com> wrote in message
news:1138756934.548687.176410@.g47g2000cwa.googlegr oups.com...
>I have set up Mege replication where a publishing subscriber
> subscribes to a central publisher. The publishing subscriber publishes
> to other regional subscribers. All Subscriptions are set up as pull
> subscriptions using dynamic snapshots. The problem however is that
> when the associated application is used and merge replication running
> across all machines at 1 minute intervals occurrs randomly some rows
> within some of the articles are not recieved at the leaf node
> subscribers (No errors are received or conflicts generated) . The
> changes always make it out to the publishing subscriber. This
> inconsistant state can be corrected by inititaing a reinitialisation of
> the subscription for all subscrptions relating to the publishing
> subscriber. When this occurrs the original shapshot is reapplied and
> all the changes since the last snap shot are applied. The result is
> all the records are now transferred. In addition if i use associated
> web application to create records within the database and then manually
> run each merge agent the data is successfully transferred.
> I have tried a lot of differnet things to correct this problem.
> (Monitored locks / deadlocks etc). Has anyone else experienced this
> sort of behavior?
>
|||Thanks for the reply Hilary I really appreciate your help,
I am using Join filters with @.keep_partition_changes = N'true'.
There are no conflict tables
The problem seems to be that when synchronization bewteen my lowest
level leafnode subsciber and regional publisher occurr
the merge agent when enumerating pending changes does not pick up the
full set of data that should be delivered to the subscriber based on
the join filter.
I have managed to narrow down the sequence of events that cause the
problem i am experiencing
This is the Replcation topology i have used
CentralPublication ->Regional Publisher 1 (Global Subscription) ->
Subscriber 1 (Local Subscription)
In Step 1 My web application runs against the Central Publisher
database effectively creating rows of data in a number of tables (all
articles of the publication)
Step 2 - Replication takes place between central publisher and the
regional publisher via a global pull subscription. Rows are filtered
via Join filter to the regional Publisher. This seems to be operating
as i would expect
Step 3 - Replication then takes place between the regional Publisher
and the Regional Subscriber. (The regional Publisher republishes a
filtered set to the regional subscriber) Investigation of the
replicated data at the Central publisher is also correct at this stage
Step 4 - Perform mode transactions via web application such that when
synchronisation between central publisher and regional publisher take
place the join filter now includes some of the rows created in step 1
but not part of the filtred data in step 2
Step 5 - Merge Agent executes synchronizing between central publisher
and Regional Publisher. Inspection of the regional Publisher database
also is shown to be correct
Step 6 - Merge Agent executes on regional Subscriber to sync Regional
subscriber with Regional Publisher. And not all rows are transferred
Interestingly the missing rows seem to be the ones that were created in
step 1 and only satisfy the join filters at step 4 and 5.
Actually this problem i have just noticed seems very similar to one you
have been discussing on Developers Dex
http://www.codecomments.com/sql/mess...4712070&Page=1
with regards to Join Filters and GenerationIDs.
|||As part of my ivestigation i have noticed that the msMerge_GenHistory
table at the leafnode subscriber is populated with entries that have
not been necessaily delivered to the the subscriber. Does this sound
like expected operation. Could this account for the missing records in
that the merge process believes they have already been delivered.

Monday, March 12, 2012

Merge Replication Mysteriously Deleting Rows

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?

What does your subset filter clause look like? If you insert a row that shouldn't be in your partition, merge replication will delete it from the subscriber. The whole point of filtering is to have only rows in your filter remain on the subscriber.|||

Some of the tables on the publisher have more columns than the respective tables on the subscriber. So there is vertical filtering. For example, a sensitive date will not be part of the replication.

|||I was talking about horizontal filtering. Are you filtering using HOSTNAME, or SUSER_SNAME, or some other static filtering?|||

I am using horizontal filtering on two tables. It's a simple "where bit = 1" filter. For example if a special flag is true then the publisher will replicate it to the subscriber. Of course, for the subscriber this value will always be true. Like I said, I am not doing anything really complex.

The weird thing is that these two tables are not the tables I am having problems with.

|||THen you need to provide more information about the article and values so we can help you. IS this article part of a join filter?|||

I think I have solved the problem. When I setup the horizontal filtering, it automatically extended some of the filters and created join filters. When I removed the join filters, the rows stopped being deleted. I will need to create some join filters to ensure that the subscriber only gets the data it needs, but the wizard created join filters all over the place.

Thanks for your help Greg

|||I haven't played around with the wizards in a while, but i will bet it can create joins for all the pk/fk relationships, which makes sense if you want horizontal filtering. Just keep an eye on it.

Merge replication join filter clause can be subquery, self join?

Hi,
I need to filter the replicated rows of a table by using a subquery that
references to itself, or by doing a self join. My filter for the table
called "aCrediAnti" looks like:
Select *
From aCrediAnti t1
Where t1.ImpSaldoAntiMonOri > 0
Or exists(select * from aCrediAnti t2
Where t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
And t2.ClaEmp = t1.ClaEmp
And t2.ImpSaldoAntiMonOri > 0
)
Or may be also in the self join syntax
Select *
From aCrediAnti t1
Left Join aCrediAnti t2
On t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
And t2.ClaEmp = t1.ClaEmp
Where t1.ImpSaldoAntiMonOri > 0
Or (
Not t2.FolOperProvCrediAnti Is Null
And t2.ImpSaldoAntiMonOri > 0
)
My problem is how to specify such a filter. For both I need to specify table
aliases (t1, t2) for using them on the join, but neither the Enterprise
Manager and sp_addmergefilter (script version) allows me to use alias. What
can I do?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
I think a UDF would be ideal for something like this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"faustino Dina" <ffdina@.matusa.com.mx> wrote in message
news:O91w%238DkEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need to filter the replicated rows of a table by using a subquery that
> references to itself, or by doing a self join. My filter for the table
> called "aCrediAnti" looks like:
> Select *
> From aCrediAnti t1
> Where t1.ImpSaldoAntiMonOri > 0
> Or exists(select * from aCrediAnti t2
> Where t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
> And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
> And t2.ClaEmp = t1.ClaEmp
> And t2.ImpSaldoAntiMonOri > 0
> )
> Or may be also in the self join syntax
> Select *
> From aCrediAnti t1
> Left Join aCrediAnti t2
> On t2.ClaOperProvCrediAnti = t1.ClaOperCrediProv
> And t2.FolOperProvCrediAnti = t1.FolOperCrediProv
> And t2.ClaEmp = t1.ClaEmp
> Where t1.ImpSaldoAntiMonOri > 0
> Or (
> Not t2.FolOperProvCrediAnti Is Null
> And t2.ImpSaldoAntiMonOri > 0
> )
> My problem is how to specify such a filter. For both I need to specify
table
> aliases (t1, t2) for using them on the join, but neither the Enterprise
> Manager and sp_addmergefilter (script version) allows me to use alias.
What
> can I do?
> Thanks in advance
> --
> Faustino Dina
> If my email address starts with two 'f'
> drop the first 'f' when mailing me.
>
|||> I think a UDF would be ideal for something like this.
Thanks! Despite it took me hours to decode what "UDF" means ;-) it looks it
works
Faustino