Use Drop and Create Instead Of Alter Option did not work with source control

chuck

Hi,
I am using sql comapre to generate scripts, I am using source control revision as data source.
I configure the option "use drop and create instead of alter".
However, the scripts genereted still keep alter procedure.

I have tried compare two databases as datasource,the option works fine.

Is this a bug only for source control?


The version of sql compare is 13.0.5.5219
The version of sql source control is 5.7.6.6321

chuck

I have found the latest version for sql compare is 13.1.0.5287 - October 16th 2017 from release notes
But when I use check for updates, the program can not found the newer version.

The latest version of sql source control is6.0.2.6908 - October 17th, 2017 ,but the program still can not check this update

sam.blackburn

SQL Compare 13.1 is currently only available on our frequent updates channel (although we expect to release it to everyone very soon). To get these updates sooner, you can turn on frequent updates as described here:

https://documentation.red-gate.com/display/SC13/Turning+on+Frequent+Updates

chuck

sam.blackburn wrote: »

SQL Compare 13.1 is currently only available on our frequent updates channel (although we expect to release it to everyone very soon). To get these updates sooner, you can turn on frequent updates as described here:

https://documentation.red-gate.com/display/SC13/Turning+on+Frequent+Updates

Hi,
I want to know if you have fixed this problem, or you just designed it like this.(can not use drop and create when use git as source for sql compare)

Tianjiao_Li

Hi @chuck ,

SQL Source Control use the same compare engine as SQL Compare. It should work in the same way no matter the source is from live database or source control. Is that possible to share the deployment script?

chuck

Hi @Tianjiao_Li ,
This is the deploy script I get from sql compare, and I am pretty sure that I have checked “use drop and create instead of alter”.
However, the options did not work.

You can test it by yourself to confirm it.
It's very simple to reproduce it, just create a new database,and add a stored procedure to database and commit it.
Then modify it,and commit it again.
Then you can use sql compare to generate the script by compare two commits in git repository.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ProcedureName]'
GO
IF OBJECT_ID(N'[dbo].[ProcedureName]', 'P') IS NOT NULL
EXEC sp_executesql N'-- =============================================
-- Author:		Chuck Lu
-- Create date: 2017-10-27
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[ProcedureName] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT Id ,
           Age ,
           NAME FROM dbo.Test
END
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO

Tianjiao_Li

Hi @chuck ,

Thanks and I've reproduced the issue. I'll update you shortly.

Tianjiao_Li

Hi @chuck ,

We've logged it as a bug (SC-9791) and will update you when there is a fix!

Sorry for the inconvenience caused.

Tianjiao_Li

Hi @chuck ,

Thanks for your patience with this issue.

Unfortunately the development team hasn't been able to prioritise this work. Given the size of the backlog of higher-priority items, it doesn't look like it's something we will fix in the foreseeable future.

However we still have it logged as SC-9791 in our internal bug tracking system and we'll reassess it again in the future. Please keep an eye on the release note!

chuck

Tianjiao_Li wrote: »

Hi @chuck ,

Thanks for your patience with this issue.

Unfortunately the development team hasn't been able to prioritise this work. Given the size of the backlog of higher-priority items, it doesn't look like it's something we will fix in the foreseeable future.

However we still have it logged as SC-9791 in our internal bug tracking system and we'll reassess it again in the future. Please keep an eye on the release note!

Understand, thanks.

chuck

chuck said:

Tianjiao_Li wrote: »

Hi @chuck ,

Thanks for your patience with this issue.

Unfortunately the development team hasn't been able to prioritise this work. Given the size of the backlog of higher-priority items, it doesn't look like it's something we will fix in the foreseeable future.

However we still have it logged as SC-9791 in our internal bug tracking system and we'll reassess it again in the future. Please keep an eye on the release note!

Understand, thanks.

Hi,

   I found  you fix this bug in this release version

13.3.6.6375 - June 4th 2018

SC-9791: The 'DROP and CREATE instead of ALTER' option is no longer ignored when creating a deployment script.