Red Gate forums :: View topic - Getting "Invalid object name" error during Schema Comparison
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Getting "Invalid object name" error during Schema Comparison

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Chadwick00008



Joined: 18 Jul 2007
Posts: 6

PostPosted: Fri Jul 17, 2009 11:30 pm    Post subject: Getting "Invalid object name" error during Schema Comparison Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Tue Jul 21, 2009 11:00 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chadwick00008



Joined: 18 Jul 2007
Posts: 6

PostPosted: Tue Jul 21, 2009 3:34 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Chadwick00008



Joined: 18 Jul 2007
Posts: 6

PostPosted: Tue Jul 21, 2009 5:41 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Tue Jul 21, 2009 6:00 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chadwick00008



Joined: 18 Jul 2007
Posts: 6

PostPosted: Tue Jul 21, 2009 7:06 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6580

PostPosted: Wed Jul 22, 2009 11:00 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group