Re: Dynamic filter using a UDF

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul Hatcher (phatcher_at_nospam.cix.co.uk)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 13:24:34 -0000

Because it says in BOL that you can't use dynamic snapshots if neither of
HOST_NAME/SUSER_NAME() functions are not specified in the row filter. The
scenario is a largish central database with a bunch of subsidiary offices,
some of which are on dial-up, so I'm trying everything to minimise the
amount of time taken to do replication and the amount of data on the wire.

I'll try it anyway and see what happens; the other point is that I've come
across Transact-SQL oddities like this before, and it's a good idea to get
rid of them if at all possible.

Thanks

Paul

"Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:OX8lbLaxEHA.1400@TK2MSFTNGP11.phx.gbl...
> why don't you put the hostname within your function as opposed to
> supplying it as an argument.
>
> "Paul Hatcher" <phatcher@nospam.cix.co.uk> wrote in message
> news:OHJ9%238YxEHA.2624@TK2MSFTNGP11.phx.gbl...
>> I'll need to check this out in SQL 2005 beta to see if it's fixed or if
>> anyone can give a reason why ...
>>
>> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
>> dbo.Split('1, 2', ','))
>>
>> compile (and runs), but
>>
>> SELECT * FROM [dbo].[SiteData] WHERE SiteId IN (SELECT Value FROM
>> dbo.Split(HOST_NAME(), ','))
>>
>> does not, whereas
>>
>> SELECT * FROM [dbo].[SiteData] WHERE SiteId = MyUdf(HOST_NAME())
>>
>> compiles again.
>>
>> It's nothing to do with replication per se, but how the complier is
>> parsing the statements.
>>
>> Thanks
>>
>> Paul
>>
>> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message
>> news:4d0d01c4c575$382daee0$a301280a@phx.gbl...
>>> Paul,
>>> I'll edit the page to make clearer, but I've only used
>>> the MYUDF(HOST_NAME()) solution for scalars.
>>> Rgds,
>>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>>
>>> (recommended sql server 2000 replication book:
>>> http://www.nwsu.com/0974973602p.html)
>>>
>>
>>
>
>

Relevant Pages

  • Re: block access to forms vba
    ... > same thing--no vba access. ... but replication is entirely designed for two-way synchronization. ... you do not turn off compile on demand. ... close the instance of Access used to decompile. ...
    (microsoft.public.access.replication)
  • Re: NTFRS errors after rebuilding DC
    ... Paul thanks for the help. ... > d:\i386\adminpak.msi (Server tools for remote management of servers) ... >> DC1 is a master browser, but this computer is not a master browser. ... >> The File Replication Service is having trouble enabling replication from ...
    (microsoft.public.win2000.active_directory)
  • Re: Microsoft Class 2591
    ... I completely understand the amount of work required of a class like this. ... Edssentially, will be showing them the components of replication, how to set ... > also you'll not get the PowerPoint presentation - that's in the teacher ... > Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • Re: Updateable Subscriptions - Im a little confused
    ... Actually, Paul, I should clarify what I meant by DTS'ing. ... > the subscriber is to be treated as read-only. ... again with subqueries and the A linked server. ... > replication setup - it doesn't use this methodology but results in the ...
    (microsoft.public.sqlserver.replication)
  • Re: Initiating Replication Between AD Direct Replication Partners
    ... Paul pretty much laid it out for you. ... There are three Naming Contexts - or Partitions - ... and europe.nkdsolutions.com) then each of the six Domain Controllers would ... It is also very important to understand that Active Directory Replication ...
    (microsoft.public.win2000.active_directory)