Data Provider or other service returned an E_FAIL status

_{Friday, August 1, 2008 10:55 PM}

I use a scheduled batch job to run a Visual Studio 2008 Professional Visual Basic executable querying data on SQL Server 2005 SP2 for each text file placed in a specified folder. The batch job executes the Visual Basic executable once for each text file found in the folder. The batch job runs every hour, 24 hours per day, 7 days a week and has been operating for successfully for several weeks with no upgrades or patches applied during the active period.

Today, a couple of hundred text files were placed in the folder and the batch job executed the Visual Basic executable a little over a hundred times and then for all files after that the subject error message was returned. Cannot manually execute (via Visual Basic debug) a previously successfully processed file without receiving the error message. The message occurs when the open recordset (open dynamic, optimistic locking, client side cursor) has added a new blank record, changed the values in the new record, and attempts to "update" the recordset.

I reviewed the 5 "related questions" provided by this forum, but none of the possible solutions worked. Because, I need to update the recordset, server side cursors are not appropriate.

8/4/08 06:00 AM

I stopped SQL Server 2005 service and restarted service to no avail. Error still occurs. Extremely frustrating as this process successfully processed over 100 text files for one batch job execution and then has failed ever since.

8/4/08 11:00 AM

After much chasing, the problem was traced to trying to set a text field in the query with a string larger than the size of the text field. While the error seems inadequate, I should still have caught this sooner. Especially since I know that I know the source of the information made to attempt to validate what was entered into the text.

_{Monday, August 4, 2008 3:12 PM ✅Answered}

I apologize for the posting. See edited version of posting.

_{Saturday, August 2, 2008 12:44 PM}

Not sure, chose to post in SQL Server because that is the service returning the E_FAIL status and several of the forum suggested solutions appeared to be posted to the SQL Server database engine forum.

_{Monday, August 11, 2008 8:34 PM}

Dear All,

I had just encountered the reported error message in VB6.0/IBM DB2. The error happens to show at the time of

OPEN statement... viz. rs.Open str_SQL, con, adOpenStatic, adLockReadOnly, adCmdText

where rs - ADODB.Recordset,

str_SQL - SQL Query

con - Connection String Object

 

Strange thing is that the same code does the work for with different set of parameters in the SQL but fails for a particular set of parameter that is always required to be there depend on the validation inplace.

I did checked the SQL and it works fine on the Command line interface and does retrieves the conditional records that it was prepared to. Also there is no tab space/space issue in the SQL tried changing all the joins. Am exhausted of all my options. Request your guidance. Kindly suggest

Regards,

Harsha

_{Monday, August 11, 2008 11:55 PM}

I wish I could help. My problem arose when I issued a rs.update and I had supplied 269 characters to a text field that could accept only 255 characters. I frequently get a query timeout that is caused because of locking problems. Using "adLockReadOnly" doesn't prevent locking the records requested by the query--I can usually fix this problem (if I don't have to update the recordset) by using the "WITH(NOLOCK)" option.

_{Tuesday, August 12, 2008 11:08 AM}

Thanks for the quick reply Joe. In this case, what could the best possible try be? or In your memory do you know any forum or other alternative where I could find a solution to my problem?

_{Tuesday, August 12, 2008 7:28 PM}

The only thing I could advise is to provide more information about the table structure and the query string and post on the MSDN forum whose sole purpose is to provide you direction as to the best forum to post your issue. That forum is called "Where Is the Forum For...?" and a hyperlink to the forum is http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=881&SiteID=1