| Groups | Blog | Home |
sql server replication : user defined function (UDF) with host_name() imput
as far as I recall, it's only scalar UDFs that can be used. If you need to create many values from the hostname, you can use a linking table and include that in the merge join filter. Please see the example below: This can be achieved using a lookup table as an additional article. To test this, I used 2 tables - Region and HostnameLookup - shown below. The HostnameLookup table defines the multiple values I'm interested in. I use dynamic filters to filter the HostnameLookup table using HOST_NAME() and a join filter to join to the region table. Editing the 2nd step on the merge agent's job to include: -Hostname PaulsComputer means that only 2 regions get replicated. I've listed the exact text below in case you want to recreate it to test. Region RegionID, RegionDescription, rowguid 1, Eastern, 9A6377F0-70DF-4A5C-962D-B41A41EFA82B 2, Western, C0CAEFAD-7B8C-43D6-87ED-920494A5C60C 3, Northern, 6CD9AA33-BE2D-4DCF-B447-745A3B86818E 4, Southern, B4826E41-96D6-457C-8645-DD4984AE3BF5 HostnameLookup RegionDescription, Hostname, rowguid Northern, PaulsComputer, 2367C78A-1001-417B-A3B1-1C74B23F8131 Southern, PaulsComputer, 4F563C4D-8479-4A7D-A938-490DD514F12A Filter Clause for HostnameLookup: SELECT <published_columns> FROM [dbo].[HostnameLookup] WHERE HostnameLookup.Hostname = HOST_NAME() Filter Clause for Region: < All rows published > Join Filter: Filtered table is HostnameLookup Table to Filter is Region SELECT <published_columns> FROM [dbo].[HostnameLookup] INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription = region.regiondescription Edit the 2nd step on the merge agent's job to include: -Hostname PaulsComputer Run the snapshot then merge agents and only 2 regions should be replicated. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Hi,
I need to create a UDF that I can call like this: select machinename from fn_Split2(host_name()) My UDF code is this: CREATE FUNCTION fn_Split2(@machinename varchar(11)) RETURNS @Strings TABLE ( machinename varchar(10) ) AS BEGIN declare @m varchar(11) set @m = @machinename insert @Strings (machinename) select @m RETURN END If I executed my call I get the errormessage: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '('. Can someone please help me? Please don't come with suggestions to get rid of the UDF because I need the UDF...it is for Dynamic Filtering in merge replication. Lars America
Paul,
Thank you for your comment but what I am trying to achieve is the following. At this moment we have such a lookup table that included 7.7 million records. The clustered index is on machinename and it is terribly slow if there are modifications on that table. Unfortunately this machinenames change on a regular basis. Therefor I want to find a way to get rid of this big table and after some reading I found that you can use a UDF with the parameter host_name(). "In the dynamic filter, you specify a Microsoft=AE SQL Server=99 2000 function or a user-defined function that is evaluated differently for each Subscriber based on the connection properties of the Merge Agent when the merge process is replicating data between the Subscriber and Publisher. The most common system functions used for this purpose are SUSER_SNAME() and HOST_NAME(). You can use a user-defined function in a dynamic filter, but unless the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), or the user-defined function evaluates one of these system functions in the filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function will be static. " This will also result into a dynamic filter. So this is what I am trying to achieve here. I hope you have another suggestion.
Paul, can you check this out?
I use a similar structure, & I'D LIKE TO ADD TO YOU EXAMPLE TO = ILLUSTRATE SQL'S BEHAVIOR THAT I'M WONDERING ABOUT.... (oops caps) Anyway: Region RegionID, RegionDescription, rowguid 1, Eastern, 9A6377F0-70DF-4A5C-962D-B41A41EFA82B 2, Western, C0CAEFAD-7B8C-43D6-87ED-920494A5C60C 3, Northern, 6CD9AA33-BE2D-4DCF-B447-745A3B86818E 4, Southern, B4826E41-96D6-457C-8645-DD4984AE3BF5 HostnameLookup RegionDescription, Hostname, rowguid Northern, PaulsComputer, 2367C78A-1001-417B-A3B1-1C74B23F8131 Southern, PaulsComputer, 4F563C4D-8479-4A7D-A938-490DD514F12A Northern, ScottsComputer, 5567C78A-1001-417B-A3B1-1C74B23F8131 Western, ScottsComputer, 5667C78A-1001-417B-A3B1-1C74B23F8131 Filter Clause for HostnameLookup: SELECT <published_columns> FROM [dbo].[HostnameLookup] WHERE HostnameLookup.Hostname =3D HOST_NAME() Filter Clause for Region: < All rows published > Join Filter: Filtered table is HostnameLookup Table to Filter is Region SELECT <published_columns> FROM [dbo].[HostnameLookup] INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =3D=20 region.regiondescription When I insert the new records in the HostnameLookup table, there is a = delay (it much be scanning the partitions, or ???)=20 And the overlapping partions are resent to the existing hostnames.... In this example Northern gets resent to PaulsComputer.... Is there a way to avoid this? In our large deployment, adding a regional manager (may replicate 20 = territories, it affects 20 laptops that have the single teritorry in = this group) Thanks!! [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message =
news:u9nbKRkLGHA.2912@tk2msftngp13.phx.gbl... > Lars, >=20 > as far as I recall, it's only scalar UDFs that can be used. If you = need to=20 > create many values from the hostname, you can use a linking table and=20 > include that in the merge join filter. Please see the example below: >=20 > This can be achieved using a lookup table as an additional article. > To test this, I used 2 tables - Region and HostnameLookup - shown = below. The=20 > HostnameLookup table defines the multiple values I'm interested in. I = use=20 > dynamic filters to filter the HostnameLookup table using HOST_NAME() = and a=20 > join filter to join to the region table. Editing the 2nd step on the = merge=20 > agent's job to include: -Hostname PaulsComputer means that only 2 = regions=20 > get replicated. I've listed the exact text below in case you want to=20 > recreate it to test. >=20 > Region > RegionID, RegionDescription, rowguid > 1, Eastern, 9A6377F0-70DF-4A5C-962D-B41A41EFA82B > 2, Western, C0CAEFAD-7B8C-43D6-87ED-920494A5C60C > 3, Northern, 6CD9AA33-BE2D-4DCF-B447-745A3B86818E > 4, Southern, B4826E41-96D6-457C-8645-DD4984AE3BF5 >=20 > HostnameLookup > RegionDescription, Hostname, rowguid > Northern, PaulsComputer, 2367C78A-1001-417B-A3B1-1C74B23F8131 > Southern, PaulsComputer, 4F563C4D-8479-4A7D-A938-490DD514F12A >=20 >=20 > Filter Clause for HostnameLookup: > SELECT <published_columns> FROM [dbo].[HostnameLookup] > WHERE HostnameLookup.Hostname =3D HOST_NAME() >=20 > Filter Clause for Region: > < All rows published > >=20 > Join Filter: > Filtered table is HostnameLookup > Table to Filter is Region >=20 > SELECT <published_columns> FROM [dbo].[HostnameLookup] > INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =3D=20 > region.regiondescription >=20 > Edit the 2nd step on the merge agent's job to include: -Hostname=20 > PaulsComputer >=20 > Run the snapshot then merge agents and only 2 regions should be = replicated. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) >=20
I ran into the same problem, also i tried this user fuction, but it only
filtes correctly on the first sync, BOL also states this static behavior: Do not create row filters that mimic join filters. It is possible to create row filters that mimic join filters by using a subquery in a WHERE clause, such as: Copy Code WHERE Customer.SalesPersonID IN (SELECT EmployeeID FROM Employee WHERE LoginID = SUSER_SNAME()) It is strongly recommended that all such logic be expressed in a join filter rather than a subquery. If your application requires a row filter to use a subsquery, ensure that the subquery only references lookup data that does not change. [quoted text, click to view] <lars.america@gmail.com> wrote in message Paul,news:1139578391.461158.248510@g14g2000cwa.googlegroups.com... Thank you for your comment but what I am trying to achieve is the following. At this moment we have such a lookup table that included 7.7 million records. The clustered index is on machinename and it is terribly slow if there are modifications on that table. Unfortunately this machinenames change on a regular basis. Therefor I want to find a way to get rid of this big table and after some reading I found that you can use a UDF with the parameter host_name(). "In the dynamic filter, you specify a Microsoft® SQL ServerT 2000 function or a user-defined function that is evaluated differently for each Subscriber based on the connection properties of the Merge Agent when the merge process is replicating data between the Subscriber and Publisher. The most common system functions used for this purpose are SUSER_SNAME() and HOST_NAME(). You can use a user-defined function in a dynamic filter, but unless the user-defined function definition includes SUSER_SNAME(), HOST_NAME(), or the user-defined function evaluates one of these system functions in the filter criteria (such as MyUDF(SUSER_SNAME()), the user-defined function will be static. " This will also result into a dynamic filter. So this is what I am trying to achieve here. I hope you have another suggestion. begin 666 copycode.gif M1TE&.#EA#P`/`.9F`).BM[+"V*[$\(&NY:Z_U'.AWER,VIBKQBE4JIFLQ_S] M_LO/U)*P[8JN_,73\JFXSF2%R=;>[/;Y_>OO]BM7L6Z<VB)&CZ:]["!!AJ"Y M[VB7U9RPSM+;\9>FNZ.RR"-(EOGZ_")%CIRKP;+)\*[0_Z^^U#5EQ2E4K*FY MSX*?V,_9YM+6W9JMRFZ7YV".Y\S7[-??ZINOS)"JX+K:_^KO^R5,FR=0HIJN MRI:FN^;J\^#F\25-G25*F+_+W=+>\,#*X,+.Y/7V^[K%U/O]_B-'DG^G\_#T M^\?2X7.2T4=NNI_%^B%#B=OD]_?X^Z_#[?#R^)>IQ*K![XZX\"%#BIRUYZ*Q MQV:"I?#S^*.RQYJNS*O"[]/>]55YP2I7LW*:ZH6GZR=0I%MRC[[.\#5)8U&# MT?_______P`````````````````````````````````````````````````` M`````````````````````````````````````````````````"'Y! $``&8` M+ `````/``\```>?@&:"@X2%AH>"`0$$! \>*X@!99-E558+AR64"CA"89B% M*&4S)#X``EI1&0P,+X)8DR!/.0*495\F0&8B94I2`P47MEXC%#\="D$3.A%4 M90T-'"Y=)V `3< 5&C*41DP.63L`5S J1STI944M!F1D-SP`8_/S2&5#-%MB F3@E$AA!EVKDC<R"$(2X2-L1@D> `E"6&DB! 8*/&!PM3, 0"`#L` ` end
As far as I know, my solution is the only one that will achieve the split
you're attempting. Currently you are using a table valued UDF and I've only ever seen scalar ones in the filter and believe they are the only type that work. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Scott,
please have a look at @keep_partition_changes. I think this might be the = solution. Hilary knows more about this and he posted me a solution that = is relevant but I didn't yet get round to looking at it (sorry Hilary). Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book:
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |