views:

229

answers:

3

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!

+1  A: 

You need to properly decorate your CLR function to mark it as Deterministic, Precise and Data Access/System Data Access as DataAccessKind.None.

Remus Rusanu
Hmm I thought marking it deterministic, but it isn't, really... in this case, it would return the same value MOST of the time, but certainly not ALL the time. I'm not sure what the others do... can you elaborate?
Michael Bray
Where does dbo.MyCustomCLRDateFunction manufacture the returned date from? Do you lookup tables? Does the output depend on the current time? If you cannot mark the output as deterministic, there are little chances to trick the query execution to filter remotely.
Remus Rusanu
Well I'm actually asking this single question, but the reality is that I have about 10 different functions that are used in different ways. Some depend on time, some don't, but not all of them are guaranteed to ALWAYS return the same value for a given set of arguments. Those that are, I will certainly mark them as such. Also, isn't DataAccessKind.None the default?
Michael Bray
A: 

Can't you just send a query like this, or does the clr function have to actually be called inside the select statement?

Declare @datetime datetime
Set @datetime = dbo.MyCustomCLRDateFunction()

SELECT * FROM SERVER.Database.dbo.RemoteView
WHERE EntryDate > @datetime
Gordon Tucker
You know what's funny? This same solution was suggested by another user, but I said I couldn't do it because the query is actually dynamically built by a WTF data layer that chooses the columns it wants to see, how to group the data, how to sort, etc... I didn't think I was going to be able to use this answer because it's all being executed as a constructed query. But in looking at it more, I think I just barely have enough flexibility in how it was built to get this solution to work. In testing, it certainly seems to work.
Michael Bray
OK so I've successfully implemented this in dev environment... As I thought, the WTF system had just enough flexibility that I was able to get this type of code inserted before the select. Lesson learned here is "don't immediately dismiss possible solutions without giving it some thought". Thanks!
Michael Bray
A: 

You could also construct a string and use an openquery ...

set @sqlString =
 ' select into myTable from openquery
    (remoteServer,
        "SELECT * FROM Database.dbo.RemoteView WHERE EntryDate > %DTSTART"
    )
 '

set @sqlString  = 
    replace(@sqlString, '%DTSTART', 
                        (select cast(dbo.MyCustomCLRDateFunction() as char(8)) 
           )

EXECUTE sp_executesql @stmt=@sqlString
cindi