Disable sp_refreshview

brigzybrigzy Posts: 14
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 (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'Refreshing [dbo].[Isokon_Tfb_Transactions]'
GO
EXEC sp_refreshview N'[dbo].[Isokon_Tfb_Transactions]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[up_Net2Matter_SetRecent_MatterContactAgenda]'
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[up_Net2Matter_SetRecent_MatterContactAgenda]
	(@EntityRef varChar(15), @MatterNo int, @ContactRef VarChar(15), 
	@AgendaID int, @UserID VarChar(20))
AS
BEGIN
	-- Create results table
	DECLARE @Result TABLE(EntityRef varChar(15),
						MatterNo int, 
						ContactRef varChar(15),
						AgendaID int,
						NewOrder int)
	-- Gather results 
	INSERT INTO @Result
	SELECT TOP 30 EntityRef, MatterNo, Contact, AgendaID,
			ROW_NUMBER() OVER (order by orderinlist) -1 as RowNumber
	FROM recentmatters 
	WHERE usercode = @UserID AND NOT
		 (EntityRef = @EntityRef AND MatterNo [email protected]) 
	-- 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 (UserCode, EntityRef, MatterNo, Contact, AgendaID, OrderInList)
	SELECT @UserID, EntityRef, MatterNo, ContactRef, AgendaID, NewOrder FROM @Result
END
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Usr_CreditCheck]'
GO
ALTER TABLE [dbo].[Usr_CreditCheck] ADD
[ResultsEntityRef] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResultsStepID] [int] 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
PRINT N'Refreshing [dbo].[Chart_Of_Accounts_GroupView]'
GO
EXEC sp_refreshview N'[dbo].[Chart_Of_Accounts_GroupView]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[StepPostInDirect]'
GO
EXEC sp_refreshview N'[dbo].[StepPostInDirect]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[vw_ClientBills]'
GO
EXEC sp_refreshview N'[dbo].[vw_ClientBills]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[vw_ClientBills_ShortSummary]'
GO
EXEC sp_refreshview N'[dbo].[vw_ClientBills_ShortSummary]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[ViewTaskList]'
GO
EXEC sp_refreshview N'[dbo].[ViewTaskList]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Dm_TimePostings]'
GO
EXEC sp_refreshview N'[dbo].[Dm_TimePostings]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[FM_fFiles]'
GO
EXEC sp_refreshview N'[dbo].[FM_fFiles]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Chall_View_DisbsOUT]'
GO
EXEC sp_refreshview N'[dbo].[Chall_View_DisbsOUT]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[F_OrgTypeLnk]'
GO
EXEC sp_refreshview N'[dbo].[F_OrgTypeLnk]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[PBC_fWorkTypes]'
GO
EXEC sp_refreshview N'[dbo].[PBC_fWorkTypes]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Chall_View_DisbsIN_Sept2008]'
GO
EXEC sp_refreshview N'[dbo].[Chall_View_DisbsIN_Sept2008]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Chall_View_DisbsOUT_Sept08]'
GO
EXEC sp_refreshview N'[dbo].[Chall_View_DisbsOUT_Sept08]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Chall_IncomeVsTargets]'
GO
EXEC sp_refreshview N'[dbo].[Chall_IncomeVsTargets]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[vw_LeverArchLabels]'
GO
EXEC sp_refreshview N'[dbo].[vw_LeverArchLabels]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[AllMatters]'
GO
EXEC sp_refreshview N'[dbo].[AllMatters]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[MARFEE]'
GO
EXEC sp_refreshview N'[dbo].[MARFEE]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Corebridge_Directory]'
GO
EXEC sp_refreshview N'[dbo].[Corebridge_Directory]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[AcPLAgCrMatterAlloc]'
GO
EXEC sp_refreshview N'[dbo].[AcPLAgCrMatterAlloc]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Ac_CashAccountTransactions]'
GO
EXEC sp_refreshview N'[dbo].[Ac_CashAccountTransactions]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[vw_CaseSteps]'
GO
EXEC sp_refreshview N'[dbo].[vw_CaseSteps]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[VIEW_LGFS_DATA]'
GO
EXEC sp_refreshview N'[dbo].[VIEW_LGFS_DATA]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[StepTime]'
GO
EXEC sp_refreshview N'[dbo].[StepTime]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[StepPostDirect]'
GO
EXEC sp_refreshview N'[dbo].[StepPostDirect]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[Chall_View_DisbsIN]'
GO
EXEC sp_refreshview N'[dbo].[Chall_View_DisbsIN]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[F_Organisations]'
GO
EXEC sp_refreshview N'[dbo].[F_Organisations]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[F_OrganisationsMatter]'
GO
EXEC sp_refreshview N'[dbo].[F_OrganisationsMatter]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[PBC_fClients]'
GO
EXEC sp_refreshview N'[dbo].[PBC_fClients]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[CDSDataView]'
GO
EXEC sp_refreshview N'[dbo].[CDSDataView]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[VIEW_CLAIMSTOAPPROVE]'
GO
EXEC sp_refreshview N'[dbo].[VIEW_CLAIMSTOAPPROVE]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Refreshing [dbo].[AcPLAgCrNominalAlloc]'
GO
EXEC sp_refreshview N'[dbo].[AcPLAgCrNominalAlloc]'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Usr_IDCheck]'
GO
ALTER TABLE [dbo].[Usr_IDCheck] ADD
[ResultsEntityRef] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResultsStepID] [int] 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
ALTER TABLE [dbo].[Usr_IDCheck] DROP
COLUMN [PDFStepID]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[Usr_Trace_Check]'
GO
ALTER TABLE [dbo].[Usr_Trace_Check] ADD
[ResultsEntityRef] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResultsStepID] [int] 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
SET QUOTED_IDENTIFIER ON
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

Richard

Comments

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

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

    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
    Richard
  • 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.
    Richard
  • 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?
    Richard
  • 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
Sign In or Register to comment.