Friday, October 10, 2008

Debug SQL conn, What is this 'Multiple-step OLE DB' error?

What is this 'Multiple-step OLE DB' error?

You may have seen this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.

This is often a datatype problem. Make sure you are passing valid datatypes to whatever is going on in the database. For example, check that you are not passing a NULL or empty string value to a column that doesn't accept them (either manually defined or, say, a DATETIME column). Make sure that all VARCHAR lengths and numeric bounds are adhered to. Make sure that you SELECT column1, column2, columns3 instead of using SELECT * (for other reasons, see Article #2096).

Danny's solution: I solved it by changing the field's datatype from varchar to nvarchar.

Turn on on error resume next, and check the errors collection of the connection object. For example:



<%
set conn = CreateObject("ADODB.Connection")
conn.open ""
sql = ""
on error resume next
conn.execute(sql)
if err.number <> 0 then
response.write(sql)
if conn.errors.count > 0 then
for each e in conn.errors
response.write e.description & "
"
next
end if
response.end
end if
%>

If this doesn't yield enough information, you might get a better error message out of the database itself, so take the results of the response.write statement and issue it directly against the DB. If you are using an ADODB.Command object, you might consider re-writing your code to execute the stored procedure directly, instead of using the command object. (If you are relying on output or return parameters from the stored procedure, you will have to alter the procedure to return those values as a resultset.)

If you are using "Persist Security info" in your connection string, try disabling it temporarily.

If you are using Driver={SQL Server} in your connection string, try Provider=SQLOLEDB instead (or vice-versa).

If you are connecting via an ODBC DSN, try using a DSN-less connection (see Article #2126).

If you are using the AddNew/Update methods of ADODB.Recordset, consider not doing so. Use an UPDATE or INSERT statement instead. If you must, make sure you do an .UPDATE or .CANCEL before attempting to continue further work on this or another record. See KB #294160 for more info.

For a couple of other possibilities, see KB #253157, KB #269495, and KB #228935.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing ?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?

No comments: