Disable sp_refreshview

brigzy · September 24, 2009 8:22AM

Hi
I am having trouble as sp_referesh view is included in the script generated by SQLCompare command line.

When running the script on the destination server\database, the script errors as the view refferes to a 'another' database that is not on the destination server.

The generated script is creating an sp_refresh for every view in the database? which is odd as there are only 4 ddl changes (one SP, three table structure changes).

Can I disable the sp_refreshview behaviour?

Attched is generated SQL script

/*
Run this script on:

        Partner-Dev.partner_chall    -  This database will be modified

to synchronize it with:

        Partner-Dev.rdb_partner_chall

You are recommended to back up your database before running this script

Script created by SQL Compare version 8.1.0 from Red Gate Software Ltd at 24/09/2009 13:31:07

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS &#40;SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID&#40;'tempdb..#tmpErrors'&#41;&#41; DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors &#40;Error int&#41;
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Isokon_Tfb_Transactions&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Isokon_Tfb_Transactions&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering &#91;dbo&#93;.&#91;up_Net2Matter_SetRecent_MatterContactAgenda&#93;'
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE &#91;dbo&#93;.&#91;up_Net2Matter_SetRecent_MatterContactAgenda&#93;
	&#40;@EntityRef varChar&#40;15&#41;, @MatterNo int, @ContactRef VarChar&#40;15&#41;, 
	@AgendaID int, @UserID VarChar&#40;20&#41;&#41;
AS
BEGIN
	-- Create results table
	DECLARE @Result TABLE&#40;EntityRef varChar&#40;15&#41;,
						MatterNo int, 
						ContactRef varChar&#40;15&#41;,
						AgendaID int,
						NewOrder int&#41;
	-- Gather results 
	INSERT INTO @Result
	SELECT TOP 30 EntityRef, MatterNo, Contact, AgendaID,
			ROW_NUMBER&#40;&#41; OVER &#40;order by orderinlist&#41; -1 as RowNumber
	FROM recentmatters 
	WHERE usercode = @UserID AND NOT
		 &#40;EntityRef = @EntityRef AND MatterNo =@MatterNo&#41; 
	-- Add our new most recent row, this entity, this matterno, this AgendaID
	UNION SELECT @EntityRef, @MatterNo, @ContactRef, @AgendaID, -1
	ORDER BY RowNumber
	-- Set new orders
	UPDATE @Result SET NewOrder = NewOrder +1

	DELETE recentmatters WHERE UserCode = @UserID

	INSERT INTO recentmatters &#40;UserCode, EntityRef, MatterNo, Contact, AgendaID, OrderInList&#41;
	SELECT @UserID, EntityRef, MatterNo, ContactRef, AgendaID, NewOrder FROM @Result
END
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering &#91;dbo&#93;.&#91;Usr_CreditCheck&#93;'
GO
ALTER TABLE &#91;dbo&#93;.&#91;Usr_CreditCheck&#93; ADD
&#91;ResultsEntityRef&#93; &#91;varchar&#93; &#40;15&#41; COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
&#91;ResultsStepID&#93; &#91;int&#93; NULL
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chart_Of_Accounts_GroupView&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chart_Of_Accounts_GroupView&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;StepPostInDirect&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;StepPostInDirect&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;vw_ClientBills&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;vw_ClientBills&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;vw_ClientBills_ShortSummary&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;vw_ClientBills_ShortSummary&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;ViewTaskList&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;ViewTaskList&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Dm_TimePostings&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Dm_TimePostings&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;FM_fFiles&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;FM_fFiles&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chall_View_DisbsOUT&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chall_View_DisbsOUT&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;F_OrgTypeLnk&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;F_OrgTypeLnk&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;PBC_fWorkTypes&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;PBC_fWorkTypes&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chall_View_DisbsIN_Sept2008&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chall_View_DisbsIN_Sept2008&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chall_View_DisbsOUT_Sept08&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chall_View_DisbsOUT_Sept08&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chall_IncomeVsTargets&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chall_IncomeVsTargets&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;vw_LeverArchLabels&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;vw_LeverArchLabels&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;AllMatters&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;AllMatters&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;MARFEE&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;MARFEE&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Corebridge_Directory&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Corebridge_Directory&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;AcPLAgCrMatterAlloc&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;AcPLAgCrMatterAlloc&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Ac_CashAccountTransactions&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Ac_CashAccountTransactions&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;vw_CaseSteps&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;vw_CaseSteps&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;VIEW_LGFS_DATA&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;VIEW_LGFS_DATA&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;StepTime&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;StepTime&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;StepPostDirect&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;StepPostDirect&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;Chall_View_DisbsIN&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;Chall_View_DisbsIN&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;F_Organisations&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;F_Organisations&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;F_OrganisationsMatter&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;F_OrganisationsMatter&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;PBC_fClients&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;PBC_fClients&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;CDSDataView&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;CDSDataView&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;VIEW_CLAIMSTOAPPROVE&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;VIEW_CLAIMSTOAPPROVE&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing &#91;dbo&#93;.&#91;AcPLAgCrNominalAlloc&#93;'
GO
EXEC sp_refreshview N'&#91;dbo&#93;.&#91;AcPLAgCrNominalAlloc&#93;'
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering &#91;dbo&#93;.&#91;Usr_IDCheck&#93;'
GO
ALTER TABLE &#91;dbo&#93;.&#91;Usr_IDCheck&#93; ADD
&#91;ResultsEntityRef&#93; &#91;varchar&#93; &#40;15&#41; COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
&#91;ResultsStepID&#93; &#91;int&#93; NULL
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
ALTER TABLE &#91;dbo&#93;.&#91;Usr_IDCheck&#93; DROP
COLUMN &#91;PDFStepID&#93;
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering &#91;dbo&#93;.&#91;Usr_Trace_Check&#93;'
GO
ALTER TABLE &#91;dbo&#93;.&#91;Usr_Trace_Check&#93; ADD
&#91;ResultsEntityRef&#93; &#91;varchar&#93; &#40;15&#41; COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
&#91;ResultsStepID&#93; &#91;int&#93; NULL
GO
IF @@ERROR&lt;&gt;0 AND @@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors &#40;Error&#41; SELECT 1 BEGIN TRANSACTION END
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS &#40;SELECT * FROM #tmpErrors&#41; ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT&gt;0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Michelle T · September 24, 2009 9:31AM

You might find that SQL Compare produces less refreshview statements if you create the script through the GUI (or if you need to use the command line, create a project in the GUI, save the project, and use the /pr: switch on the command line to use the project to generate the script).

The extra view refreshes are a known issue (SC-4542) which we'll hopefully get fixed soon.

brigzy · September 24, 2009 9:47AM

Michelle Taylor wrote:

You might find that SQL Compare produces less refreshview statements if you create the script through the GUI (or if you need to use the command line, create a project in the GUI, save the project, and use the /pr: switch on the command line to use the project to generate the script).

The extra view refreshes are a known issue (SC-4542) which we'll hopefully get fixed soon.

Ok thankyou, I am trying the /pr switch

The GUI project finds differences ok.

When using /pr switch however I receive message :
Error: The selected objects are identical or no objects have been selected in the comparison.

brigzy · September 24, 2009 10:13AM

brigzy wrote:

Michelle Taylor wrote:

You might find that SQL Compare produces less refreshview statements if you create the script through the GUI (or if you need to use the command line, create a project in the GUI, save the project, and use the /pr: switch on the command line to use the project to generate the script).

The extra view refreshes are a known issue (SC-4542) which we'll hopefully get fixed soon.

Ok thankyou, I am trying the /pr switch

The GUI project finds differences ok.

When using /pr switch however I receive message :
Error: The selected objects are identical or no objects have been selected in the comparison.

Here is the project contents:

&lt;?xml version="1.0" encoding="utf-8" standalone="yes"?&gt;&lt;!--
SQL Compare
SQL Compare 8
Version:8.1.0.360--&gt;&lt;Project version="1" type="SQLComparisonToolsProject"&gt;&lt;DataSource1 version="2" type="LiveDatabaseSource"&gt;&lt;ServerName&gt;PARTNER-DEV&lt;/ServerName&gt;&lt;DatabaseName&gt;RDB_PARTNER_CHALL&lt;/DatabaseName&gt;&lt;Username /&gt;&lt;SavePassword&gt;False&lt;/SavePassword&gt;&lt;Password /&gt;&lt;IntegratedSecurity&gt;True&lt;/IntegratedSecurity&gt;&lt;/DataSource1&gt;&lt;DataSource2 version="2" type="LiveDatabaseSource"&gt;&lt;ServerName&gt;PARTNER&lt;/ServerName&gt;&lt;DatabaseName&gt;PARTNER_CHALL&lt;/DatabaseName&gt;&lt;Username /&gt;&lt;SavePassword&gt;False&lt;/SavePassword&gt;&lt;Password /&gt;&lt;IntegratedSecurity&gt;True&lt;/IntegratedSecurity&gt;&lt;/DataSource2&gt;&lt;LastCompared&gt;09/24/2009 14:40:33&lt;/LastCompared&gt;&lt;Options&gt;317002946838538&lt;/Options&gt;&lt;InRecycleBin&gt;False&lt;/InRecycleBin&gt;&lt;Direction&gt;0&lt;/Direction&gt;&lt;ProjectFilter version="1" type="DifferenceFilter"&gt;&lt;FilterCaseSensitive&gt;False&lt;/FilterCaseSensitive&gt;&lt;Filters version="1"&gt;&lt;None version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/None&gt;&lt;Assembly version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Assembly&gt;&lt;AsymmetricKey version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/AsymmetricKey&gt;&lt;Certificate version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Certificate&gt;&lt;Contract version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Contract&gt;&lt;DdlTrigger version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/DdlTrigger&gt;&lt;Default version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Default&gt;&lt;EventNotification version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/EventNotification&gt;&lt;FullTextCatalog version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/FullTextCatalog&gt;&lt;FullTextStoplist version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/FullTextStoplist&gt;&lt;Function version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Function&gt;&lt;MessageType version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/MessageType&gt;&lt;PartitionFunction version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/PartitionFunction&gt;&lt;PartitionScheme version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/PartitionScheme&gt;&lt;Queue version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Queue&gt;&lt;Role version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Role&gt;&lt;Route version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Route&gt;&lt;Rule version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Rule&gt;&lt;Schema version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Schema&gt;&lt;Service version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Service&gt;&lt;ServiceBinding version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/ServiceBinding&gt;&lt;StoredProcedure version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/StoredProcedure&gt;&lt;SymmetricKey version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/SymmetricKey&gt;&lt;Synonym version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Synonym&gt;&lt;Table version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Table&gt;&lt;User version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/User&gt;&lt;UserDefinedType version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/UserDefinedType&gt;&lt;View version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/View&gt;&lt;XmlSchemaCollection version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/XmlSchemaCollection&gt;&lt;/Filters&gt;&lt;/ProjectFilter&gt;&lt;ProjectFilterName /&gt;&lt;UserNote /&gt;&lt;SelectedSyncObjects version="1" type="SelectedSyncObjects"&gt;&lt;Schemas type="ListString" version="2"&gt;&lt;value type="string"&gt;ZABiAG8A&lt;/value&gt;&lt;/Schemas&gt;&lt;ZABiAG8A&gt;xHHnGKCjdJcKPDjJpAbtLAICAPVvHeWVTco3IsGYU+jVlpoCAgDSHfGSAHcQMlchk6+h8AX2AgIAeRG7MhHrGx1o6YozUaTJ/AMCAA__&lt;/ZABiAG8A&gt;&lt;Grouping type="ListByte" version="2"&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;/Grouping&gt;&lt;SelectAll&gt;False&lt;/SelectAll&gt;&lt;/SelectedSyncObjects&gt;&lt;SCGroupingStyle&gt;0&lt;/SCGroupingStyle&gt;&lt;SQLOptions&gt;266&lt;/SQLOptions&gt;&lt;MappingOptions&gt;82&lt;/MappingOptions&gt;&lt;ComparisonOptions&gt;0&lt;/ComparisonOptions&gt;&lt;TableActions type="ArrayList" version="1" /&gt;&lt;SessionSettings&gt;15&lt;/SessionSettings&gt;&lt;DCGroupingStyle&gt;0&lt;/DCGroupingStyle&gt;&lt;/Project&gt;

and here is my command line

"C:\Program Files\Red Gate\SQL Compare 8\sqlcompare.exe" /pr:"RDB_DEV_PROD.scp" /scriptfile:"C:\Program Files\SQL Release\SQL Scripts\SQL 

Scripts RDB\A0000 SQLCompare.sql" /force

brigzy · September 24, 2009 11:46AM

brigzy wrote:

brigzy wrote:

Michelle Taylor wrote:

You might find that SQL Compare produces less refreshview statements if you create the script through the GUI (or if you need to use the command line, create a project in the GUI, save the project, and use the /pr: switch on the command line to use the project to generate the script).

The extra view refreshes are a known issue (SC-4542) which we'll hopefully get fixed soon.

Ok thankyou, I am trying the /pr switch

The GUI project finds differences ok.

When using /pr switch however I receive message :
Error: The selected objects are identical or no objects have been selected in the comparison.

Here is the project contents:

&lt;?xml version="1.0" encoding="utf-8" standalone="yes"?&gt;&lt;!--
SQL Compare
SQL Compare 8
Version:8.1.0.360--&gt;&lt;Project version="1" type="SQLComparisonToolsProject"&gt;&lt;DataSource1 version="2" type="LiveDatabaseSource"&gt;&lt;ServerName&gt;PARTNER-DEV&lt;/ServerName&gt;&lt;DatabaseName&gt;RDB_PARTNER_CHALL&lt;/DatabaseName&gt;&lt;Username /&gt;&lt;SavePassword&gt;False&lt;/SavePassword&gt;&lt;Password /&gt;&lt;IntegratedSecurity&gt;True&lt;/IntegratedSecurity&gt;&lt;/DataSource1&gt;&lt;DataSource2 version="2" type="LiveDatabaseSource"&gt;&lt;ServerName&gt;PARTNER&lt;/ServerName&gt;&lt;DatabaseName&gt;PARTNER_CHALL&lt;/DatabaseName&gt;&lt;Username /&gt;&lt;SavePassword&gt;False&lt;/SavePassword&gt;&lt;Password /&gt;&lt;IntegratedSecurity&gt;True&lt;/IntegratedSecurity&gt;&lt;/DataSource2&gt;&lt;LastCompared&gt;09/24/2009 14:40:33&lt;/LastCompared&gt;&lt;Options&gt;317002946838538&lt;/Options&gt;&lt;InRecycleBin&gt;False&lt;/InRecycleBin&gt;&lt;Direction&gt;0&lt;/Direction&gt;&lt;ProjectFilter version="1" type="DifferenceFilter"&gt;&lt;FilterCaseSensitive&gt;False&lt;/FilterCaseSensitive&gt;&lt;Filters version="1"&gt;&lt;None version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/None&gt;&lt;Assembly version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Assembly&gt;&lt;AsymmetricKey version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/AsymmetricKey&gt;&lt;Certificate version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Certificate&gt;&lt;Contract version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Contract&gt;&lt;DdlTrigger version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/DdlTrigger&gt;&lt;Default version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Default&gt;&lt;EventNotification version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/EventNotification&gt;&lt;FullTextCatalog version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/FullTextCatalog&gt;&lt;FullTextStoplist version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/FullTextStoplist&gt;&lt;Function version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Function&gt;&lt;MessageType version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/MessageType&gt;&lt;PartitionFunction version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/PartitionFunction&gt;&lt;PartitionScheme version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/PartitionScheme&gt;&lt;Queue version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Queue&gt;&lt;Role version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Role&gt;&lt;Route version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Route&gt;&lt;Rule version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Rule&gt;&lt;Schema version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Schema&gt;&lt;Service version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Service&gt;&lt;ServiceBinding version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/ServiceBinding&gt;&lt;StoredProcedure version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/StoredProcedure&gt;&lt;SymmetricKey version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/SymmetricKey&gt;&lt;Synonym version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Synonym&gt;&lt;Table version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/Table&gt;&lt;User version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/User&gt;&lt;UserDefinedType version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/UserDefinedType&gt;&lt;View version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/View&gt;&lt;XmlSchemaCollection version="1"&gt;&lt;Include&gt;True&lt;/Include&gt;&lt;Expression&gt;TRUE&lt;/Expression&gt;&lt;/XmlSchemaCollection&gt;&lt;/Filters&gt;&lt;/ProjectFilter&gt;&lt;ProjectFilterName /&gt;&lt;UserNote /&gt;&lt;SelectedSyncObjects version="1" type="SelectedSyncObjects"&gt;&lt;Schemas type="ListString" version="2"&gt;&lt;value type="string"&gt;ZABiAG8A&lt;/value&gt;&lt;/Schemas&gt;&lt;ZABiAG8A&gt;xHHnGKCjdJcKPDjJpAbtLAICAPVvHeWVTco3IsGYU+jVlpoCAgDSHfGSAHcQMlchk6+h8AX2AgIAeRG7MhHrGx1o6YozUaTJ/AMCAA__&lt;/ZABiAG8A&gt;&lt;Grouping type="ListByte" version="2"&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;value type="Byte"&gt;0&lt;/value&gt;&lt;/Grouping&gt;&lt;SelectAll&gt;False&lt;/SelectAll&gt;&lt;/SelectedSyncObjects&gt;&lt;SCGroupingStyle&gt;0&lt;/SCGroupingStyle&gt;&lt;SQLOptions&gt;266&lt;/SQLOptions&gt;&lt;MappingOptions&gt;82&lt;/MappingOptions&gt;&lt;ComparisonOptions&gt;0&lt;/ComparisonOptions&gt;&lt;TableActions type="ArrayList" version="1" /&gt;&lt;SessionSettings&gt;15&lt;/SessionSettings&gt;&lt;DCGroupingStyle&gt;0&lt;/DCGroupingStyle&gt;&lt;/Project&gt;

and here is my command line

"C:\Program Files\Red Gate\SQL Compare 8\sqlcompare.exe" /pr:"RDB_DEV_PROD.scp" /scriptfile:"C:\Program Files\SQL Release\SQL Scripts\SQL 

Scripts RDB\A0000 SQLCompare.sql" /force

Hi Michelle
can I ask if the above /pr is a known issue?
Is there a previous (older vserion) of SQLCompare, perhaps without the issue I could use meanwhile?

Many thanks

brigzy · September 24, 2009 12:02PM

I found problem is caused when a new project is created, the compare is run, but the Sync Wizard has not been run.

So I am over that hurdle and now the script is created just fine.

Indeed the /pr aproach has removed all the unwanted sp_refreshview statements from the scripts.

brigzy · September 30, 2009 3:33AM

I find there are a number of views that error when sp_refreshview is called, it would be really useful to be able to disable refresh of particular views.

Please could be logged as an enhancement request?

Mark J · March 5, 2015 12:28PM

Hi there

I think this behaviour is improved in our latest Frequent Updates release (11.1.7.47) - Compare should now refresh views less often; it will no longer refresh views unrelated to the deployment and will refresh no views if the 'Include Dependencies' option is disabled

Disable sp_refreshview

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk