What objects are not supported in the Base Line and Migrations ?

pkettlew

I do not get any of the "external data" sources scripted in the baseline scripts , causing errors in the build?


What is not supported and what do I need to do to manage this ?

Kendra_Little

Hi @pkettlew ,

Currently, External Tables are not supported by the SQL Compare Engine and they are not scripted out.

• External Tables / External Data Sources / External File Formats are the main thing that has come up recently which are not scripted.
• The main categories of other things which aren't scripted are database configuration properties and settings and instance-level things like SQL Server Agent jobs, linked servers.
• There are some "dependency chain" scenarios which cause some objects to script as "unsupported", which is a slightly different scenario.

Work for adding support of External Tables is currently ongoing, and we expect support for External tables to ship this quarter (Q1 2021) in SQL Compare and its related tools (including SQL Change Automation).

Creating External Tables in a pre-deployment script is the current workaround. 

Kendra

pkettlew

hi 

so all objects in the baseline scripts that are associated with those external tables should be in the pre-deployment scripts ?   schema, views, procedures 

I need to change the base line script to remove all references to the external tables - is this correct ?

Kendra_Little

Hi,

The definition of the external tables and anything that the external tables are dependent on (ie the external data source) would need to be set up by the pre-script.

Things that reference an external table, like views, procedures, etc, do not need to be modified in the baseline script (the baseline script is run *after* the pre-deployment script.)

Hope this helps,
Kendra

pkettlew

i have done this , but consistently get this error . in SSMS and SCA 4

RedGate.Versioning.Client.VsShell.Ssms.Models.Observable.UnhandledObserverException: A subscribed observable threw an unhandled exception: Object reference not set to an instance of an object. ---> System.NullReferenceException: Object reference not set to an instance of an object.

   at RedGate.Versioning.Client.VsShell.Ssms.Ui.Utilities.ObjectFilterer.<>c__DisplayClass0_0`1.<Filter>b__0(T x)

   at System.Linq.Enumerable.WhereArrayIterator`1.MoveNext()

   at System.Collections.ObjectModel.CollectionExtensions.AddRange[T](Collection`1 collection, IEnumerable`1 items)

   at RedGate.Versioning.Client.VsShell.Ssms.Ui.Views.GenerateMigrations.GenerateMigrationsViewModel.UpdateFilteredDifferences()

   at RedGate.Versioning.Client.VsShell.Ssms.Ui.Views.GenerateMigrations.GenerateMigrationsViewModel.OnComparisonModelUpdated(ProjectComparisonModel comparisonModel)

   at RedGate.Versioning.Client.VsShell.Ssms.Models.Observable.ActionObserver`1.OnNext(T value)

   at RedGate.Versioning.Client.VsShell.Ssms.Models.Observable.Subject`1.Next(T value)

   --- End of inner exception stack trace ---

pkettlew

I have external tables and data sources successfully working in the pre-deployment scripts in SSMS 

However in the AzureDevops pipeline job , it fails consistently 

Have you got some guidelines of how to successfully create a build pipeline in Azure DevOps , with a pre-deployment script that has the statement

CREATE EXTERNAL DATA SOURCE

Version : 4.0.2012161408