SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - Script DDL bug?

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - Script DDL bug?
Author Message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post [SA 9.0.176 Pro] - Script DDL bug? Reply with quote
I was testing dumping my DB schema (tables, constraints, triggers, sprocs, etc) and importing them into an express instance. Overall it seems to have gotten the job done (aside from logins not existing and spewing errors) but there are SEVERAL errors in my execution status that tells me "Procedure XYZ cannot be created because the object name already exists".

This database was fresh, nothing in it... I just made it:

So I went to the source file:

It seems SOMETHING is duplicating ALL of the triggers during extraction:

Code:

Line 4291: CREATE TRIGGER [BottleOrderTest_ITrig] ON [dbo].[BottleOrderTest]
   Line 4327: CREATE TRIGGER [BottleOrderTest_UTrig] ON [dbo].[BottleOrderTest]
   Line 4411: CREATE TRIGGER [BottleOrderTest_ITrig] ON [dbo].[BottleOrderTest]
   Line 4437: CREATE TRIGGER [BottleOrderTest_UTrig] ON [dbo].[BottleOrderTest]



If I go look at those, the triggers are identical.

The export log doesn't show repetition that I can find:
Code:
Line 46: Scripted out object [DB].[dbo].[BottleOrderGroup_ITrig] (0.78 ms) ... OK
   Line 47: Scripted out object [DB].[dbo].[BottleOrderGroup_UTrig] (0.78 ms) ... OK

Thu Nov 17, 2016 3:35 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
I re-ran the export but this time deselected everything and turned on everything but "SELECT All Triggers in current Schema", no duplicates.

Then I re-ran it again with just one solitary table selected, and indeed it does get the trigger.

Soooo... there seems to be a disconnect there. Perhaps the "get table" logic shouldn't get the trigger if the trigger is a separate object to select?
Thu Nov 17, 2016 3:45 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I'm not quite following how you're doing that. The Script DDL function is not designed for database synchronization. The easiest method is to use the Schema Compare tool, which should figure out automatically what needs to be copied. That tool isn't based on the DDL scripting function.
Thu Nov 17, 2016 4:04 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
SysOp wrote:
I'm not quite following how you're doing that. The Script DDL function is not designed for database synchronization. The easiest method is to use the Schema Compare tool, which should figure out automatically what needs to be copied. That tool isn't based on the DDL scripting function.


It wasn't a synchronization. I dumped the DDL and pasted the script into a new query window on my new (fresh) target DB.

It then ran and spat out a bunch of errors about triggers that already exist (because the script duplicated them) because it gets it in the Table Schema and as a separate "Trigger" object.
Thu Nov 17, 2016 4:05 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Quote:
I dumped the DDL and pasted the script into a new query window on my new (fresh) target DB.
. Thank you I understand it now. The script DDL tool isn't designed for that kind of use. It's more or less a generic function that scripts out selected objects and from that function's point of view triggers may appear in two places, as part of a table DDL to script the "complete" table definition and its attributes. and also as separate procedural objects so that they can be scripted separately. If you select both tables and triggers, they get scripted twice as in your example. One way to mitigate that is to uncheck triggers item in list items options so that triggers aren't shown as separate objects. Btw, in some databases trigger codes aren't tied to tables, there are separate triggers and trigger functions.

Hope this makes sense. If you think it's very confusing I can submit a ticket asking to optimize the processing and add a check to not script triggers twice if they are already scripted with parent table definitions.
Thu Nov 17, 2016 4:36 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
SysOp wrote:
Quote:
I dumped the DDL and pasted the script into a new query window on my new (fresh) target DB.
. Thank you I understand it now. The script DDL tool isn't designed for that kind of use. It's more or less a generic function that scripts out selected objects and from that function's point of view triggers may appear in two places, as part of a table DDL to script the "complete" table definition and its attributes. and also as separate procedural objects so that they can be scripted separately. If you select both tables and triggers, they get scripted twice as in your example. One way to mitigate that is to uncheck triggers item in list items options so that triggers aren't shown as separate objects. Btw, in some databases trigger codes aren't tied to tables, there are separate triggers and trigger functions.

Hope this makes sense. If you think it's very confusing I can submit a ticket asking to optimize the processing and add a check to not script triggers twice if they are already scripted with parent table definitions.


I think it might make sense to have a warning that if triggers are selected it WILL produce duplicates at the very least.

I am checking out schema compare now.. that's slick.
Thu Nov 17, 2016 4:40 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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
Design by Freestyle XL / Flowers Online.