SQL Server Error Fix: “The definition of object ‘stored_procedure’ has changed since it was compiled.”


You might have got below error message while running a stored procedure in your environment.

Msg 2801, Level 16, State 1, Procedure prc_test, Line 15

The definition of object ‘my_proc’ has changed since it was compiled.

I have analyzed this issue and found the root cause for it.:

I found that this issue occurs only when two conditions satisfied : 1) underlying objects(tables/indexes) of running SP are re-indexed and 2) running SP is marked for recompilation.

Following three tests I have done to come on this conclusion:

Test 1)

1. Execute the attached script file. This script file will create a table and nonclustered index on it. It will insert some values. Will create a stored procedure. I have made this SP to run at least 20 seconds.

2. Open a new query window and execute following statement.

use tempdb

GO

exec prc_test

3. Open a new query window and execute following statement.

Use tempdb

GO

exec sp_recompile ‘prc_test’

If you want you can run this statement as many times as you want.

You will see that the SP is executed successfully.

Test 2)

1. Execute the attached script file.

2. Open a new query window and execute following statement.

use tempdb

GO

exec prc_test

3. Open a new query window and execute following statement.

Use tempdb

GO

ALTER INDEX ALL

ON tst

REBUILD

If you want you can run this statement as many times as you want.

You will see that the SP is executed successfully.

Test 3)

1. Execute the attached script file.

2. Open a new query window and execute following statement.

use tempdb

GO

exec prc_test

3. Open a another query window and execute following statement.

Use tempdb

GO

exec sp_recompile ‘prc_test’

4. Open a new query window and execute following statement.

Use tempdb

GO

ALTER INDEX ALL

ON tst

REBUILD

You will get following error:

Msg 2801, Level 16, State 1, Procedure prc_test, Line 15

The definition of object ‘prc_test’ has changed since it was compiled.

Please make sure that you run it on the correct sequence as described above.

Advertisements

2 Responses

  1. Thanks for taking the time to post this. It was key in my understanding the cause of a logon trigger entering that state and denying all connections to the server.

    I confirmed this behavior is the same in the newest production code, SQL 2008 R2 as of this writing.

    I may file a support request to see what MS has to say about this. It seems to my Oracle-centric brain that it should just recompile the darned object instead of manifest this error, breaking whatever it is the code is supposed to do.

  2. re-create the store procedure is fixed the problem in my pc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: