I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this:
SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > '1/1/2009'
then the filter is applied remotely... However, I don't actually want to use '1/1/2009' as the date - I want the date to be supplied by a user-defined function, like this:
SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > dbo.MyCustomCLRDateFunction()
where the function is a custom CLR scalar-valued function that returns a date time... (You may ask why I need to do this... the details are a bit complicated, so just trust me - I have to do it this way.)
When I run this query, the remote query is NOT filtered remotely - the filtering is done after all of the data is pulled down (400,000 rows vs 100,000 rows) and it makes a significant difference.
Is there a way that I can force the query to do the filtering remotely?
Thanks!