Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Error 540 Schema for table '%.*s' has changed since compilation of this query.

4 posts in General Discussion Last posting was on 2011-07-25 18:18:18.0Z
Sybase Beginner Posted on 2011-07-24 13:52:27.0Z
Sender: 5985.4e2c216b.846930886@sybase.com
From: Sybase Beginner
Newsgroups: sybase.public.ase.general
Subject: Error 540 Schema for table '%.*s' has changed since compilation of this query.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e2c239a.5a12.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jul 2011 06:52:27 -0700
X-Trace: forums-1-dub 1311515547 10.22.241.41 (24 Jul 2011 06:52:27 -0700)
X-Original-Trace: 24 Jul 2011 06:52:27 -0700, 10.22.241.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30368
Article PK: 72548

I have encountered a problem when running a SQL where A is a
VIEW and B is a table.

SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND A.UID =
B.UID AND A.REFERENCE = B.REFERENCE AND A.STATUS = B.STATUS
AND .....

The SQL has many AND clause and once when i add one more AND
clause in the SQL. It returns the error "Schema for table
'C' has changed since compilation of this query. Please
re-execute query." The table C is a table which is using in
the VIEW A. When I re-execute the query, the error happens
again. When i remove one of the AND clause such as "AND
A.REFERENCE = B.REFERENCE". The SQL can run without problem.
Please help. Many Thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-07-24 14:12:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Error 540 Schema for table '%.*s' has changed since compilation of this query.
References: <4e2c239a.5a12.1681692777@sybase.com>
In-Reply-To: <4e2c239a.5a12.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e2c2861$1@forums-1-dub>
Date: 24 Jul 2011 07:12:49 -0700
X-Trace: forums-1-dub 1311516769 10.22.241.152 (24 Jul 2011 07:12:49 -0700)
X-Original-Trace: 24 Jul 2011 07:12:49 -0700, vip152.sybase.com
Lines: 33
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30369
Article PK: 72547

Was table C altered/modified recently and if so, what was the modification?

If table C was altered/modified, did you a) check that the view definition is still valid and b) drop/recreate the view?

Without more details I'm assuming table C was altered/modified and this operation may have invalidated the definition
for (at least) one of the view's columns. You then get the error when your query attempts to access the view's
questionable column(s).

If you can't figure it out I'd suggest you post the following:

- view definition (sp_help <view_name>)
- table C definition (sp_help <table_C>)
- description of recent changes to table C
- the problematic AND clause that generates the error
- the full/complete text of the 540 errror
- your dataserver version (select @@version)

On 07/24/2011 09:52, Sybase Beginner wrote:
> I have encountered a problem when running a SQL where A is a
> VIEW and B is a table.
>
> SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND A.UID =
> B.UID AND A.REFERENCE = B.REFERENCE AND A.STATUS = B.STATUS
> AND .....
>
> The SQL has many AND clause and once when i add one more AND
> clause in the SQL. It returns the error "Schema for table
> 'C' has changed since compilation of this query. Please
> re-execute query." The table C is a table which is using in
> the VIEW A. When I re-execute the query, the error happens
> again. When i remove one of the AND clause such as "AND
> A.REFERENCE = B.REFERENCE". The SQL can run without problem.
> Please help. Many Thanks.


Sybase Beginner Posted on 2011-07-24 15:03:11.0Z
Sender: 5b69.4e2c26df.1804289383@sybase.com
From: Sybase Beginner
Newsgroups: sybase.public.ase.general
Subject: Re: Error 540 Schema for table '%.*s' has changed since compilation of this query.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e2c342e.5fd7.1681692777@sybase.com>
References: <4e2c2861$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jul 2011 08:03:11 -0700
X-Trace: forums-1-dub 1311519791 10.22.241.41 (24 Jul 2011 08:03:11 -0700)
X-Original-Trace: 24 Jul 2011 08:03:11 -0700, 10.22.241.41
Lines: 98
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30370
Article PK: 72549

Hi Mark,

