Getting "Invalid object name" error during Schema Comparison

[Chadwick00008]

Hello,

I've got some code to synch the schemas of two databases. When I run it, I get an exception during the .ExecuteBlock function that comes back with "Invalid Object Name 'MSS.dbo.wallMainCondition'". I've looked through both databases (which I ran through the SchemaCompare tool from RedGate) and the tables exist in both databases.

The two databases are currently both residing on my machine as Im testing out the code for a client. Im not sure where/why it is appending MSS to the front of dbo.wallMainCondition, as the word MSS appears no where in the resulting script. Below is the code fragment and resulting script produced.

Thanks in advance,

Chadwick




****************CODE FRAGMENT**************************
// Now generate a differences object to hold differences between
// the two database schemas.
Differences differences = db1.CompareWith(db2, Options.Default);
foreach (Difference difference in differences)
{
if (difference.DatabaseObjectType == ObjectType.User || difference.DatabaseObjectType == ObjectType.Schema)
{
difference.Selected = false;
}
else
{
difference.Selected = true;
}
}

// Create the SQL execution script to run on the TB if any differences are found
ExecutionBlock blockSchema = null;
try
{
if (differences.Count > 0)
{
Work work = new Work();
work.BuildFromDifferences(differences, Options.Default | Options.IgnoreUsers | Options.IgnoreUserProperties | Options.IgnoreOwners, true);
blockSchema = work.ExecutionBlock;

/// DEBUG:
FileInfo tDiff = new FileInfo("SchemaDifferences.txt");
StreamWriter TexSchema = tDiff.CreateText();
TexSchema.WriteLine(blockSchema.GetString());
TexSchema.Write(TexSchema.NewLine);
TexSchema.Close();
///

BlockExecutor executorSchema = new BlockExecutor();
executorSchema.ExecuteBlock(blockSchema, strServerTB, strDatabaseTB);
}
}
catch (Exception exc)
{
m_bLoggedIn = false;
string strMsg = "Error executing SQL statement block during schema compare: " + exc.Message.ToString();
MessageBox.Show(strMsg, "Error");
}
finally
{
blockSchema.Dispose();
db1.Dispose();
db2.Dispose();
}
*******************END CODE FRAGMENT********************

*******************SQL OUTPUT FILE************************
/*
Script created by SQL Compare version 5.3.0.44 from Red Gate Software Ltd at 7/17/2009 5:25:34 PM
Run this script on TRANS-CHADLAPTO.MSS_TB to make it the same as TRANS-CHADLAPTO.MSS_CENTRAL
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[WallDefects]'
GO
ALTER TABLE [dbo].[WallDefects] ALTER COLUMN [Description1] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
exec sp_refreshview N'[dbo].[CGALL_TABLES]'
exec sp_refreshview N'[dbo].[CGCOLUMN_PRIVILEGES]'
exec sp_refreshview N'[dbo].[CGTABLE_NOPRIVILEGES]'
exec sp_refreshview N'[dbo].[CGTABLE_PRIVILEGES]'
exec sp_refreshview N'[dbo].[mapxqz61606482]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

[Brian Donahue]

Hi Chad,

I can't say where this object name is coming from without knowing your database schema. If it doesn't appear in the actual update script, then I'd assume you have got a DDL trigger somewhere running this code as a result of your attempted schema modification.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Chadwick00008]

Thanks for the prompt reply. : )

If it is a DDL trigger, is there some way I can tell the toolkit to ignore it, or to not append it when running the script?

[Chadwick00008]

I took the SQL script output and tried running it directly in MSSQL 2005. It came back with the error:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'MSS'.

As far as I know, there are no stored procedures in the database. Any thoughts?

[Brian Donahue]

There is an option to disable ddl triggers: DisableAndReenableDdlTriggers
You can use this option wherever you can specify Options, for instance in the Work.BuildFromDifferences method.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[Chadwick00008]

Ok, I tried it with the DisableAndReenableDdlTriggers option and got the same result.

When I run the SQL script in 2005 Im getting this error:

Msg 208, Level 16, State 1, Procedure sp_refreshview, Line 1
Invalid object name 'MSS.dbo.wallMainCondition'.

I can't find the SP sp_refreshview in the database...even in the system SP's.

[Brian Donahue]

Hi,

I think that SQL Compare updates views that are dependent on tables that are being updated as well. Have you got a view named wallMainCondition, and if so, can you try dropping and recreating it? It seems like a compiled version of the view is incorrect or something wierd like that.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521