Why do I get 80040E14 errors?

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 pretty self-explanatory... if you try to insert/update a column that does not allow NULL values, with NULL (e.g. by hard-coding NULL into the statement, or by leaving a column - that does not have a default value defined - out of the INSERT list). 
 

---

 

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.

 
This will happen if you are using #temp tables and the collation on tempdb does not match that of the database(s) you're working in. The collation of all affected databases should match, or at least be compatible. You can run the following command to compare collation between the databases (look at the Collation= section of the 'Status' value in the first resultset): 
 

EXEC sp_helpdb 'tempdb'  EXEC sp_helpdb '<userDatabaseName>'

 
If you can't change the collation settings for tempdb, you can override them for your #temp tables by creating your definitions like this: 
 

CREATE TABLE #temp  (      FullName VARCHAR(32) COLLATE DATABASE_DEFAULT  )

 

---

 

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

 
Several string functions cannot be performed against TEXT/NTEXT columns. See Article #2061 for more information. 
 

---

 

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

 
If you know the object exists, and that you are in the correct database, this is usually a permissions issue. See Article #2284. 
 

---

 

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>'.

 
You either used a reserved word as a column or alias name (or used a column name that begins with a number or non-alphanumeric character), didn't delimit a value properly, or really have a syntax error. See Article #2086 for more information. If you are trying to use the TOP keyword when you get this error, see Article #2434. You might also get this: 
 

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. Try using the following approach instead of the troublesome ADODB.Command object: 
 

conn.execute("EXEC proc_name @param1='value'")    or    set rs = conn.execute("EXEC proc_name @param1='value'")

 
(This is both easier on the eyes and less prone to errors. If you need an output or return variable, consider changing the stored procedure to send that back as a recordset instead - you can then use the nextRecordsSet() method to process multiple resultsets independently.) 
 
If you still get the same error, response.write the SQL statement and paste it into Query Analyzer. You might get a more meaningful error message from the database. 
 
If these suggestions don't solve the problem, response.write the bad query and post it to microsoft.public.inetserver.asp.db and someone will try to help you figure out what the problem is. 
 

---

 

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 need to investigate your query, and the structure of the table(s) it affects, to determine why you might be violating one of these relationships. See Article #2509 for some ideas on preventing duplicates, or at least preventing this error. 
 

---

 

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 got the syntax wrong). For information about renaming columns, see Article #2505; you can also look at sp_rename in Books Online if you are trying to rename another object. 
 

---

 

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

 
Several functions available within Access are not available through ADO/JET providers - see Article #2394. 
 

---

 

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 (I admit, it could yield a more helpful error message): 
 

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. See Article #2471 for ideas on reclaiming space in SQL Server. 
 

---

 

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. Here is a Books Online excerpt about ROBUST PLAN: 

Forces the query optimizer to attempt a plan that works for the maximum potential row size, possibly at the expense of performance. When the query is processed, intermediate tables and operators may need to store and process rows that are wider than any of the input rows. The rows may be so wide that, in some cases, the particular operator cannot process the row. If this happens, SQL Server produces an error during query execution. By using ROBUST PLAN, you instruct the query optimizer not to consider any query plans that may encounter this problem.

For more information about how to implement OPTION ROBUST PLAN, see the 'SELECT' topic in Books Online. 
 
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.

 
If this is the case, post your schema, sample data, current query and desired output to microsoft.public.sqlserver.programming and you will get some help. 
 

---

 

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 '' (see Article #2035). 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: 
 

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

 
use: 
 

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

 
Further to this, 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. 
 

SELECT columns FROM table WHERE column="foo"

 
Use single-quotes: 
 

SELECT columns FROM table WHERE column='foo'

 
Or, change the QUOTED_IDENTIFIER setting (see below). 
 

---

 

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: 
 

SELECT * FROM table WHERE column=""

 
To immediately alleviate the problem, change " to ': 
 

SELECT * FROM table WHERE column=''

 
If you really want to use double quotes to delimit strings, you'll have to fiddle with the default QUOTED_IDENTIFIER settings. If you enable this setting, as follows: 
 

SET QUOTED_IDENTIFIER ON

 
You will be able to use double quotes to delimit strings. I recommend leaving this setting OFF and using single quotes... this will keep your code cleaner, by only allowing one type of identifier, and will remove any ambiguity... 
 

---

 

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: 
 

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

 
Should be: 
 

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. 
 

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