Distributed query & OLE DB provider

Hi,

I hope I'm in correct forum..

I'm working on simple OLE DB provider that should be usable in SQL server as a linked server. The provider should provide some support for SQL (certain SELECTs for now) and therefore I'd like the SQL server to delegate portions of query processing to the provider instead of fetching all rows and doing SELECT on its own.

Alas, I'm no OLE DB expert and my provider apparently doesn't implement enough things to actually make SQL server pass the query to it. My provider's session currently implements mandatory interfaces + couple of others (IDBCreateCommand, IDBSchemaRowset) and via provider properties, I 'claim' to have full support for SQL92. I'm testing the provider with SQL Server 2005 ('Express edition').

I'd like to know what am I missing in the implementation, or whether/how can I find out what makes SQL server decide between fetching full rowset and creating a command to let rthe query run in provider.

Thanks,

L.

In general, if you use 4-part name query, the processing is done at the local server. That means the remote data is pulled to the local server before work is done. However, if openquery/openrowset/opendatasource() is used the remote processing is done on the remote server. This is what we often call as pass-through query.

If you're creating a custom oledb provider, what would be more important is the distributed transaction implementation. I suggest you check msdn on this.

|||

I just begun reading on the transactions yesterday evening, so I certainly shall dig into it bit deeper as you suggest, thanks .

Regarding 4-part name query, you wrote 'in general'. Is the processing 'always' done locally, or is there a way to make even 4-part name queries run remotely?

|||

If you have a remote filter and your provider is delegateable (see http://msdn2.microsoft.com/en-us/library/ms180972.aspx) only the filtered rows are returned.

e.g.

Code Snippet

select a.name,b.name
from master..sysdatabases a join remotesrv.master.dbo.sysdatabases b on a.status=b.status
where b.name like 'D%'

What going on here is the local engine delegates the following work to the remote server

Code Snippet

select name from master.dbo.sysdatabases

where name like 'D%'

instead of asking the remote server to return the entire table then do join and filter locally.

Note that the join is still done locally!

|||

Well, according to description available at the link, my provider 'should be' delegateable as it claims to support SQL 92 and supports the command. I suppose there's nothing like a tool to tell me 'yes' or 'no, you fail here and here to be SQL command provider'..?

However, I guess I might have a problem with collation rules as I haven't cared about them at all. I'll check it later today.

Thanks for help!