Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Discussion of open issues, suggestions and bugs regarding database management and development tools for SQL Server
Post Reply
Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Fri 15 Jun 2018 09:54

Hi,

We have some merge replication tables, when we made some changes in triggers on DEV environment and want push those changes to production envirnment with dbForge schema comapre it creates a script like

EXEC sp_executesql N'CREATE OR ALTER TRIGGER ...

But replication works only with "ALTER TRIGGER ...", when I execute script the schema change won't replicate to the subscribers.

BOL: https://docs.microsoft.com/en-us/sql/re ... erver-2017
If you make the following schema changes to a published article, they are propagated, by default, to Microsoft SQL Server Subscribers:
ALTER TABLE
ALTER VIEW
ALTER PROCEDURE
ALTER FUNCTION
ALTER TRIGGER
Can I disable this "CREATE OR ALTER" functionality in dbForge Schema Compare? Our current version 4.4.61

Regards,
ganendran

alexa

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by alexa » Mon 18 Jun 2018 11:41

Please use the 'Use DROP and CREATE instead of ALTER for views, procedures, functions and triggers' option on the 'Options' page of the schema synchronization wizard.

Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Wed 18 Jul 2018 13:26

Hi,

Schema changes are only replicated to the subscriber if I execute the trigger as ALTER ... (Drop and create won't populate to the subscribers with merge replication)

Al also I tried the "DROP AND CREATE" method, but still the script is generated with "CREATE OR ALTER"

Code: Select all

--
-- Drop trigger [trfOutKwaliteit] on table [EAI].[OutKwaliteit]
--
DROP TRIGGER IF EXISTS [EAI].[trfOutKwaliteit]
GO
IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

--
-- Create trigger [trfOutKwaliteit] on table [EAI].[OutKwaliteit]
--
GO
IF OBJECT_ID(N'[EAI].[trfOutKwaliteit]', 'TR') IS NULL
EXEC sp_executesql N'CREATE OR ALTER TRIGGER [trfOutKwaliteit] ON  [EAI].[OutKwaliteit] 
AFTER INSERT, DELETE
AS 
BEGIN
...
...


alexa

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by alexa » Fri 20 Jul 2018 10:12

Could you please clarify this? If we understand you correctly, you need it to generate "ALTER TRIGGER..." instead of "CREATE OR ALTER TRIGGER..." in the sync script for object modifying.

Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Fri 20 Jul 2018 12:20

Hi,

Yes that would be nice if the script generate "ALTER trigger ..." instead of "CREATE or ALTER Trigger ..."
Regards,
Ganendran

AlexandrS
Devart Team
Posts: 20
Joined: Mon 22 Jan 2018 11:27

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by AlexandrS » Mon 30 Jul 2018 11:20

Hello,

We have planned this function to be available in the future product builds.

Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Fri 23 Aug 2019 06:47

Hi,

In the previous version you solved this with 'ALTER statement', today I installed the new version "dbForge Compare Bundle for SQL Server Professional' version 5.7.22, and it is again with "CREATE OR ALTER"

Regards,
Ganendran

alexa

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by alexa » Fri 23 Aug 2019 13:42

For, SQL Server 2016 (version 13.0.1601.5), dbForge generates a sync script with "CREATE" and "ALTER" for procedures, views, functions and triggers as it does for SQL Server 2012 and 2014.

For, SQL Server 2016 SP1 (version 13.0.4001.0) and higher dbForge generates a script with "CREATE OR ALTER".

Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Fri 23 Aug 2019 14:01

Hi Alexa,
Yes, uou made the changes correctly with the previous compare bundle version with 'ALTER' but now with the new version its again 'CREATE OR ALTER' if you have a merge replication and when you execute the script ex. CREATE OR ALTER trigger.. then the trigger won't replicate to the subscriber(s).

Compare bundle 5.7.1 'CREATE OR ALTER'
Compare bundle 5.7.20 'ALTER'
Compare bundle 5.7.22 'CREATE OR ALTER'

alexa

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by alexa » Tue 27 Aug 2019 10:52

Could you please provide us the server's version you are experiencing issues with?

Ganendran
Posts: 14
Joined: Tue 21 Nov 2017 14:51

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by Ganendran » Tue 27 Aug 2019 11:30

Hi,

Server version : Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64)

If you read de full issue comments here I already asked to add this feature and you already made changes I think because it worked in de previous version but I don't know why it changed back again. Replicated triggers only replicated to the subscribers with 'ALTER' statement.

https://docs.microsoft.com/en-us/sql/re ... erver-2017

alexa

Re: Can I disable new SQL Server 2016 feature "CREATE OR ALTER" statement ?

Post by alexa » Tue 08 Oct 2019 09:26

Sorry for the delay on this. We will fix this in one of the next product versions.

Post Reply