Thread: Classic ASP FAQ

This is an index of the most common classic ASP-related errors that users experience. Please search through this thread for a possible solution before posting your question.

This is not yet complete. There is a lot of information available and I will post as time allows.

Why do I get 8002000A errors?
Why do I get 80040200 / 80040514 / 800A0E7A errors?
Why do I get 80040e09 errors?
Why do I get 80040E0C errors?
Why do I get 80040E10 errors?
Why do I get 80040E14 errors? (Part 1)
Why do I get 80040E14 errors? (Part 2)
Why do I get 80040E21 errors?

Reference: 8000XXXX Errors

Provider error '8002000a'
Out of present range

This is typically because a numeric value that is too large for the data type has been passed when using an ADODB.Command. For example, sending 2^32 to a parameter that is specified as adInteger, will cause this error.

This series of errors includes the following errors messages:

ADO could not find the specified provider.

Provider cannot be found. It may not be properly installed.

Data source name not found and no default driver specified.

Unspecified runtime error.

Library not registered.

Class not registered.

If you receive one of these errors you should first check your connection string as there is most likely a typographical error.

You should also ensure you have the most recent version of MDAC installed, which can be downloaded here: MDAC Download

With Microsoft Access, this can be caused when trying to connect to the database using OLEDB, (e.g. Provider=Microsoft.Jet.OLEDB.4.0, without having the JET components installed. Microsoft stopped shipping the JET files with MDAC, starting at 2.6.

Connection pooling may cause the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040200'
[Microsoft][ODBC Driver Manager] Only SQL_DRIVER_NOPROMPT is allowed when connection pooling is enabled

The prevent this error, you can override the prompt property of the connection object:

Code:

<% 
    const adPromptNever = 4 
    set conn = CreateObject("ADODB.Connection") 
    conn.Properties("Prompt") = adPromptNever 
    conn.open yourConnectionString
    ... 
%>

When using Microsoft Access databases, it is common to see the following message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

or

Microsoft JET Database Engine error '80040e09'

The following related messages may also be seen:

Record(s) cannot be read; no read permission on '<table name>'.

or

Cannot update. Database or object is read-only.

Ensure that the Internet User Account (IUSR_machinename) has Read/Write permissions on the database.

With SQL Server, you may receive the following message:

[Microsoft][ODBC SQL Server Driver][SQL Server]
EXECUTE permission denied on object '<proc name>', database '<database name>', owner '<owner name>'.

This may also be related to permissions, so you should ensure that the user you're connecting to the database as has appropriate permissions on the appropriate database objects.

Some databases may give this error if table or column names have quotes around them. Quotes should be used for string literals only, not for object names.

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'
Command text was not set for the command object.

This error is typically caused by an empty SQL string. For example:

Code:

<%
    set conn = Server.CreateObject("ADODB.Connection")
    
    strSQL = "UPDATE users SET IsActive = 1 WHERE userID = 12:
    conn.Execute(SQLstr)
 
    ...
%>

Notice above, how strSQL was flipped to SQLstr, thus causing an empty SQL string to be passed to the connection object. Errors like this can be avoided by using Option Explicit. You will get another type of error, stating that the variable is "undefined". This should lead you to the solution.

The following errors are typically seen when creating SQL statements:

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

or

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected <n>.

or

Microsoft OLE DB Provider for SQL Server error '80040e10'
Procedure '<procedure>' expects parameter '<param>', which was not supplied.

This can be caused by one of the following things:

1. One or more of the values was blank
2. A field name was spelled incorrectly
3. You tried to pass the wrong datatype (i.e. a string was passed as an integer by not placing quotes around it or a numeric value was passed as a string by placing quotes around it)

To troubleshoot this error, you can use response.write to write your SQL statement to the screen:

Code:

<%
    strSQL = "INSERT INTO usersToGroups VALUES("1", 3)"
    Response.Write(strSQL)
    Response.End
%>

Response.Write is vital when troubleshooting SQL statements, as well as other code.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot insert the value NULL into column '<column>', table '<table>';
column does not allow nulls. INSERT fails. (or UPDATE fails.)

This error is self-explanatory. If you try to INSERT/UPDATE a column with a NULL value, and that column does not allow NULL values, you will get this error.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot resolve collation conflict for EQUAL TO operation.

or

Cannot resolve collation conflict for UNION operation.

You will get this error if you are using #temp tables and the collation on the tempdb does not match that of the databases you are working in. The collation of all affected databases should match or at least be compatible.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Argument data type text is invalid for argument <n> of <function> function

There are several string functions that cannot be performed against columns that have TEXT or NTEXT datatypes.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid object name '<objectname>'.

If the object in question does exist and you are in the correct database, then this is probably an issue with permissions.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Syntax error (missing operator) in query expression '<expression>'

or

Syntax error in INSERT INTO statement

or

Syntax error in UPDATE statement

or

Syntax error in FROM clause

or

Syntax error in WHERE clause

or

Line <n>: Incorrect syntax near '<character>'.

The possible reasons these errors could happen are:

1. You used a reserved word as a column name or alias name (or used a column name that begins with a number or non-alphanumeric character)
2. You didn't delimit a value properly
3. There really is a syntax error in your SQL statement

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation

This could be for the same reasons as above, or it could be that you are using an ADODB.Command object and are attempting to pass a string to an INT parameter or vice-versa.

Microsoft OLE DB Provider for ODBC Drivers (0x80040e14)
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

You will have to verify your query and table structure to determine why you may be violating one of these relationships.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

You used sp_rename, but tried to execute it against a column or other object that doesn't exist (or the syntax is wrong).

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Undefined function 'NZ' in expression.

Several functions available within Access are not available via ADO/JET providers.

Microsoft JET Database Engine error '80040e14'
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

This can happen if you try and use syntax like SELECT TOP 10 ... WITH TIES, or other syntax from SQL Server that is not directly portable to Access.

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.

This could be from using a reserved word as a column or expression name, or it could be a real bonafide syntax error. Or, it could be that you are trying to send a batch of statements to Access. While the following will work with SQL Server, Jet prohibits it:

Code:

conn.execute "INSERT INTO tbl1 VALUES(1); INSERT INTO tbl1 VALUES(2)"

In order to prevent this error, you will need to send the two statements separately.

Microsoft OLE DB Provider for SQL Server error '80040e14'
SQL Web Assistant: Could not open the output file.

This is usually caused when you are using a web task to modify existing HTML files which are also in use by IIS. One workaround would be to cycle between two filenames... active and inactive. Flip which one is 'current' every time the web task runs, depending on the frequency of the web task, this will reduce the chance that someone will still have the inactive file open when you make the other file active. In addition, you could delete the inactive file after each run of the web task, to make it even more unlikely that IIS will have a lock on the file.

This can also happen if the account that the SQL Server and SQL Server Agent services don't have sufficient privileges on the folder where the web task outputs its file.

Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot create a row of size <n> which is greater than the allowable maximum of 8060.

This can happen if you have a table that is defined to allow more than 8060 characters per row (SQL Server warns you about this when creating the table, but allows you to create it nonetheless). This kind of structure can be useful if, say, you have two different VARCHAR(8000) columns where only one of them could possibly contain that much text. If you try to insert 8000 characters into both columns, you get the above error. Your SQL statements need to be constructed with logic that carefully insulates them from exceeding the physical bounds of the table. If you feel you might need to exceed 8060 characters in a single row, consider storing the characters off-row (e.g. in a TEXT/NTEXT column).

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Could not allocate space for object '<object>' in database '<database>' because the 'PRIMARY' filegroup is full.

This error happens for one of two reasons. Either the disk where the data is stored is full, or the database is not set to auto-grow and it has reached capacity. If the former, you will need to free up space on the drive (or move the data files to a different location). If the latter, you will need to set the database to auto-grow, or clear out stale data and perform a shrink.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint.
or
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
or
The current query would generate a key size of <n> for a work table. This exceeds the maximum allowable limit of 900.

This usually means you are trying to run a complex query with a row width that the optimizer can't handle (typically due ot use of wide CHAR or VARCHAR columns). In SQL Server 7.0 and up, you can solve this issue by adding OPTION ROBUST PLAN to your query.

Of course, this could lead to the following error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes. Resubmit your query without the ROBUST PLAN hint.

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Invalid column name 'value'with_apostrophe'.

This is usually caused by using " instead of ' to delimit string values, often in an attempt to avoid having to replace ' with ''. However, " are not string delimiters by default in SQL Server, they are identifiers. This means that strings inside of " within a SQL expression are expected to contain column names. So, instead of:

Code:

SQL = "UPDATE table SET column = """ & request.form("value") & """"

use:

Code:

SQL = "UPDATE table SET column = '" & _ 
    replace(request.form("value"),"'","''") & "'"

Furthermore, two other comments: First, the act of doubling up the ' character is not only to prevent parsing errors, but also to avoid exposure to your system to attempts at SQL injection. Second, if you override the quoted identifiers so that " can be interpreted as a string delimiter, instead of worrying about ', now you have to worry about " in the value...

Microsoft OLE DB Provider for SQL Server error '80040e14'
Invalid column name 'valid_column_name'.

This is probably because you used double-quotes around a value, e.g.

Code:

SELECT columns FROM table WHERE column="foo"

Use single-quotes:

Code:

SELECT columns FROM table WHERE column='foo'

Microsoft OLE DB Provider for SQL Server error '80040e14'
Cannot use empty object or column names. Use a single space if necessary.

This usually comes from code that looks like this:

Code:

SELECT * FROM table WHERE column=""

To immediately alleviate the problem, change " to ':

Code:

SELECT * FROM table WHERE column=''

Microsoft OLE DB Provider for SQL Server error '80040e14'
Could not find stored procedure '<owner>.<procname>'.

Typically, this means that the stored procedure does not exist in the current database, or it is owned by a different owner than you are specifying in your EXEC call. (If you are not specifying an owner, than it is not owned by the user you are impersonating.)

Microsoft OLE DB Provider for SQL Server error '80040e14'
Formal parameter '@param_name' was defined as OUTPUT but the actual parameter not declared OUTPUT.

This can happen if you are trying to retrieve an output parameter but the stored procedure definition doesn't explicitly declare the parameter as such. For example:

Code:

CREATE PROCEDURE dbo.myProc 
    @param_name INT 
AS 
...

Should be:

Code:

CREATE PROCEDURE dbo.myProc 
    @param_name INT OUTPUT 
AS 
...

Microsoft OLE DB Provider for SQL Server error '80040e14'
Divide by zero error encountered.

This error is also self-explanatory. Your query includes a division operation, and the denominator is zero (whether it's directly from table data, or calculated within the query, or derived from somewhere else (e.g. a COUNT or SUM from a subquery)). You will need to deal with this in some way; one of the more common ways is using case.

Code:

DECLARE @i INT 
SET @i = 0 
-- ... 
SELECT 5 / @i -- raises error 
GO 
 
DECLARE @i INT 
SET @i = 0 
-- ... 
SELECT 5 / CASE @i WHEN 0 THEN 1 ELSE @i END

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.

This can happen if you use an adOpenStatic ADODB.Recordset object to open Excel, or use an adOpenKeyset recordset to perform paging.

Make sure the methods/properties you are using are supported by the driver/provider you are using to access your data.

If executing a stored procedure, ensure you do so through the connection.execute() method, rather than a command object, and add the following two lines of code to the beginning of your procedure:

Code:

SET ANSI_NULLS OFF 
SET NOCOUNT ON

Microsoft OLE DB Provider for SQL Server error '80040e21'
Optional feature not implemented.

or

ADOBD Parameter error '800a0d5d'
The application is using a value of the wrong type for
the current operation.

This can often be caused by using invalid ad* constants with ADODB.Command, such as adDBDate. Use adDBTimeStamp instead (see KB #214459) or use a straight EXEC statement instead of using the command object at all.

Microsoft JET Database Engine error '80040e21'
Cannot include Memo, OLE, or Hyperlink Object when you select unique values (<column(s)>).

Access does not allow these 'special' columns to be included in DISTINCT queries. So if you have the following code, where bar is a memo column:

Code:

SELECT DISTINCT foo, bar FROM table

You could change it to the following to alleviate the error:

Code:

SELECT foo, bar FROM table GROUP BY foo, bar

Microsoft OLE DB Provider for SQL Server error '80040e21'
Invalid character value for cast specification.

This can happen if you try and pass a NULL string or a non-string datatype to an ADODB.Recordset object or a stored procedure, and the database logic attempts to perform an implict or explicit CONVERT or CAST.

Microsoft JET Database Engine error '80040e21'
You tried to execute a query that does not include the specified expression
'<column>' as part of an aggregate function.

If you are using an aggregate function (e.g. SUM, COUNT, MAX), then any other column in the SELECT list must also be in the GROUP BY clause. This is so that the database knows how to organize results.