The version is
Adaptive Server Enterprise/15.0.2/EBF 15685
ESD#5/P/ia64/HP-UX B.11.23/ase1502/2528/64-bit/FBO

Table C didn't have any altered/modified recently. But Table
B has create an index and delete the index because the index
didn't make performance improvement.

And sorry for my misleading that the error message actually
comes up in the group.

SELECT ID
FROM A INNER JOIN
B ON A.ID = B.ID
AND A.UID = B.UID
AND A.REFERENCE = B.REFERENCE
AND A.STATUS = B.STATUS
AND .....(many and clauses)
group by A.ID,
A.UID,
A.REFERENCE,
A.STATUS....(total 32 groups)

When i just quote the first 31 groups to run the query, it
is no problem. But if including the number 32 group, the
error message come out.

Msg 540, Level 16, State 1
Schema for database object 'C' has changed since compilation
of this query. Please re-execute query.

Then i re-arrange the group, move the number 32 group upward
such as moving the A.STATUS in front of A.REFERENCE.

SELECT ID
FROM A INNER JOIN
B ON A.ID = B.ID
AND A.UID = B.UID
AND A.REFERENCE = B.REFERENCE
AND A.STATUS = B.STATUS
AND .....(many and clauses)
group by A.ID,
A.UID,
A.STATUS,
A.REFERENCE
....(total 32 groups)

And run the first 31 groups, then the query is still can run
successful. So i think this is not related to the content of
the group but the number of the group inside the query.

Sorry that i can't post the table definition and the content
of the SP as it is restricted. Thank for your help.

> Was table C altered/modified recently and if so, what was
> the modification?
>
> If table C was altered/modified, did you a) check that the
> view definition is still valid and b) drop/recreate the
> view?
>
> Without more details I'm assuming table C was
> altered/modified and this operation may have invalidated
> the definition for (at least) one of the view's columns.
> You then get the error when your query attempts to access
> the view's questionable column(s).
>
> If you can't figure it out I'd suggest you post the
> following:
>
> - view definition (sp_help <view_name>)
> - table C definition (sp_help <table_C>)
> - description of recent changes to table C
> - the problematic AND clause that generates the error
> - the full/complete text of the 540 errror
> - your dataserver version (select @@version)
>
> On 07/24/2011 09:52, Sybase Beginner wrote:
> > I have encountered a problem when running a SQL where A
> > is a VIEW and B is a table.
> >
> > SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND A.UID =
> > B.UID AND A.REFERENCE = B.REFERENCE AND A.STATUS =
> > B.STATUS AND .....
> >
> > The SQL has many AND clause and once when i add one more
> > AND clause in the SQL. It returns the error "Schema for
> > table 'C' has changed since compilation of this query.
> > Please re-execute query." The table C is a table which
> > is using in the VIEW A. When I re-execute the query, the
> > error happens again. When i remove one of the AND clause
> > such as "AND A.REFERENCE = B.REFERENCE". The SQL can run
> > without problem. Please help. Many Thanks.


"Mark A. Parsons" <iron_horse Posted on 2011-07-25 18:18:18.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Error 540 Schema for table '%.*s' has changed since compilation of this query.
References: <4e2c2861$1@forums-1-dub> <4e2c342e.5fd7.1681692777@sybase.com>
In-Reply-To: <4e2c342e.5fd7.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e2db36a$1@forums-1-dub>
Date: 25 Jul 2011 11:18:18 -0700
X-Trace: forums-1-dub 1311617898 10.22.241.152 (25 Jul 2011 11:18:18 -0700)
X-Original-Trace: 25 Jul 2011 11:18:18 -0700, vip152.sybase.com
Lines: 120
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30374
Article PK: 72553

So all of your queries fail if you have 32 items in the GROUP BY clause?

And all of your queries succeed if you have 31 items in the GROUP BY clause?

There's a solved case at sybase.com which is not-quite-but-kinda-similar to yours (group by generates 540) ...

http://search.sybase.com/kbx/solvedcases?id_number=11459667

... it's also ASE 15.0.2 ESD #5; you may want to try that case's resolution to see if it helps any.

