SQL Server Performance Forum – Threads Archive

Sporadic "Data provider…E_Fail Status"

The system in question is a ASP/SQL Server web application. It has been in production for about a month now and most of the time, there are no problems at all. I know because I’ve set up the system to send e-mails on all errors generated from the DB communication and they don’t come often. However, sporadically (5-6 times now), I’ve recieved an e-mail that states: -2147467259: Microsoft Cursor Engine
Data provider or other service returned an E_FAIL status. Because of some other debugging information in the e-mail, I know where this is coming from. Keep in mind the functions shown simply error trap the actual commands, which will be explained in italics: Note: The actual code will be in bold, while comments in italics. ‘Step 1: Init the Recordset
call InitRS(pConn, tmpRS, "FormsArchive", 3)
‘InitRS executes tmpRS.Open(sSQL, pConn) after setting parameters
‘FormsArchive is sSQL, the 3 simply specifies the following parameters
‘tmpRS.CursorType = 1 ‘adOpenKeyset
‘tmpRS.LockType = 2 ‘adLockPessimistic
‘tmpRS.CursorLocation = 3 ‘adUseClient ‘Step 2: Get the recordset ready for the Add
if not RSAction(tmpRS, "Add") then DeliverErrorMessage(pConn)
‘"Add" specifies to do an tmpRS.AddNew
tmpRS.Fields("FA_FDFFile") = pFileName
tmpRS.Fields("FA_PDFID") = pFileID
tmpRS.Fields("FA_Description") = pFileDesc
tmpRS.Fields("FA_Timestamp") = Now()
tmpRS.Fields("FA_Userstamp") = Session("User") ‘Step 3: Complete the Add and update the DB
if not RSAction(tmpRS, "UpdateBatch") then DeliverErrorMessage(pConn)
‘"UpdateBatch" specifies to do an tmpRS.UpdateBatch I don’t know exactly where the error is coming from (Step 1, 2, or 3), but my guess is that is comes from Step 3. Any ideas?

You are using a client side cursor (adUseclient) combined with rs.Add. This means that the data is transfered over to ADO’s own cursor engine (located on the client = web server RAM in this case) that always is adOpenStatic. Then you add some data on the recordset before issuing an UpdateBatch. During the meantime data could have changed on the server and no longer be valid when UpdateBatch is executed. I would implement some error handling here to catch any errors in the ADO collection when issuing an UpdateBatch. I would also avoid using rs.Add etc and use SQL delete/insert/update statments instead, either with dynamic SQL or stored procedures. Using rs.Add etc is really slow in a multiuser environment like a web site. This was more common before when building VB applications. If you can’t change the code to avoid rs.Add etc I would switch to a server side cursor (tmpRS.CursorLocation = 2 ‘adUseServer). This way the connection will keep locks on the data so other connections can’t change it until it’s done. This can lead to poor concurrency though but you are already on that path by using rs.Add and adLockPessimistic. But start by checking the ADO error collection after tmpRS.UpdateBatch. You should get more detailed information about the error there. If it’s not that the data has changed before issuing rs.UpdateBatch it could be that the date format on the server sometimes it not compatible with SQL servers datetime data type when you are issuing "Now()" in your code.

http://support.microsoft.com/default.aspx?scidhttp://support.microsoft.com:80/support/kb/articles/Q235/0/53.ASP&NoWebContent=1 see whether this is related to fix the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the help so far. I definitely will try to change the cursor location and lock type to see what’s happening, but in Step 3, I do check the error collection. If there are any errors, RSAction returns false, which calls DeliverErrorMessage(), which sends the e-mail I was referring to in my original description (the e-mail dumps the connection object error collection). I get no other errors along with this one and even this one is sporadic. Does the fact that this works 100 times in a row, then returns the E_Fail message on 1, then works for a hundred more change your analysis? We are running this over a network, so it’s probable that the network is slower at times, but wouldn’t I see a timeout error or something more descriptive? I think you might be on the right track with the cursor location, but in a "Add", the record doesn’t exist because I’m adding it, so what changes on the server would impact the cursor? The code above that produces the error is adding a new record, so I’m not following exactly what you think is going on. Can you elaborate a little more about the cursor location theorey? Thanks!

Ok. Assumed you fetched some data and then added more in the recordset. A few questions:
– What MDAC version are you running (on the web server and sql server) – What does your connectionstring look like (are you using ODBC driver or an OLEDB provider)? OLEDB is often to prefer. – Can any of the values in the recordset you add be empty? Depending on MDAC version this can be handled a bit different. Still it looks like a provider error so I would try upgrade MDAC, switch to OLEDB if it isn’t already and try with a server side cursor. Try with OLEDB first, if it doesn’t help switch to server side cursor and last upgrade MDAC since that has most impact on the server.

Actually, we are using ODBC to connect and our web server has MDAC 2.7. I know it’s not the latest versions, but it turns out that wasn’t the problem. The problem was only happening in production, so I changed several things at once to try an minimalize the impact (so, now that it’s fixed, I don’t know exactly what change fixed the problem). I made the following changes:
* I changed the recordset to a server side cursor
* I changed the lock type to optimistic rather than pessimistic
* Added an index on the table
* closed the tmpRS object before using it for this Insert The function now returns after a second or two (typical web server response for our WAN). Thanks for all of your input and help.

]]>