Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
I edited 'data and schema perfect match' to just 'schema perfect match', as the A_Audit tables are different between databases. The second constraint that I mentioned is for a table named C_Comment, and the data between these tables does match. –  JoeB Apr 17 '11 at 18:10

2 Answers 2

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?

SELECT * FROM dbo.A_Audit A
WHERE NOT EXISTS ( SELECT * FROM dbo.T_Type T WHERE A.AuditTypeID = T.TypeID)
share|improve this answer
    
0 rows returned. You're right in that it could certainly be something else than what I've suggested. I've used Redgate's Compare and Data Compare tools to try to spot data that could be causing this to no avail. –  JoeB Apr 17 '11 at 18:06
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.