Toad World® Forums

Toad for SQL Server Beta 6.6.0.516 - big problem in Schema Compare

Last beta version of TOAD.

I compare schema of two DB's and TOAD tell me that there are some different.

I attach a screenshot of the problem :

As you can see TOAD tell me that these two views are different.

For me these two views are identical. Why TOAD consider these views different ?

Have you some idea ?

Best regards,

Sergio

Hi Sergio,

Thank you. As I know, some comparison options setting and properties will make the result look like this.

For example, I create a view select table with an column Collation are different. And when I comparing, I do not check the options 'Ignore Collation', I got the result below.

We can see the different in Properties.

if I check 'Ignore Collation' when comparing. the result will show equal.

Compare Options.jpeg

Please Kindly check if there some properties are different.

Best Regards,

Kelly

HI Kelly,

if I check 'ignore collation' I obtain the same result.

I attach a screenshot with my schema compare option :

Have you some others idea ?

Best regards,

Sergio

Hi Sergio,

Thank you very much for your detail. Would you please provide the Table you select to us? Thank you.

Best Regards,

Kelly

Hi Kelly,

in attach a rar file.

This file contains 2 scripts. Source.sql and target.sql.

These scripts contains metadatas of view that generate the problem and relative tables used by this view.

I stay tune for your answer,

Best regards,

Sergio
toadSS66.rar (1.29 KB)

Hi Sergio,

Thank you very much for your provide. I compare these two Views and the result shows they are same.

There a bug in Toad now, that is Schema Compare View cannot detect the change of table. For example:

Step:

  1. Create two different table: TableA and TableB. Column_1 in TableA ‘Not null’ is ture, Column_1 in TableB is false

2.Create two views as select table.

3.Compare two database.

  1. The result show views are different.

  2. Now, we alter TableB, make it same as TableA.

  3. Then we compare again.

Result: we found that the view still show differnt in result. The expect result should be same.

Is this bug affect your case? After we create and compared Views, did the tables been altered ?

Best Regards,

Kelly

Hi Kelly,

many thank’s for your support.

I create 2 DB’s with a tool. This tool connect to an ODBC data source and first create a SQL db and after pump data from source ODBC to SQL table.

I create manually view that generate the problem. I save the script and execute it on first and second DB.

I alter table DETAIL_SCADENZE to set field CONTA autoincrement and not null and field SYNCTIME to timestamp.

That’s all.

I hope this can help you to indetify the problem.

Best regards,

Sergio

Hi Sergio,

Thank you very much. According to the steps you provided. I think that is the same bug we mentioned. The bug number is TSS-881. I will keep you update for this. Sorry for your inconvenience.

Best Regards,

Kelly

Hi Kelly,

OK. Many thank’s for the TSS-881.

I stay tune to test the problem in the next beta version of TOAD for SS.

Best regards,

Sergio

Hi Kelly,

in Toad for SQL Server Beta 6.6.0.604 the problem is still present.

Best regards,

Sergio

Hi Sergio,

TSS-881 haven’t been fixed in this Beta 6.6.0.604. Thank you very much for testing.

Best Regards,

Kelly

Hi Kelly,

no problem. I test it in the future on the new beta when will be available.

Best regards,

Sergio

Hi Kelly,

in Toad for SQL Server Beta 6.6.0.920 the problem is still present.

Best regards,

Sergio

Hi Sergio,

Sorry, TSS-881 haven’t been fixed. Now I marked it to be fixed in 6.7 release.

Thank you very much for you support.

Best Regards,

Kelly

Hi Kelly,

this is not a great news. This is a serious problem.

Best regards,

Sergio

Hi Sergio,

Issue TSS-881, the problem is because that after we alter table properties, not update the saved data for view. So when we get data to compare view or review the View Details, we cannot see the latest data.

For schema compare, it can only get data from saving . We cannot make any progress for this issue in Schema Compare.

For now, if we in this case, we can run below sql to refresh the data manually.

sp_refreshview ‘ViewName’

Best Regards,

Kelly

Hi Kelly,

for now many thank’s for your help and support.

I appreciate it too much.

But for the future I would like to see the issue resolved. I don’t want manually execute any sp_XXX.

Maybe in the new Beta version 6.7 of TOAD SS when will be available.

Best regards,

Sergio

Kelly,

Are you saying that when we update a table, we have to manually hunt down every view that references that table and execute sp_refreshview on it before Schema Compare will produce the result that we desire?

Is there any enhancement that can be made in Toad to handle this automatically?

  • Mark

Hi Mark,

Toad retrieves data for the views from a list of system views(e.g., sys.objects, sys.types etc.). Certain data from those views are not updated after the parent
tables are altered. One of the ways to push the changes to the system views is sp_refreshview. We understand it is difficult for users to find all references of the table and execute the sp, however this is how SQL Server itself handles the changes. We created
TSS-1025 to find a more proper solution to make sure Toad gets the up-to-date data for view for both view details and schema compare.

Thanks,

Vincent

Hi Kelly,Vincent

Toad for SQL Server Beta 6.7.0.65. I test my problem with last Beta 6.7.

For me the problem was solved.

I see the list of bug corrected with initial release of new beta version of TOAD SS but I never find TSS-881.

Kelly, Vincent can you tell me or confirm me if the problem was solved ?

I stay tune for your consideration,

Best regards,

Sergio