Toad World® Forums

Comparing Model To SQL Server 2016 Database

Hi,

I’ve just moved to SQL 2016 and done a simple data model which i then used to generate a script to build the database tables (using TDM 6.1.3.26).

However, without making any changes to either model or db, when I do a compare I get a large number of differences which can basically be summarised as the same set of 5 or 6 fields per table:-

  • Attribute collation - this is not set in the model, but the compare has detected the collation applied and flagged it as a difference

  • Entity index properties (5 spatial properties set in model, but not in db and type of index set as relational in db but not in model)

I know I can remove these fields in the advanced settings, but there seems to be no way for this settings change to be remembered for the next time and it is quite laborious to have to keep unchecking all these item boxes (not to mention the large possibility of making a mistake!).

So is there any way to have the compare tool remember these settings changes for future compares?

Alternatively, is there any way to set up defaults when creating model items so that at least the model side doesn’t add anything in that doesn’t seem to be used by SQL 2016 (spatial fields, collation)? If so, can this be applied retrospectively to an existing model or will it only work for new models and items?

Thanks

Dan

Hi Dan,

This differences are due to default values. In Toad Data Modeler you don't need specify all value at all, empty values are filled automatically by generate Create Script. Sometime by TDM logic and sometime by Default Value set in DB.

Remember Settings Changes in Compare Tool

I'm not sure if you use only Compare functionality, or Compare and Merge or Generate Change Script. There are more possibilities.

  1. You can save selection from compare tree and store it to file. See toolbar in Wizard and Buttons Import/Export Selection. It is accessible only for Generate Change Script, Merge Models and Convert Model. For MOdel compare you can not define selection.
    CompareTreeImportExportSelection.png

  2. You can Define "Selected Object Types". This settings is good, when you know that you never want compare some property as Collation for all objects in compare session. To store this selection you need switch on "Expert Mode" (Main Menu - Settings -Options - General - Expert Mode). When you have switch on Expert Mode, you will see in wizard on frame "Select Object Type" toolbar with button for save and load selection. Saved selection yo can repeatable use, or store all to Model Action(see next point).

  3. You can use Model Actions functionality. See Main Menu Windows - Model Actions. You can store actions with selections and use it again very quickly.

Default Values

In Toad Data Modeler you can define Default value this value will be set when you create new object. Go to "Main Menu - Settings - Default Values" for all classes in current model, or on each dialog of object you can choose from popup menu "Default Values" only for current class.

6560.DefaultValues.png

Attribute Collation

When you leave empty Attribute Collation it will be set by default value set in db. You can after run create script on db run model update to set this values back to model.

Index Type

When you leave empty Index Type it will be evaluated during generate Create Script by Toad Data Modeler. It dependent on Attribute data type. You can after run create script on db run model update to set this values back to model.

Spatial Properties

In this case I don't understand well. Did you set index type to spatial, when you created index with spatial properties in Model? Maybe you can do this case more clear when you write example of index, and generated SQL code.

Retrospective set Default Values

I see two ways:

  1. Use Model update.
  2. Write some simple Macro or script for set properties. If you want write any macro or script you need switch on "Expert Mode"

Regards Daril

Hi Daril,

Thanks for the really detailed answer J it may take me some time to go through it, but I like the fact that I can set some defaults at model level, that will help!

I can give you more info though immediately on the spatial question, it must have been defaults applied as I did not specify any of the spatial properties when I created the index as a result of adding a new relationship. I have pasted the sql generated which doesn’t have any mention of spatial properties. I have also snipped a screenshot of the compare for that table (model left, db right). I have checked the properties page of the index in TDM and it does indeed have those values of MEDIUM and 16.

I don’t actually want any spatial properties and I am quite happy with the generated sql as it is so I will try emptying those properties out in the model and see what happens!

Cheers

Dan

CREATE TABLE [platform_channel]

