Does SQL Data Compare support External Tables?

• 
  Hi @rgben
  
  I got the following info:
  
  External tables are similar to Linked Servers in on-prem SQL Server - it maintains a connection to an outside database so that you can connect and query easily from within this database.
  
  If compatible - Data Compare can connect directly to the database that the "external table" is actually in - but it cannot use the external table connection to compare data.
  
  

  DanC January 03, 2023 13:27

  - 0 +
• 
  Thanks, Dan, for getting back with me. It sounds like I may be able to connect to the Azure Synapse Lake Database, which is the source for the External Tables in the Azure SQL Database.
  
  Not a bad idea I think my trial has expired, though, so I cannot confirm it will work.

  rgben January 03, 2023 20:31

  - 0 +
• 
  Hi @rgben
  
  Brilliant, sounds like a plan!
  
  If you get in contact with Sales they can extend a trial license for you to test this!

  DanC January 04, 2023 10:40

  - 0 +
• 
  I got an extension and connected to the Azure Synapse on-demand instance using authentication type Active Directory universal with MFA.
  
  Everything seemed to go well, but I do not think Data Compare can properly list the databases in Azure Synapse. I got an error message: 'sysdatabases' is not supported.
  
  I think Data Compare is trying to query a sysdatabases object (to populate the Database drop-down menu) which does not exist in Synapse apparently.

  rgben January 06, 2023 04:33

  - 0 +
• 
  Hi @rgben
   
  Unfortunately using Azure Synapse as the source for comparison isn't supported in either SQL Compare nor SQL Data Compare and this will be the issue!
  

  DanC January 10, 2023 10:42

  - 0 +
• 
  Hey, so I made a workaround for this recently. I was not working on this for a while, but when I returned, I had a new idea. It is a little tedious, but it works wonderfully.
  
  I do a Select * Into NewTableName From ExternalTableName to copy all data from the External Table into a "real" table in the database. Then I can use the Data Compare tool against the two "real" tables. It works!
  
  So, I will need to use this technique table by table, but that is ultimately a great solution for people with a small table count. And in the case of one of my sample tables, I was comparing 400 columns' data, so the tool was still extremely helpful!

  rgben February 28, 2023 22:13

  - 0 +

Add comment

Please sign in to leave a comment.