Yet I get no 'previous errors'. The line it gives me (by double clicking in SSMS) shows the second alter statement as the culprit:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_Audit_AuditTypeID]')
AND parent_object_id = OBJECT_ID(N'[dbo].[A_Audit]'))
ALTER TABLE [dbo].[A_Audit] WITH CHECK ADD CONSTRAINT [FK_A_Audit_AuditTypeID] FOREIGN KEY([AuditTypeID])
REFERENCES [dbo].[T_Type] ([TypeID])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_A_Audit_AuditTypeID]')
AND parent_object_id = OBJECT_ID(N'[dbo].[A_Audit]'))
ALTER TABLE [dbo].[A_Audit] CHECK CONSTRAINT [FK_A_Audit_AuditTypeID]
GO
Now when I try to remove this piece of the script, I get the same exact error on a different alter constraint statement that's pointing to the same table (T_Type).
The same unaltered script works fine on our TEST database, which is a schema perfect match for our production database. This leads me to believe there is some external factor making this script work in test but not in production.
I was able to find two major differences between our production and test machines:
- The production database machine recently had CommVault software installed. On a different database within the same company, this CommVault installation broke other scripts. (Yes, I realize I could provide more information here - I'm working on getting it now)
- Production is SQL 2005 SP4, while test is SQL 2005 SP3 (9.0.5 vs 9.0.4053).
Any help is greatly appreciated.
I have to ask this first before digging into other details. Have you verified that there is no data in the dbo.A_Audit table that violates the foreign key constraint on the dbo.T_Type table? Does this bring up any data in Production?
This was caused by an application running on someone's dev machine that had a linked-server connection to our production database. This application was blocking the alter statement from running (somehow, I didn't ask).
Thanks for looking.