Dynamic filter using a UDF

In BOL it says that you can use a UDF in a dynamic filter, passing in one of
the system UDFs. However, when I try this I get a syntax error, e.g.
SELECT * FROM [dbo].[SiteData]
WHERE SiteId IN (SELECT Value FROM dbo.Split(HOST_NAME(), ','))
where dbo.Split is a UDF that splits a list of values into a table
It doesn't seem to like the brackets after HOST_NAME, but the example shown
in BOL is MYUDF(HOSTNAME())
Any ideas what I've got wrong with the syntax?
TIA
Paul
Paul,
the only way I've been able to resolve a system function to multiple values
is to use a linking table and replicate it as well. Have a look at the
example in the merge section of www.replicationanswers.com where there is a
related example.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
Thanks for that, I can make it work in my scenario - BTW you also quote the
MYUDF(HOST_NAME()) solution as well further down the page.
Regards
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236k7vXQxEHA.3612@.tk2msftngp13.phx.gbl...
> Paul,
> the only way I've been able to resolve a system function to multiple
> values
> is to use a linking table and replicate it as well. Have a look at the
> example in the merge section of www.replicationanswers.com where there is
> a
> related example.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||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)
|||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)
>
|||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...
>
|||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...
>