Issues with "Generate Change Script"

Toad Data Modeler
1

I’m on the newest version (6.3)

First of all, would like a way for the function to take into account the “generate” flag on each item and only generate scripts for those.

Extended Properties:
Comparing model to SQL 2016. I have many extended properties that are set null until we figure out all the values to be populated. When reading extended properties from SQL 2016, modeler doesn’t pick up any of the ones with a NULL value and generates an “addproperty” for all of them which comes back and says “property already exists” when ran. My issues is going to be when I fill in the property in the model, it is going to try to run an “add” when it really needs to do an “update” and the property value will never get set.

Attributes:
I get a LOT of ALTER Table statements to ALTER COLUMN to the datatype and null/not null value that is already present. Tried different settings and was never able to eliminate these.

Temporal Tables:
This is where it gets really bad. Especially with “generates temp tables to preserve data” selected.

  1. It doesn’t seem to drop all the constraints
  2. It tries to create a history table with the same name as an existing one.
  3. It does not copy the history table data to preserve it.
  4. It does not take into account the file group selected under the Temporal Table Properties/History Table Properties for each table and automatically creates under PRIMARY.
  5. It seems the script functions are all out of order.
    I’ve yet to generate a change script that I didn’t have to spend half my day fixing before it could actually run and even then I had problems.
    Please help.

Thanks

Michael

2

Hello Michael,

  1. In current version Generate check from function properties form is not transfer to generate change script. Its purpose is only for Generate SQL Create Script(not change script). We created Issue on this TDM-4095, but it is not planned for next version. In current version it is possible to save selection of change script and use it next time, you can use it this function at this time.

  2. Empty Extended properties are not reversed in Toad Data Modeler 6.3. So it is reason why TDM is generate addproperty instead update in change script. We created Issue TDM-4096 you can track it in Release Notes. For this time we prepared workaround. In attachment is user package “OREDBMS16_user.txg”, which modify reverse engineering for SQL Serve 2016 (Empty Ext. Prop. will be reversed).
    How to install user package:

  • Run tdm and switch on expert mode (Main Menu - Settings - Options - General - Expert Mode)
  • In Main Menu should be now new menu item “Expert Mode”
  • Go to Main Menu - Expert Mode - Customization - Import Custom Package
  • After this restart TDM
    In fact this process only copy package to location defined in TDM. By Default it is c:\Users\USERNAMEXY\Documents\Toad Data Modeler\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC\ .

If you in future will upgrade to version, where will be this fixed, it s recommended delete this package.

  1. Could you specify cases where TDM generate “a LOT of ALTER Table statements to ALTER COLUMN…”? Two models will be ideal or you can describe theses cases.

  2. I think you probably use user package from community ( http://www.toadworld.com/products/toad-data-modeler/toad_data_modeler_beta_program/f/98/t/31195 ). It is obsoleted for version 6.3. Please try to delete it. In version 6.3 you can create new History Table by button “Create” in Entity Dialog on Tab “Temporal Table Properties”. In next step you can open new history Table and there set Filegroup and other properties.

Daril
OREDBMS16_user.zip (4.02 KB)

3

Thanks Daril.

Update to each of your points:

1) I can do without this for now. Would just be nice to have. Thanks for submitting it for future versions.

2) The user package works PERFECT for picking up empty extended properties and comparing.

3) I figured out what was causing all the ALTER COLUMN statements. 2 problems.

3a) Toad has a datatype of DATETIME2 which I used for all my dates. When ran on SQL Server 2016, it automatically creates a DATETIME2(7). When the columns are compared, it is not picking up that DATETIME2 is not the same as DATETIME2(x) so it creates an alter statement for all DATETIME2 columns.

3b) The second problem is due to collation. When scripted into SQL Server, all varchar’s collation is set to which in my case is SQL_Latin1_General_CP1_CI_AS. When it compares, it see collation on the SQL Server side, but none on the modeler side and generates an ALTER COLUMN statement with no collation.

Only fix I can think of for the datetime2 is to just remove it and make datetime2(x) the only option. No idea on the collation unless maybe if when reversing the database… IF collation on a column = database default and modeler collation IS NULL THEN ignore ELSE alter column to modeler collation? Or just simply ignore if modeler collation is NULL?

Is this something that can be addressed in a user package or do I need to start changing datatype and collation for 400+ attributes? I’ve worked out macros before, but not sure how to change datatypes. Could I get an example of changing DATETIME2 to DATETIME2(7) as well as filling in the collation for VARCHAR(x) and VARCHAR(MAX) attributes?

4) I removed the previous package and wow! You’ve really revamped the temporal table capabilities! I clicked ‘Create’ on each and generated the history table and changed the filegroup to my liking. Still a few issues though:

4a) The generate change script function does not detect that a history table is already present in SQL server and tries to recreate it even though they are identical. Is this the same problem I had with extended properties in that you just aren’t pulling them?

4b) All the history tables get lumped under ‘Entities’ with no way to filter down to non-history tables only, etc. Makes it really hard to organize. Can they either be split into two different subfolders under “Entites” OR the history table added under the expanded parent table similar to SQL Server Management studio?

4c) Hide the “Temporal Table Properties” on the Entity Properties of Temporal tables and hide “Temporal Properties” for Temporal table attribute properties. I didn’t try to make a temporal table for a temporal table because I didn’t want to muck it up, so I’m not sure it would even let me. Unless I’m completely wrong and you’d want to chain temporal tables?

Thanks so much for your help.

Michael

4

Hi Michael,

  1. You can use Model Update to synchronize your model with current state in DB.
    Or second option is script. Here is example how change data type (You can use it in scripting Window in TDM)

function main()
{
var Entity, Attribute, e, a;

var DataType_DateTime2 = Model.ModelDef.DataTypes.GetObjectByName(“Datetime2(%p1)”);
if (DataType_DateTime2 == null)
return;

Model.Lock();
for (e=0; e<Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
Entity.Lock();
for (a=0; a<Entity.Attributes.Count; a++)
{
Attribute = Entity.Attributes.GetObject(a);
if (Attribute.DataType != null)
if (Attribute.DataType.Name == “Datetime2”)
{
Attribute.SetLinkedObject(“DataType”, DataType_DateTime2);
Attribute.DataTypeParam1 = “7”;
Log.Information(“Attribute “+Attribute.Name+” in entity “+Entity.Name+” changed.”);
}
}
Entity.UnLock();
}
Model.UnLock();
}

Daril

5

Thanks so much Daril! I’ll test that out when I get a chance.

6

Daril. You are awesome!

Can I get a quick example of changing the collation for any alpha datatype (varchar(x),varchar(max), char, etc)?

Thanks much,

Michael

7

Hello,

this is code how to change collation for attirbute

function main(){
var Ent = Model.Entities.GetObject(0);
var Attr = Ent.Attributes.GetObject(0);
Attr.Collate = ‘ABC’;
Log.Information(Attr.Name+’ '+Attr.Collate);
}

Daril