(

[ID] Int IDENTITY(1,1) NOT NULL,

[platform_id] Int NOT NULL,

[channel_id] Int NOT NULL,

[name] Nvarchar(256) NOT NULL,

[legacy_name] Nvarchar(256) NOT NULL,

[delivery_lead_time] Int NULL,

[created] Datetime NOT NULL,

[last_modified] Datetime NOT NULL

)

go

– Create indexes for table platform_channel

CREATE INDEX [IX_platform_platform_channel_FK] ON [platform_channel] ([platform_id])

go

CREATE INDEX [IX_channel_platform_channel_FK] ON [platform_channel] ([channel_id])

go

– Add keys for table platform_channel

ALTER TABLE [platform_channel] ADD CONSTRAINT [platform_channel_PK] PRIMARY KEY ([ID])

go

From: Daril [mailto:bounce-Daril@toadworld.com]
Sent: 08 March 2017 14:08
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Comparing Model To SQL Server 2016 Database

RE: Comparing Model To SQL Server 2016 Database

Reply by Daril

Hi Dan,

This differences are due to default values. In Toad Data Modeler you don’t need specify all value at all, empty values are filled automatically by generate Create Script. Sometime by TDM logic and sometime by Default Value set in DB.

Remember Settings Changes in Compare Tool

I’m not sure if you use only Compare functionality, or Compare and Merge or Generate Change Script. There are more possibilities.

  1. You can save selection from compare tree and store it to file. See toolbar in Wizard and Buttons Import/Export Selection. It is accessible only for Generate Change Script, Merge Models and Convert Model. For MOdel compare you can not define selection.
  2. You can Define “Selected Object Types”. This settings is good, when you know that you never want compare some property as Collation for all objects in compare session. To store this selection you need switch on “Expert Mode” (Main Menu - Settings -Options - General - Expert Mode). When you have switch on Expert Mode, you will see in wizard on frame “Select Object Type” toolbar with button for save and load selection. Saved selection yo can repeatable use, or store all to Model Action(see next point).
  3. You can use Model Actions functionality. See Main Menu Windows - Model Actions. You can store actions with selections and use it again very quickly.

Default Values

In Toad Data Modeler you can define Default value this value will be set when you create new object. Go to “Main Menu - Settings - Default Values” for all classes in current model, or on each dialog of object you can choose from popup menu “Default Values” only for current class.

Attribute Collation

When you leave empty Attribute Collation it will be set by default value set in db. You can after run create script on db run model update to set this values back to model.

Index Type

When you leave empty Index Type it will be evaluated during generate Create Script by Toad Data Modeler. It dependent on Attribute data type. You can after run create script on db run model update to set this values back to model.

Spatial Properties

In this case I don’t understand well. Did you set index type to spatial, when you created index with spatial properties in Model? Maybe you can do this case more clear when you write example of index, and generated SQL code.

Retrospective set Default Values

I see two ways:

  1. Use Model update.
  2. Write some simple Macro or script for set properties. If you want write any macro or script you need switch on “Expert Mode”
    Regards Daril

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler Forum notifications altogether.
Toad Data Modeler - Discussion Forum

Flag this post as spam/abuse.

Hi Dan,

“Spatial Cells Per Object” and “Spatial Level” are set to “16”, “MEDIUM” in SQL Server 2008. If your model was originally SQL 2008, so it is reason.

You can clear this values by model update or script.

Daril

Aaahhhhh that’ll be the reason J yes I copied in tables from my 2008 model!

That is brilliant, many thanks for all your help Daril!!

Cheers

Dan

From: Daril [mailto:bounce-Daril@toadworld.com]
Sent: 09 March 2017 09:16
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Comparing Model To SQL Server 2016 Database

RE: Comparing Model To SQL Server 2016 Database

Reply by Daril

Hi Dan,

“Spatial Cells Per Object” and “Spatial Level” are set to “16”, “MEDIUM” in SQL Server 2008. If your model was originally SQL 2008, so it is reason.

You can clear this values by model update or script.

Daril

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler Forum notifications altogether.
Toad Data Modeler - Discussion Forum

Flag this post as spam/abuse.