NOTE: This case says its problem is fixed in 15.0.2 ESD #6, as well as 15.0.3 - so you may want to see if an upgrade
fixes the problem. Personally, I'd want to get off 15.0.2 as soon as possible; while the later versions of 15.0.2
weren't too bad (they were at the quality level we should've had when ASE 15.0 first came out), 15.0.3 seems to be a bit
more stable/reliable than 15.0.2.

I don't think there's a limit to the number of columns in the GROUP BY as long as it doesn't exceed some largish number
of bytes.

Since you're not able to post full details I'd suggest you open a case with Sybase tech support if you continue to have
problems.

On 07/24/2011 11:03, Sybase Beginner wrote:
> Hi Mark,
>
> The version is
> Adaptive Server Enterprise/15.0.2/EBF 15685
> ESD#5/P/ia64/HP-UX B.11.23/ase1502/2528/64-bit/FBO
>
> Table C didn't have any altered/modified recently. But Table
> B has create an index and delete the index because the index
> didn't make performance improvement.
>
> And sorry for my misleading that the error message actually
> comes up in the group.
>
> SELECT ID
> FROM A INNER JOIN
> B ON A.ID = B.ID
> AND A.UID = B.UID
> AND A.REFERENCE = B.REFERENCE
> AND A.STATUS = B.STATUS
> AND .....(many and clauses)
> group by A.ID,
> A.UID,
> A.REFERENCE,
> A.STATUS....(total 32 groups)
>
> When i just quote the first 31 groups to run the query, it
> is no problem. But if including the number 32 group, the
> error message come out.
>
> Msg 540, Level 16, State 1
> Schema for database object 'C' has changed since compilation
> of this query. Please re-execute query.
>
> Then i re-arrange the group, move the number 32 group upward
> such as moving the A.STATUS in front of A.REFERENCE.
>
> SELECT ID
> FROM A INNER JOIN
> B ON A.ID = B.ID
> AND A.UID = B.UID
> AND A.REFERENCE = B.REFERENCE
> AND A.STATUS = B.STATUS
> AND .....(many and clauses)
> group by A.ID,
> A.UID,
> A.STATUS,
> A.REFERENCE
> ....(total 32 groups)
>
> And run the first 31 groups, then the query is still can run
> successful. So i think this is not related to the content of
> the group but the number of the group inside the query.
>
> Sorry that i can't post the table definition and the content
> of the SP as it is restricted. Thank for your help.
>
>
>
>
>> Was table C altered/modified recently and if so, what was
>> the modification?
>>
>> If table C was altered/modified, did you a) check that the
>> view definition is still valid and b) drop/recreate the
>> view?
>>
>> Without more details I'm assuming table C was
>> altered/modified and this operation may have invalidated
>> the definition for (at least) one of the view's columns.
>> You then get the error when your query attempts to access
>> the view's questionable column(s).
>>
>> If you can't figure it out I'd suggest you post the
>> following:
>>
>> - view definition (sp_help<view_name>)
>> - table C definition (sp_help<table_C>)
>> - description of recent changes to table C
>> - the problematic AND clause that generates the error
>> - the full/complete text of the 540 errror
>> - your dataserver version (select @@version)
>>
>> On 07/24/2011 09:52, Sybase Beginner wrote:
>>> I have encountered a problem when running a SQL where A
>>> is a VIEW and B is a table.
>>>
>>> SELECT * FROM A INNER JOIN B ON A.ID = B.ID AND A.UID =
>>> B.UID AND A.REFERENCE = B.REFERENCE AND A.STATUS =
>>> B.STATUS AND .....
>>>
>>> The SQL has many AND clause and once when i add one more
>>> AND clause in the SQL. It returns the error "Schema for
>>> table 'C' has changed since compilation of this query.
>>> Please re-execute query." The table C is a table which
>>> is using in the VIEW A. When I re-execute the query, the
>>> error happens again. When i remove one of the AND clause
>>> such as "AND A.REFERENCE = B.REFERENCE". The SQL can run
>>> without problem. Please help. Many Thanks.