Forcing SQL Remote Query to a remote filter instead of local

Question

I have an MS SQL query that retrieves data from a remote server. The data I pull should be filtered by the date that is determined at runtime. When I run the query as follows:

SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > '1/1/2009' 

then the filter is applied remotely ... However, in fact, I do not want to use '1/1/2009' as a date - I want the date to be provided by a user-defined function, for example:

 SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > dbo.MyCustomCLRDateFunction() 

where the function is a custom CLR scalar function that returns the time of the date ... (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 - filtering is done after all the data has been pushed out (400,000 rows versus 100,000 rows), and this is significant.

Is there a way I can get the request to perform filtering remotely?

Thanks!

Answer 1

Can't you just send a request like this, or should the clr function really have to be called inside the select statement?

 Declare @datetime datetime Set @datetime = dbo.MyCustomCLRDateFunction() SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > @datetime 

Answer 2

You can also build a string and use 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 

Answer 3

You need to properly decorate your CLR function to mark it as deterministic, accurate, and data access / system data access as DataAccessKind.None.