This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead. This UserFilters table would need to be maintained by triggers on the constituent tables so that when rows in Road, UserRegion or SyncSetting change, the rows in UserFilters would be automatically changed to match. I will demonstrate how to do all of this below.
First a note; This table (UserFilters) is a de-Normalized table that is adjunct to your normal application data schema. That is, it is an operational artifact that is only used to manipulate the Replication technology into doing what you want, and is not part of your application's data design/schema. This use of denormalization is considered acceptable as it is solely to address technology limitations and is not directly used by your applications.
OK, here's how to do it:
1. Write Your Desired Filter Query In JOIN Form:
Here's your original filter query (unrolled, to make it easier to decompose):
select *
from StreetLight
where roadId in
(
select Id
from Road
where RegionId in
(
select regionId
from UserRegion
where userid = 1
)
)
and exists
(
select 1
from syncsetting
where userid = 1
and [table] = 'StreetLight'
)
We want to re-write this into a format with only JOINs and WHEREs, but no subQueries. (This can always be done but I won't go into how in this article.) Like so:
SELECT StreetLight.*
FROM StreetLight
JOIN Road ON Road.Id = Streetlight.roadId
JOIN UserRegion ON UserRegion.regionid = Road.RegionId
JOIN syncsetting ON syncsetting.userid = UserRegion.userid
And syncsetting.[table] = 'StreetLight'
Where UserRegion.userid = 1
2. Create A View Consolidating All of the Filter Conditions
We use the JOIN clauses above to make a view that contains only the columns relevant to the Replication Filter, and that projects all of the rows necessary to completely describe all of the filter conditions. This is easier than it sounds, it should look like this:
CREATE VIEW vwUserFilters As
SELECT
Road.Id As RoadId,
Road.RegionId As RegionId,
UserRegion.userid As UserId,
syncsetting.[table] As syncTable
FROM Road
JOIN UserRegion ON UserRegion.regionid = Road.RegionId
JOIN syncsetting ON syncsetting.userid = UserRegion.userid
-- Comment the next line to generalize for all tables
-- And syncsetting.[table] = 'StreetLight'
Note that we have also generalized the View's expression to cover all users. Also, we have commented out the SyncSetting "[table] = 'StreetLight'", in order to generalize this for all tables covered by SyncSetting and Road (which may or may not be valid, you'll have to decide that).
3. Create The UserFilters Table
Create the UserFilters table using the view as a guide for the columns. However, we'll want to add an IDENTITY column for key/index performance reasons:
CREATE TABLE adjUserFilters
(
filterId INT Identity(1,1) PRIMARY KEY,
RoadId INT NOT NULL,
RegionID INT NOT NULL,
userid INT NOT NULL,
syncTable VARCHAR(32) NOT NULL
)
Then populate it using the View:
INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters
For performance and locking reasons you'll want a lot of index paths. I cannot say for sure what they should be, but here's what I would start with:
CREATE UNIQUE INDEX IX_adjUserFilters
ON dbo.adjUserFilters(userid, syncTable, RoadId)
CREATE INDEX IX_adjUserFilters_1
ON dbo.adjUserFilters(RegionID, userid)
CREATE NONCLUSTERED INDEX IX_adjUserFilters_2
ON dbo.adjUserFilters(RoadId, userid)
4. Add Maintenance Triggers to the Constituent Tables
You will need to add triggers to the Road, UserRegion and SyncSetting tables to keep the contents of [adjUserFilters] in synch with these tables when they are modified. They should look like this: First the Road table:
CREATE TRIGGER dbo.trRoad_MaintainUserFilters_IUD
ON dbo.Road
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
DELETE FROM adjUserFilters
WHERE RoadId IN(Select d.Id From deleted As d)
-- Now, Add in any rows implied by any INSERT or UPDATE rows
INSERT INTO adjUserFilters
SELECT v.RoadId, v.RegionID, v.UserId, v.syncTable
FROM vwUserFilters As v
JOIN inserted As i ON i.Id = v.RoadId
END
The UserRegion table:
CREATE TRIGGER dbo.trUserRegion_MaintainUserFilters_IUD
ON dbo.UserRegion
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
DELETE FROM adjUserFilters
WHERE EXISTS(
Select * From deleted As d
Where d.UserId = adjUserFilters.userid
And d.RegionId = adjUserFilters.RegionId
)
-- Now, Add in any rows implied by any INSERT or UPDATE rows
INSERT INTO adjUserFilters
SELECT v.RoadId, v.RegionID, v.UserId, v.syncTable
FROM vwUserFilters As v
JOIN inserted As i
ON i.UserId = v.userid
And i.RegionId = v.RegionId
END
And finally, the SyncSetting table:
CREATE TRIGGER dbo.trSyncsetting_MaintainUserFilters_IUD
ON dbo.syncsetting
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
DELETE FROM adjUserFilters
WHERE EXISTS(
Select * From deleted As d
Where d.UserId = adjUserFilters.userid
And d.[table] = adjUserFilters.syncTable
)
-- Now, Add in any rows implied by any INSERT or UPDATE rows
INSERT INTO adjUserFilters
SELECT v.RoadId, v.RegionID, v.UserId, v.syncTable
FROM vwUserFilters As v
JOIN inserted As i
ON i.UserId = v.userid
And i.[table] = v.syncTable
END
5. Create Your Replication Filter Query
Now you can create your Replication Query Filter for "user1" like so:
select StreetLight.*
from StreetLight As t
JOIN adjUserFilters As f
ON f.syncTable = 'StreetLight'
And f.RoadId = t.RoadId
Where f.userid = 1
-- (corrected)
As requested, this is the script you would use to synchronize the adjUserFilters on a periodic basis, instead of from Triggers:
DELETE FROM adjUserFilters;
INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters
Off hand, I am not sure how you would insure that this runs right before or as part of the Merge Synchronization Job, but it probably can be done.
So it works again...
Maybe someone else has got the same issue one day, so i post the solution here:
I researched on the server and found out, the sql server service is running under a local user. The reason for this is, that there were problems with the backupsystem, used by our customers and so they changed it years ago.
Because of the local user account the 15404-Error occures.
Knowing, that i mustn't use domain-accounts, I also solved the initial problem with my snapshot-agent. I searched for hours (nearly days ;) ) and it was just this little change:
When the Replication is created, the job is created too. The job has three steps. The Job-owner is the local-admin, also for the server-agent-service. But the second step of my job (replictionsnapshot) has one setting: run as. And by default this isn't the job-owner but the user running the creation, in my case my domain-account.
Now, that I set it to the local-administrator as well everything works fine again.
Thanks, Karl
Best Answer
There is a lot of information here,
And here,
So the thing to be aware of, because I was using parameterised filters the snapshot folder above doesn't contain any data, only schema.
Also I was not filtering correct using SUSER_SNAME() so the filtering was not using the snapshot at all causing me performance issues.
Once that was sorted I could see in the snapshot folder there subfolders with a snapshot for each user of the system. This data is the data filtered for that particular user.