Does asynchronous operation supported by oledb provider for openquery?
in [Programming SQL]
|
Prev: Query question - Separating Columns?
Next: Available well experienced consultants for your positions
From: greenxiar on 8 Jun 2010 15:03 I want to develop an oledb provider for distributed data collection via network. The result rowset is forward readonly and dynamic collumns. Because return data is time consuming that asynchronous populate is needed. My question is: 1 Does asynchronous operation is supported in linked server for custom oledb provider? 2 Is there any specification of the asynchronous oledb provider for sql server openquery? Thanks Greenxiar
From: Erland Sommarskog on 8 Jun 2010 17:53 greenxiar (greenxiar(a)hotmail.com) writes: > I want to develop an oledb provider for distributed data collection via > network. The result rowset is forward readonly and dynamic collumns. > Because return data is time consuming that asynchronous populate is > needed. > > My question is: > > 1 Does asynchronous operation is supported in linked server for custom > oledb provider? No. How would that work? T-SQL is a synchronous language; one statement must complete before the next can start executing. There is no support for threads, interrupts or asynchronous operations. Maybe you could leverage on the CLR; in a CLR stored procedure you can use thread and maybe also asynchronous operations. Still it leaves the question: while the rowset is being populated, what will you do in the mean time? > 2 Is there any specification of the asynchronous oledb provider for sql > server openquery? Since there is no such thing, there is no such specification. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: greenxiar on 9 Jun 2010 01:59 "Erland Sommarskog" <esquel(a)sommarskog.se> ??????:Xns9D91F31B1812AYazorman(a)127.0.0.1... > greenxiar (greenxiar(a)hotmail.com) writes: >> I want to develop an oledb provider for distributed data collection via >> network. The result rowset is forward readonly and dynamic collumns. >> Because return data is time consuming that asynchronous populate is >> needed. >> >> My question is: >> >> 1 Does asynchronous operation is supported in linked server for custom >> oledb provider? > > No. How would that work? T-SQL is a synchronous language; one statement > must complete before the next can start executing. There is no support > for threads, interrupts or asynchronous operations. What is the "complete" meaning? What is the Specification of "Complete" for OleDB Provider? The returning rowset object never tell total count of result. In OleDB Provider means to check the end of rowset: 1 DB_S_ENDOFROWSET returned from IRowset::GetNextRows in sync mode. 2 Get DBASYNCHPHASE_COMPLETE from IDBAsynchStatus::GetStatus in async mode. 3 Listen S_OK of IDBAsynchNotify::OnStop in async mode. Does SQL Server never use IDBAsynchStatus/IDBAsynchNotify to check the end of rowset and only used the 1 means: check DB_S_ENDOFROWSET returned by GetNextRows? If SQL Server use any of IDBAsynchStatus/IDBAsynchNotify intefaces in openquery, then SQL Server support asynchronous operation. I donot know the internal code of SQL Server handling the OleDb Provider for open query. Obvious SQL SERVER does not support full features of OLEDB Provider. Is there any guide for developing oledb provider for SQL Server, tell me where is the limitation and restriction, and what oledb features does not supported for SQL Server? > Maybe you could leverage on the CLR; in a CLR stored procedure you can > use thread and maybe also asynchronous operations. Still it leaves the > question: while the rowset is being populated, what will you do in > the mean time? 1 CLR TVF only support fixed columns. 2 When i implement my custom IEnumerable and IEnumerator to dynamicaly fetching data with "ON DEMAND", the SQL Server has navigated all the records before it transfered the result. That is to say, i should fully prepared and cache data before returning the IEnumerable interface to SQL Server. CLR TVF is never a asynchronous mean. My project do not required result consistancy because it collect data from many unstable node. But the returning records from every node has massive count. I joint them in local indexed table. As OleDb Provider populating, new incomming data from other node will be continue. When timeout, stop populating and end rowset. Its important that SQL Server never stop join operation when a lot of massive records not coming. > >> 2 Is there any specification of the asynchronous oledb provider for sql >> server openquery? > > Since there is no such thing, there is no such specification. My requirement is quite clearly: asynchronous fetching data from rowset from oledb provider. Does SQL Server simply check DB_S_ENDOFROWSET by calling GetNextRows to end the result fetching? > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: Erland Sommarskog on 9 Jun 2010 17:57
greenxiar (greenxiar(a)hotmail.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> >> No. How would that work? T-SQL is a synchronous language; one statement >> must complete before the next can start executing. There is no support >> for threads, interrupts or asynchronous operations. > > What is the "complete" meaning? What is the Specification of "Complete" The specification of complete is that if a stored procedure includes five statments: STATEMENT 1 STATEMENT 2 STATEMENT 3 STATEMENT 4 STATEMENT 5 SQL Server will execute those statements in order, and it will not start to execute one statement, then start the next statement and then come back to the previous statement later. > Does SQL Server never use IDBAsynchStatus/IDBAsynchNotify to check the > end of rowset and only used the 1 means: check DB_S_ENDOFROWSET returned > by GetNextRows? If SQL Server use any of IDBAsynchStatus/IDBAsynchNotify > intefaces in openquery, then SQL Server support asynchronous operation. Exactly how SQL Server employs the OLE DB interface is not likely to be public information, and it may also be subject to changes between different versions of SQL Server. If you want to find out, I would guess the only reason is to write an OLE DB provider and add debug logging to it, so that you can see which interfaces it uses. (Well, you don't have to write one from scratch. You could take one of the sample providers out there.) But again, if you have a query like SELECT ... FROM OPENQUERY (....) There is little point with being asynchronous. What would SQL Server do in the meanwhile? Sure, it can run threads for other processes, but the particular connection running OPENQUERY will be stalled. > 1 CLR TVF only support fixed columns. So do OPENQUERY. Or more precisely, when the query with OPENQUERY is compiled, the column has to be known, or else it will not work out well. > My project do not required result consistancy SQL Server is quite interested in result consistency, so there may be a clash of philopsohpies there. > because it collect data from many unstable node. But the returning > records from every node has massive count. I joint them > in local > indexed table. Somehow I feel that you should receive the data in a client that then uses a bulk-copy interface to send them to SQL Server. > When timeout, stop populating and end rowset. Its important that SQL > Server never stop join operation when a lot of massive records not > coming. I'm note sure what you have in mind here, but it does not sound like it would work out. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |