Merge Replication Conditional Filter with UDF Problem

I'm performing Merge Replication between SQL Server 2000/SQL CE using Dynamic Filters.
I would like a conditional filter based on the Host_Name(). I've written and tested a UDF
in Query Analyzer called SELECT_Stores_RepID. In the Dynamic Filters properties for the
Stores Table I've tried many different things such as:
SELECT <published_columns> FROM [dbo].[Stores] WHERE dbo.SELECT_Stores_RepID (HOST_NAME())
All them give me errors. My question is what is the correct syntax to use my UDF? The documentation
on this is very poor.
Cheers
it should be something like this
select <punished_columns> from <<Table>> where au_id in (select au_id from dbo.Authors_filter('CA'))
There may be more efficient ways of doing this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||Sorry typo in my post ...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
Should be (extra bracket)...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
Still getting the same problem as before.
-- Cube wrote: --
Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||can you post your UDF.
BTW - I should warn you that UDF's don't always offer the best performance. Also Host_Name() is evaluated where the merge agent is run. If you kick it off on EM runing it on your desktop it will evaluated to your machine name.
If you kick if off on the subscriber as a pull subscription it will resolve correctly.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I'm still getting this error in Replication. Does anyone know if its a known issue?
The error is that when I call my UDF below in my Dynamic Filter and pass in
HOST_NAME() I get an incorrect syntax near '(' error.
Ciaran
-- Cube wrote: --
Hi Hilary,
Please find below my UDF. The RepID in my table is an int, which I CAST so I can
compare it to my Host_Name(). I'm interested to know your opinion. Also, if UDF's
don't offer best performance, do I have other options apart from creating a seperate
publication?
ALTER FUNCTION dbo.SELECT_Stores_RepID
(
@.inRepIDchar(4)
)
RETURNS @.Stores TABLE (StoreID int)
AS
BEGIN
IF (@.inRepID = '8')
INSERT INTO @.Stores
SELECTStoreID FROM Stores
ELSE
INSERT INTO @.Stores
SELECTStoreID
FROM Stores
WHERE CAST(Stores.RepID AS char(4)) = @.inRepID
RETURN
END
BTW - Some history behind all this. I have some HandHeld Devices deployed with my custom app.
The Replication depends on the RepID, which is set through the Host_Name() on the device, pulling
back the users specified data. Now, there is a requirement to pull back all data in some cases
e.g. management, without modifying the software on the client. Henece, the reasons for the
IF RepID = X conditional filter.