Why do I get 80040E14 errors?
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).
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):
If you can't change the collation settings for tempdb, you can override them for your #temp tables by creating your definitions like this:
Several string functions cannot be performed against TEXT/NTEXT columns. See Article #2061 for more information.
If you know the object exists, and that you are in the correct database, this is usually a permissions issue. See Article #2284.
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:
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:
(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.
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.
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.
Several functions available within Access are not available through ADO/JET providers - see Article #2394.
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.
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):
In order to prevent this error, you will need to send the two statements separately.
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.
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).
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.
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:
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.
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:
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...
This is probably because you used double-quotes around a value, e.g.
Or, change the QUOTED_IDENTIFIER setting (see below).
This usually comes from code that looks like this:
To immediately alleviate the problem, change " to ':
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:
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...
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.)
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:
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.
Related ArticlesWhy 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 80040E21 errors?
Why do I get 80040E23 errors?
Why do I get 80040E24 errors?
Why do I get 80040E2F errors?
Why do I get 80040e30 errors?
Why do I get 80040E31 errors?
Why do I get 80040E37 errors?
Why do I get 80040e4e errors?
Why do I get 80040E54 errors?
Why do I get 80040E57 / 80040E07 errors?
Why do I get 8004D00A errors?
Why do I get 80070070 errors?
Why do I get 800A01FB errors?
Why do I get 800A0BB9 / 800A1391 errors?
Why do I get 800A0C93 errors?
Why do I get 800a0cb3 errors?
Why do I get 800A0CC1 errors?
Why do I get 800A0E78 errors?
Why do I get 800A0E7C errors?
Why do I get 800A0E7D errors?
Can I use the NZ() function without getting 80040E14 errors?