any one knows answer to this?
We were using MSACESS in our application before and our application used Multiple recordset from the same table. Recordset Refresh interval in MSACESS is 5 seconds. So we did not have problem with the multiple recordset getting other recordset changes.
Now we migrate to SQL server , we are opening recordsets as adDyamicset and it is not reflecting the changes happening in the table. What is the default recordset refresh interval in sql server ado provider and is their any method to force the refresh in ADO. Anyone knows answer for this issue??/
[559 byte] By [
ssivapra] at [2007-11-17 13:03:24]
# 1 Re: any one knows answer to this?
1. Try use server side dynamic cursor (adUseServer, adOpenDynamic).
2. Maybe with this locking method - adLockOptimistic.
You could requery recordset with Requery() method but it is recreating recordset and that could be slow. But dynamic cursor on server side has to work, we are using it in that way.
# 2 Re: any one knows answer to this?
We use our recordset for a lookup search in 2 tables which hold so much data. This look up takes place in 33,000+time in each thread. Before that I used plain OLEDB interfaces and it worked fine. Now we are using ADO find method. If I open the recordset with serverside cursor as dynamicset, my SQL server getting hammered ( it is taking 50% of the cputime). But if i use clientcursor beformance is excellent only problem is the refresh. Everytime I cant open and close the table or make query in SQL server. I just want lookup as part of my recordset not in SQL server. Anyone knows any techniques, I dont understand why dynamicset not reflecting other user( process) changes regardless of the server or client side cursor. These dynamic recordsets need to be updated by the ADO provider, otherwise what is the difference from snapshot?.
# 3 Re: any one knows answer to this?
Let explain you ADO & Recordset & dynamic cursor:
ADO creates recrodset for you, based on the cursor. Depends from the type of cursor.
Dynamic could be only server side, to give you oportunity see changes from other users. It is made with ADO stored procedures, at the bagining sp_cursoropen creates cursor and then it uses sp_cursorfetch to get next/prev row from recordset. It everything on server which consumes server resources a lot but saves netwiork traffic, client sent only commands and receives only data which is asking for.
Static cursor creates whole recordset and save it on server (if it is server site) or transfer it to the client (in case of server site). But recordset is created and all changes after are nto visible.
Take a look at this explanation:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5913&Key=ActiveX%20Data%20Objects%20%28ADO%29
I hope I was right.
# 4 Re: any one knows answer to this?
You suggest to use adOpenDynamic, adLockOptimistic and adUseServer. But I'm experiencing right now a problem with SQL Server 7.0 and this setting as I'm getting an error message "Provider does not support minimum binding requirements" as soon as I try to bind the defined (and opened) Recordset to a DataGrid (with adUseKeyset or adUseStatic binding works fine, but of course user changes are not reflected. See code extract below :
// using smartpointer interface from #import directive
m_spGrid = m_pGrid.GetControlUnknown(); // GridControl interface
_ConnectionPtr spKSConnPtr(CLSID_Connection);
spKSConnPtr->CursorLocation = adUseServer;
HRESULT hr = spKSConnPtr->Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=KundenStamm;Data Source=CSNWS542", _T(""), _T(""), -1);
_RecordsetPtr spKSRecSet(CLSID_Recordset);
_bstr_t queryString = _T("SELECT * FROM TableXY");
spKSRecSet->CursorLocation = adUseServer;
spKSRecSet->CursorType = adOpenDynamic;
spKSRecSet->LockType = adLockOptimistic;
spKSRecSet->Open(queryString, (IDispatch*)spKSConnPtr, adOpenDynamic, adLockOptimistic, adCmdText);
// So far everything is ok
// Binding of Recordset to DataGrid Control. Here I get the error message if adOpenDynamic used !!
m_spGrid->DataSource = MSDATASRC::DataSourcePtr(spKSRecSet);
Can you see anything wrong or do you know why SQL server could have a problem with dynamic/serverside cursors ?
Thanks Chris
# 5 Re: any one knows answer to this?
I thing that it is about behavior of server side cursor.
Client side means that all recordset is transfered to the client and then cuold be putted to DataGrid. But server side means that recordset is "on server" and you have to fetch all records one by one to the client and DataGrid could nto do that. I don't know how DataGrid works, but if it needs whole recordset at the beginning then you are in trouble if you want to see updates form other users in DataGrid.
# 6 Re: any one knows answer to this?
Another thing.
Dynamic cursor has diferent behavior, it doesn't support some properties / features, for example RecordCount property, or bookmarks. Possible is that DataGrid asks for some property which is not supported by dynamic cursor.
We are using only dynamic cursors in our application (we have to :o[[[).
# 7 Re: any one knows answer to this?
Yes, you were right. The datagrid supports Bookmarks, which are not supported by dynamic, serverside cursor of SQL server. In static or keySet mode they are supported.
But the main question to me remains : Is it possible to have 2 Client Apps using each a datagrid connected to the same ADO recordset on the DB server in a way that changes made (and updated on DB) by one user on machine 1 are also notified to the recordset on machine 2 ? According to msdn the cursor mode "adOpenKeyset" and "adOpenDynamic" should propagate changes from other users. But it does NOT ! Do you know a solution to that problem ?
# 8 Re: any one knows answer to this?
I'm sorry, I don't understand you. I looks like your are mixing two things. Please be more clearly, my english skills are not so good ;o))))
One thing is see changes made by another users and you don't see them is you are using dynamic cursor.
Another thing is problem with data grid, it doesn't work with certain cursor type - dynamic.
Am I right?
# 9 Re: any one knows answer to this?
The problem with this, that I have found, Chris is that you cannot assign server side cursors to a datagrid. If there is a method I haven't seen one yet. Have you considered the possibility of using a FlexGrid, and timer to refresh the text array?
Say every 3 seconds the TextArray is refreshed. This of course would depend on the amount of data that would be shown on the FlexGrid of course. You wouldn't want to refresh tons of data every 3 seconds and so forth.
Using a FlexGrid you can flash data from the server side cursor to the client side computer using TextArray. It is the only method I know to see the serverside cursor data on a Grid based data system. When the FlexGrid is updated... you have to update every single cell to the server, it will not automatically update like a Datagrid can. I'm more than positive that is why a Datagrid is not able to use server side cursors, since it is a realtime connection from client to server. Therefore it needs to be a clientside cursor pointing to the server.
I hope I'm right, if I'm not someone please help explain server side cursors to me a bit better...lol
