[SA 9.0.176 Pro] - Script DDL bug?

[SA 9.0.176 Pro] - Script DDL bug?

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

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?

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.

	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.

	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.

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.