'' The definition of object [object name] has changed since it was compiled'' error when alt

Hello All

Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.

I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:

There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.

[stored procedure name] and [trigger name] are where the actual names appear in the message.

I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).

Has anyone seen this, and if so, how can I fix it?

Thanks in advance for your help!

Jay

Try the Transact-SQL forum.

|||Is there a specific reason that you didn't move to compatibilty 9.0 after the upgrade. You can check and upgrade.

--check compatibility level
EXEC sp_dbcmptlevel '<DatabaseName>'--change compatibility level to 9.0
EXEC sp_dbcmptlevel '<DatabaseName>', '90'
I don't think, it is directly related to your issue though!
|||

Hi

Unfortunately, the database was upgraded before I joined the company, so I'm unsure as to the reason. I suspect they felt it would be better seeing the original database was on SQL Server 2000. I'll give this a try and see what happens. Thanks for the reply!

J