Getting "Invalid object name" error during Schema Comparison
Chadwick00008
Posts: 8
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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
Comments
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.
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?
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?
You can use this option wherever you can specify Options, for instance in the Work.BuildFromDifferences method.
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.
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.