This section contains error messages for the Adaptive Server query processor.
16
Query contains an illegal outer-join request.
Error 301 is raised in the following scenario:
the select clause of a query contains one or more correlated subqueries, and
the subquery contains an outer join clause, and
a correlated column is an outer join operand.
For example:
select t2.b1, (select t2.b2 from t1 where t2.b1 *= t1.a1) from t2
Column t2.b1 is an outer join operand. This violates Transact-SQL query semantics, since correlated variables are not allowed to participate in an outer join.
Some ASE versions report Error 11013 ("Correlated
columns are not allowed in the outer join clause of the subquery.")
or Error 11055 ("Query contains an illegal outer-join
request.") instead.
Rewrite the query to use an ANSI outer join:
select t2.b1, (select t2.b2 from t2 left outer join t1 on t2.b1 = t1.a1) from t2
As an alternative, you can replace the outer join with an equijoin; that is, replace ’*=’ with ’=’.
All versions
16
The optimizer could not find a unique index which it could use to scan table '%.*s' for cursor '%.*s'.
For an explicit updatable cursor scan, Adaptive Server requires that a unique index exist on the table. A unique index ensures that the cursor will be positioned at the correct row the next time a fetch is performed on that cursor. Error 311 occurs when a unique index does not exist during a scan required for a cursor marked for update.
Create a unique index using one of the following methods:
Drop your existing index. Create an index with the unique option or use alter table add constraint with the unique option.
Create a new index using create index with the unique option or use alter table add constraint with the unique option.
When you create an index with the unique option,
if there is a duplicate key value or if more than one row contains
a null value, the command is aborted and Error 1505 is raised, showing
the duplicate value. Refer to Error 1505 if that happens.
Add an IDENTITY column using alter table and the identity option.
If your table has one or more IDENTITY columns, you can, for future indexes you create for this table, use the database option identity in nonunique index. When this option is set, any nonunique index created on a table with an IDENTITY column will have the IDENTITY column automatically included as the last key field of the index. This allows an otherwise nonunique index to be used for a cursor marked for update.
However, all indexes would then be considered unique by the optimizer since every index that is created would be unique and this could result in poor performance for some queries. Since the identity in nonunique index option does not affect existing indexes, only future indexes you create, the existing 311 error will not be solved.
WARNING! Do not use the identity in nonunique index option unless you plan to test your queries.
To use the identity in nonunique index option, the table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.
The commands to set this option are:
1> use master 2> go 1> sp_dboption database_name, 2> "identity in nonunique index", true 3> go
1> use database_name 2> go 1> checkpoint 2> go
Refer to the ASE Reference Manual for information about create index, alter table, and sp_dboption.
All versions
16
A dirty read scan requires a unique index (unless the user forced a non-unique index or table scan). The optimizer could not find a unique index to use.
Isolation level 0 allows transactions to read uncommitted data (dirty reads). Since level 0 scans do not acquire locks, modifications by other processes can cause rows being scanned to move. When this occurs, Adaptive Server restarts its scan. To restart properly, the scan must use the same key used to find the modified row, and start again from the next key in the index. Thus, the scan must be on a unique index. Error 313 occurs when a unique index does not exist during a dirty read scan.
Create a unique index using one of the following methods:
Drop your existing index. Create an index with the unique option or use alter table add constraint with the unique option.
Create a new index using create index with the unique option or use alter table add constraint with the unique option.
When you create an index with the unique option,
if there is a duplicate key value or if more than one row contains
a null value, the command is aborted and Error 1505 is raised, showing
the duplicate value. Refer to Error 1505 if that happens.
Add an IDENTITY column using alter table and the identity option.
If your table has one or more IDENTITY columns, you can, for future indexes you create for this table, use the database option identity in nonunique index. When this option is set, any nonunique index created on a table with an IDENTITY column will automatically have the IDENTITY column included as the last key field of the index. This allows an otherwise nonunique index to be used for a cursor declared for update.
However, all indexes would then be considered unique by the optimizer since every index that is created would be unique and this could result in poor performance for some queries. Since the identity in nonunique index option does not affect existing indexes, only future indexes you create, the existing 313 error will not be solved.
WARNING! Do not use the identity in nonunique index option unless you plan to test your queries.
To use the identity in nonunique index option, the table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.
The commands to set this option are:
1> use master 2> go 1> sp_dboption database_name, 2> "identity in nonunique index", true 3> go 1> use database_name 2> go 1> checkpoint 2> go
forceindex with a clustered nonunique index uses key values to restart the scan at the first row that has key values. You can use forceindex to force Adaptive Server to use a nonunique index for your table scan. However, the restart will either be approximate or it will fail altogether if a row becomes invalidated. If it fails, your query will abort.
WARNING! It is recommended that you do not use forceindex with dirty reads.
Refer to the ASE Reference Manual for information about create index, alter table, and sp_dboption.
All versions
16
WARNING: A non-unique clustered index has been forced on an isolation level 0 scan on table '%.*s'. If the scan must restart, the scan will be repositioned at the beginning of the duplicate key group. Thus, it is possible that this can infinitely loop.
Isolation level 0 allows transactions to read uncommitted data (dirty reads). Since level 0 scans do not acquire locks, modifications by other processes can cause rows being scanned to move. When this occurs, Adaptive Server restarts its scan. To properly restart, the scan must use the same key used to find the modified row and start again from the next key in the index. Thus, the scan must be on a unique index.
Error 314 occurs when a unique index does not exist during a dirty read scan and you have used forceindex to force Adaptive Server to use a nonunique clustered index for your table scan. In this case, Adaptive Server will use key values to restart the scan at the first row that has key values. However, the restart will either be approximate or will fail altogether if a row becomes invalidated. If it fails, your query will abort. In addition, it is possible that the scan can go into an infinite loop.
This is a warning message. No action is required. However, since the consequences are severe, consider using the following options instead of forceindex.
Create a unique index using one of the following methods:
Drop your existing index. Create an index with the unique option or use alter table add constraint with the unique option.
Create a new index using create index with the unique option or use alter table add constraint with the unique option.
When you create an index with the unique option,
if there is a duplicate key value or if more than one row contains
a null value, the command is aborted and Error 1505 is raised, showing
the duplicate value. Refer to Error 1505 if that happens.
Add an IDENTITY column using alter table and the identity option.
If your table has one or more IDENTITY columns, you can, for future indexes you create for this table, use the database option identity in nonunique index. When this option is set, any nonunique index created on a table with an IDENTITY column will have the IDENTITY column automatically included as the last key field of the index. This allows an otherwise nonunique index to be used for a cursor declared for update.
However, all indexes would then be considered unique by the optimizer since every index that is created would be unique and this could result in poor performance for some queries. The identity in nonunique index option does not affect existing indexes, only future indexes you create.
WARNING! Do not use the identity in nonunique index option unless you plan to test your queries.
To use the identity in nonunique index option, the table must already have an IDENTITY column, either from a create table statement or by setting the auto identity database option to true before creating the table.
The commands to set this option are:
1> use master 2> go 1> sp_dboption database_name, 2> "identity in nonunique index", true 3> go 1> use database_name 2> go 1> checkpoint 2> go
Refer to the ASE Reference Manual for information about create index, alter table, and sp_dboption.
All versions
16
Invalid operator for datatype op: %s type: %s.
This error occurs during Adaptive Server expression processing, when an operator in the query is applied to a datatype for which that operator is not valid. An operator is a logical or arithmetic expression such as “+” or “-”.
For example:
1> select pub_id * pub_name from publishers 2> go
Msg 403, Level 16, State 1: Line 1: Invalid operator for datatype op: MULTIPLY type: CHAR.
Correct your query and run it again.
Refer to the Reference Manual and the Transact-SQL User's Guide for information about the use of operators in queries.
All versions
19
Too many ANDs or ORs in expression (limit %d per expression level). Try splitting query or limiting ANDs and ORs.
This error occurs when you use more than 251 and or or expressions in a query.
This error also occurs if you exceed the limit of 251 values in a where in clause. For example:
1> select * from old_table 2> where old_column in (1,2,3...,253,254,...) 3> go
Write the query so that the limit is not exceeded. Often, this requires that you split the query.
Refer to “Search Conditions” in the Transact-SQL User's Guide for more information.
All versions
16
The current query would generate a key size of %d for a work table. This exceeds the maximum allowable limit of %d.
When you execute a query, Adaptive Server may need to create work tables to temporarily store query results. For example, a work table is used when duplicate rows must be removed in processing a query with an aggregate function. A sysindexes row is built for the work table after checking that the specified command does not violate any limitations on keys for user tables.
Error 414 is raised when you execute a query containing an aggregate function, and the total length of columns named in the group by clause of the query exceeds the maximum limit of 600 bytes.
Check the command for possible violations of the 600 byte size limit. You can correct the problem by doing one of the following:
Reduce the columns named in the group by clause until the combined column length is no more than 600 bytes.
If it is necessary to group by a large character column, consider using the substring string function on the column in the group by clause. This allows Adaptive Server to build a composite key to group the result set using only a portion of the character string. For example:
1> select * from titles 2> group by title, substring(notes,1,10)
This query uses only the first 10 bytes of notes (a 200 byte varchar column) to group the data.
All versions
20
There are more than %d referential constraints on table %.*s. Please reduce the number of referential constraints before trying this query.
During query processing, Adaptive Server checks for the existence of foreign keys and dependent foreign keys (a foreign key is a column or combination of columns whose values match the primary key). A range table entry is created for each reference check and foreign key constraint. “%d” in the error message is the maximum number of table references allowed. Error 428 is raised when this limit is reached.
When setting up constraints on your tables, determine the maximum number of tables that might be touched by an update, insert, or delete statement.
To determine which constraints exist for a table, type:
1> use database_name 2> go 1> sp_helpconstraint table_name 2> go
If necessary, drop some of the constraints on the table:
1> alter table table_name 2> drop constraint constraint_name 3> go
Refer to the ASE Reference Manual for information about constraints.
All versions
16
Attempt to update or insert row failed because resultant row of size %d bytes is larger than the maximum size (%d bytes) allowed for this table.
Updated or inserted row is bigger than maximum size (%d bytes) allowed for this table.
This error occurs when you try to insert or update a row that is longer than the maximum allowable length. On a server with 2K page size, rows consist of 1962 characters; allow 2 characters of row overhead for APL tables, for a maximum usable row size of 1960. For DOL tables, subtract two characters per varchar column in determining usable row size.
Error 511 is caused by database design errors (for example, a table designed with the potential for rows containing more than the maximum allowable characters).
The following warning is given when you create a table that has the potential for rows exceeding the maximum row size (that is, the maximum length of all columns added up is greater than the allowable number of characters):
Msg 1708, Level 16, State 1:Warning: Row size could exceed row size limit, which is %d bytes.
If Error 511 is being caused by a table containing rows with more than the maximum row size, divide the table into two or more tables so that no row length is more than the allowable number of characters.
If the 511 error occurring on your database does not appear to be caused by the above situation, call Sybase Technical Support.
All versions
16
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
When an expression subquery returns more than one result, it violates the relational operator rule for the outer query, and Error 512 occurs.
An example of an expression subquery that returns one result follows:
1> select * from table_one where x = 2> (select sum(a) from table_two 3> where b = table_one.y) 4> go
An example of a query that returns more than one result and causes Error 512 follows:
1> use pubs2 2> go 1> select authors.au_id from authors where 2> authors.au_id = (select titleauthor.au_id 3> from titleauthor) 4> go
Msg 512, Level 16, State 1: Line 1: Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
To correct the problem in the example, use “in” in place of “=”, as in the following example:
1> select authors.au_id from authors where 2> authors.au_id in (select titleauthor.au_id 3> from titleauthor) 4> go
au_id ----------- 172-32-1176 213-46-8915 . . 899-46-2035 998-72-3567 (19 rows affected)
All versions
16
Attempt to insert NULL value into column '%.*s', table '%.*s'; column does not allow nulls. Update fails.%S_EED
When you create a table, you can explicitly define whether each column should allow null values. If you do not specify NULL or NOT NULL for a column when you create the table, the default value will be NOT NULL. If you use sp_dboption to set allow nulls by default to TRUE for the database, any new table that is created will have columns with the default value NULL.
Error 515 occurs at run time when a column has a NOT NULL default and you try to insert a NULL value into that column (for example, if a user does not enter a value for that column). The error message includes:
The name of the affected column.
The name of the affected table.
The EED (extended error data array) includes the database name, owner, table name, and column name. This information is not displayed but you will see it in the message definition if you select Error 515 from sysmessages.
The following sample would cause a 515 error:
1> create table table1 (column1 varchar (5)) 2> go 1> declare @c varchar(5) 2> insert into table1 values (@c) 3> go
Msg 515, Level 16, State 3: Server 'SERVER_NAME', Line 2: Attempt to insert NULL value into column 'column1', table 'test.dbo.table1'; column does not allow nulls. Update fails.
To determine whether a column has NULL or NOT NULL defined, enter commands like the following, replacing “pubs2” by the database in which your table resides and “titleauthor” by the table in which your column resides:
1> use pubs2 2> go 1> sp_help titleauthor 2> go
Name Owner Type ------------------------------------------------------------ titleauthor dbo user table Data_located_on_segment When_created ------------------------------ ----------------------------- default Oct 27 1994 10:09AM Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity ------------------------------------------------------------------------ au_id id 11 NULL NULL 0 NULL NULL 0 title_id tid 6 NULL NULL 0 NULL NULL 0 au_ord tinyint 1 NULL NULL 1 NULL NULL 0 royaltyper int 4 NULL NULL 1 NULL NULL 0
The Nulls column indicates whether null values are allowed. A value of 0 for the column means nulls are not allowed and 1 means null values are allowed.
To change the default for a column from NOT NULL to NULL:
Use bcp to copy the data out of the existing table.
If you want the modified table to have the same name as the existing table, drop the old table.
Re-create the original table, specifying NULL for the column you want to change.
Use bcp to put back the data for the table.
If you want new tables in the database to have the default NULL, use the following commands for the database:
1> use master 2> go 1> sp_dboption database_name, 2> "allow nulls by default", true 3> go 1> use database_name 2> go 1> checkpoint 2> go
where database_name is the name of the database whose behavior you want to change.
All versions
16
Attempt to insert NULL value into column %d in work table (table id %ld); column does not allow NULLS. UPDATE fails.
During the run-time phase of updates, Adaptive Server sometimes uses worktables to temporarily store query results. Later in the query processing, Adaptive Server selects the values from those tables.
If, as a result of your query, Adaptive Server tries to insert a null value into a column of a worktable, and nulls are not allowed for the column, Error 530 occurs. The command is aborted and the update fails. Error 530 is caused by an Adaptive Server problem.
Depending on the context of the error, you may be able to rewrite the query (for example, supply a column name or specify isnull). If the error reoccurs, or you cannot write the query in another way, call Sybase Technical Support.
All versions
20
Unexpected internal access methods error %d, state %d. Please report to Technical Support the following information: dbid=%d, objectid=%ld, curcmd=%d (%s), plasterror=%ld, pstat=0x%x, p2stat=0x%x, xactid=(%ld, %d).
Error 539 is raised during query processing when Adaptive Server attempts to execute an access method, but the attempt fails with an unknown failure in the access method. This is why Error 539 reports error code 0.
Error 539 is due to an Adaptive Server problem.
If additional errors accompanied the 539 error, resolve them using the relevant writeups in this document. If the problem persists, call Sybase Technical Support.
Have the following information ready when you call Sybase Technical Support:
Server version and SWR rollup level
Server error log
Text of all error messages.
All versions
16
Schema for table '%.*s' has changed since compilation of this query. Please re-execute query.
A table’s schema consists of column definitions along with any indices, constraints, rules, defaults and so on. A change to any of these elements (for example, creating or dropping a constraint) constitutes a change in the table’s schema.
Query processing involves the following basic steps:
Adaptive Server examines the SQL statement and, if it passes syntax checks, the query is parsed to produce an internal representation called the query tree.
The server then determines the optimal access strategy to implement the query and compiles the query tree to produce the query plan. For ad hoc queries the server discards the query tree. For stored procedures, the query tree is saved on disk so that the plan can be regenerated as necessary.
The query plan is executed to produce the results.
Error 540 is raised:
When the server attempts to execute the query plan of an ad hoc query, but discovers that the table’s schema changed after the query tree was produced and before the plan has been executed. Adaptive Server cannot recompile an ad hoc query because its tree has not been preserved. On the other hand, a stored procedure will always be recompiled after the server detects a schema change. Note that schema changes to a table include updating the table statistics.
If you create a table with a primary key constraint on a column, followed by an insert into that table in the same batch. For example:
1> create table table1 (id int primary key, f2 int ) 2> insert into table1 (id, f2) values (1,1)
Msg 540, Level 16, State 1: Server ’tsg125’, Line 2: Schema for table ’t1’ has changed since compilation of this query. Please re-execute query.
In this scenario, Error 540 is due to an Adaptive Server problem.
Error 540 is not a serious error; however, it may prevent you from running some ad hoc queries.
If the error occurred when creating and inserting to a table with a primary key constraint in the same batch, contact Sybase Technical Support. They can help you upgrade to a version in which the problem is resolved.
Otherwise, if the error occurred in a different scenario:
Retry the query. This ensures that Adaptive Server can build the appropriate query plan for the revised schema.
Avoid taking actions which result in schema changes to tables (for example, running update statistics) while queries referencing those tables are executing.
All versions
16
Foreign key constraint violation occurred, dbname = ’%.*s’, table name = ’%.*s’, constraint name = ’%.*s’.%S_EED
Foreign key constraints are a form of integrity constraint which ensure that no insert or update on a foreign key table is performed without a matching value in the primary key table. Error 546 is raised when an integrity constraint is violated during execution of a query. For example, error 546 can be raised when Adaptive Server detects that the data inserted into a foreign key does not match any primary key value in the table referenced by the foreign key.
For example:
1> create table departments 2> (d_id int primary key, d_name varchar(30)) 3> create table employees 4> (emp_id int, empname varchar(100), d_id int references departments) 5> go
1> insert departments values (1, ’sales’) 2> go
(1 row affected)
1> insert employees values (1, ’Fred Smith’, 1) 2> go
(1 row affected)
1> update employees set d_id = 2 where emp_id=1 2> go
Foreign key constraint violation occurred, dbname = ’master’, table name = ’employees’, constraint name = ’employees_d_id_1824006498’. Command has been aborted. (0 rows affected)
In this example, Error 546 is raised because the update command attempted to use a non-existent department.
Check your query or application to determine the source of the constraint violation. You can examine the constraint by executing:
1> sp_helpconstraint table_name 2> go
where table_name is the table appearing in the 546 message.
All versions
16
Dependent foreign key constraint violation in a referential integrity constraint. dbname = '%.*s', table name = '%.*s', constraint name = '%.*s'.%S_EED
Adaptive Server provides integrity constraints to help you maintain logical data integrity in a database. Referential integrity (or foreign key) constraints are a type of constraint which require that data being inserted into a given table column already has matching data in another column (the target column), which may be in the same table or another table. The column on which the constraint is declared can be considered the child, or dependent, in a parent-child relationship.
Error 547 is raised when updating or deleting rows from a parent table would remove target columns matching dependent data in the child table. For example:
1> create table parent 2> (a int primary key, 3> b int, 4> unique (b)) 5> go 1> create table child 2> (c int primary key, 3> d int references parent(b)) 4> go 1> insert parent values (11,22) 2> insert parent values (13,26) 3> insert child values (101,22) 4> go 1> delete parent where a=13 2> go
(1 row affected)
1> delete parent where a=11 2> go
Msg 547, Level 16, State 1: Line 1: Dependent foreign key constraint violation in a referential integrity constraint. dbname = 'hrdb', table name = 'parent', constraint name = 'detail_d_1088006907'. Command has been aborted. (0 rows affected)
1> update parent 2> set b=29 3> where a=11 4> go
Msg 547, Level 16, State 1: Line 1: Dependent foreign key constraint violation in a referential integrity constraint. dbname = 'hrdb', table name = 'parent', constraint name = 'child_d_1088006907'. Command has been aborted. (0 rows affected)
Notice that you may drop or update rows in parent provided you do not affect the referential integrity constraints.
Delete or update the dependent data in the child table before deleting or changing the parent data.
Use the system procedure sp_helpconstraint to view the referential constraints in effect for a table.
All versions
20
An unknown EVAL was sent to the execution module.
The instructions Adaptive Server creates to run a query are contained in an evaluation list that contains instruction-argument pairs. Error 551 occurs when Adaptive Server receives an illegal instruction. It is probably caused by an incorrectly compiled query tree.
Drop and re-create the procedure or trigger being run:
If you do not have a script for re-creating your procedure or trigger, get the text of the procedure or trigger:
1> use database_name 2> go 1> sp_helptext object_name 2> go
where database_name is the name of the database in which the procedure or trigger resides and object_name is the name of the procedure or trigger.
Alternatively, use the defncopy program to copy the procedure or trigger definition to a file. Refer to the Adaptive Server utility programs manual for details.
Drop the procedure or trigger:
1> drop procedure object_name 2> go
or:
1> drop trigger object_name 2> go
Re-create the procedure or trigger. Refer to the ASE Reference Manual for information about the create procedure and create trigger commands.
If the 551 error occurs again, create a scenario to reproduce the problem and call Sybase Technical Support.
Have the following information ready before calling Technical Support:
Adaptive Server version and SWR level
Copy of the query which raises the error
All versions
20
Explicit value specified for identity field in table '%.*s' when IDENTITY_INSERT is set to OFF.
Each table can include a single IDENTITY column. IDENTITY columns store sequential numbers that are generated automatically by Adaptive Server. The value of the identity column can uniquely identify each row in a table.
The Adaptive Server query processing option set identity_insert determines whether explicit inserts into a table's IDENTITY column are allowed. Inserting a value into the IDENTITY column allows you to specify a “seed” value for the column or to restore a row that was deleted in error. Setting identity_insert on allows the table owner, database owner, or System Administrator to explicitly insert a value into an IDENTITY column. Unless a unique index exists on the IDENTITY column, any positive value without regard to uniqueness may be inserted in IDENTITY columns when identity_insert is set to on.
Setting identity_insert off prohibits inserts to IDENTITY columns.
Error 584 is raised if you attempt to insert an explicit value into an IDENTITY column when identity_insert is set to off.
Set identity_insert on before attempting to insert an explicit value into an IDENTITY column. identity_insert should be reset to off once the insert operation is complete.
The syntax for setting the identity_insert option is as follows:
1> set identity_insert `table_name' {on | off}
2> go
where table_name is the base table for the column. Only the table owner, database owner, or System Administrator can set this option.
All versions
