REPOST: Merge Replication Conditional Filter with UDF Problem - Help !

Hi all,
I'm still stuck on this issue. Please find a re-cap below. I'd be really grateful if anyone
could shed some light on this.Thanks a million :o)
I am performing merge replication between SQL CE/Server 2000.
I would like to select the stores specific to each rep
unless the repid = 6, in which case I would like to select
all stores. Is this possible in merge replication?
I have been attempting to set the host_name() to my repID and
passing this Host_Name() into a table-valued UDF. The aim
being that the UDF will return a table of stores. My problem
is that I'm receiving an error and I would like to know if
UDF's accept Host_Name() as a parameter ?
My Dynamic Filter ...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.RepID IN (SELECT * FROM SELECT_Stores_RepID(Host_Name()))
The Error I'm receiving ...
Error 170: Line 1: Incorrect Syntax near '('.
Cube,this is my previous reply:"I have only been able to use UDFs before
when they are scalar, eg SELECT <published_columns> FROM [dbo].[Region]
WHERE region.regionid = dbo.fn_TaxRate(host_name())works fine. In your case
can you restructure your query to return a scalar value and then use this
value combined with another table in a join + where clause perhaps?"You
mentioned that you would give it a try and post back your results - how did
it go?Regards,Paul Ibison
|||Hi Paul,
I gave this a go but I didn't have much joy, maybe I'm just confused as to how to
go about things. As you mentioned I changed my Dynamic Filter so that I returned
the RepID ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] WHERE
CAST(FK_Rep_ID AS char(4)) = Host_Name()
Next I attempted to use this in an Extended Filter (which is where I ran into problems) ...
SELECT <published_columns> FROM [dbo].[tbl_Usr] INNER JOIN
[dbo].[Stores] ON Stores.RepID IN (SELECT * FROM
SELECT_Stores_RepID(tbl_Usr.TKT_RepID))
This product the syntax error ...
Error 170: Line 1: Incorrect Syntax near '.'.
This will work if I pass in a constant instead of tbl_Usr.TKT_RepID, which of
course I can't do. (At this stage you're probably saying to yourself...this guy just
tried the same thing he did in the Dynamic Filter...and is chasing his tail ;o)
I'm very open to trying alternative queries/approaches. At this stage I've looked at this
so many times I need some devine inspiration ;o)
Thanks in advance,
|||OK - I'll give it a try and post up an example later this afternoon (5pm UK
time).
Regards,
Paul
|||OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Hi Paul,
This is what I've been doing to date. But what I'd like is to select ALL records
if Host_Name() = some Rep ID set by the Merge Agent. Pseudocode...
IF (Host_Name() = 6)
SELECT * FROM Stores
ELSE
SELECT * FROM Stores WHERE RepID = Host_Name()
Maybe your filters do this, and I'm just missing the point ?
Thanks for the time you're giving me :o)
-- Paul Ibison wrote: --
OK, have tested it and it works. Here is how I did it:
I had 2 tables: MainTable and LookupTable
MainTable
ID,Description
1,aaa
2,bbb
3,ccc
LookupTable
ID,Description,Host
1,aaa,Host1
2,bbb,Host1
3,ccc,Host999
My filter is LookupTable.Host = Host_Name()
The tables are joined on LookupTbale.ID = MainTable.ID
I set the -HOSTNAME parameter on the merge agent to be Host1 and only 2
records from each table are replicated. This successfully mimics what you
are doing with the subquery.
Any questions, please post back.
Paul Ibison
|||Cube,
such logic is part of the linking table. If you had 3 stores, then the
linking table looks like this:
LookupTable
StoreID,Host
1,6
2,6
3,6
For the other hostnames there are records representing the corresponding
links:
LookupTable
StoreID,Host
1,6
2,6
3,6
2,999
HTH,
Paul Ibison