Getting "Invalid object name" error during Schema Comparison

Forum for users of SQL Toolkit 3,4,and 5

Getting "Invalid object name" error during Schema Comparison

Postby Chadwick00008 » Fri Jul 17, 2009 11:30 pm

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
Chadwick00008
 
Posts: 6
Joined: Wed Jul 18, 2007 5:40 pm

Postby Brian Donahue » Tue Jul 21, 2009 11:00 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Chadwick00008 » Tue Jul 21, 2009 3:34 pm

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
 
Posts: 6
Joined: Wed Jul 18, 2007 5:40 pm

Postby Chadwick00008 » Tue Jul 21, 2009 5:41 pm

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?
Chadwick00008
 
Posts: 6
Joined: Wed Jul 18, 2007 5:40 pm

Postby Brian Donahue » Tue Jul 21, 2009 6:00 pm

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Postby Chadwick00008 » Tue Jul 21, 2009 7:06 pm

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.
Chadwick00008
 
Posts: 6
Joined: Wed Jul 18, 2007 5:40 pm

Postby Brian Donahue » Wed Jul 22, 2009 11:00 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests