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 these 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.
When 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) 3> go
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 occurs 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 to help you upgrade to a version in which the problem is resolved.
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
