SQLCompare.exe terminated with the exit code 126

godfrey_d

Hello,
I have created a SQL Change Automation pipeline in Azure Dev Ops, today when I added an external data source script (find below) and the pipeline kicked-off, the pipeline failed with the below error. The error is encountered at the 'Build' stage of the pipeline.
If I execute the same script on my Azure database it runs fine.
please advise.

Error I get when pipeline is runs and fails is :
Validating project 'scripts folder 'D:\a\1\s\Prod''.

##[error]RedGate.Versioning.Automation.Shared.Errors.TerminatingException: SQLCompare.exe terminated with the exit code 126: Failed due to a SQL Server error.

SQLCompare.exe produced this error output:

Error: Comparison of 'Scripts.Prod' and 'mysqlserver.database.windows.net.mydatbase' failed: Errors occurred whilst parsing file \a\1\s\Prod\2_Schema-Model\2_External Resources\abcd.sql '' at line 1, column 21

Comparison of 'Scripts.Prod' and 'mysqlserver.database.windows.net.mydatbase' failed: Errors occurred whilst parsing file \a\1\s\Prod\2_Schema-Model\2_External Resources\abcd.sql

'' at line 1, column 21

-----External data source script-----

CREATE EXTERNAL DATA SOURCE [ABCD]

    WITH (

    TYPE = RDBMS,

    LOCATION = N'mysqlserver.database.windows.net',

    DATABASE_NAME = N'mydatbase',

    CREDENTIAL = [XYZCredentials]

    );

regardsm
Godfrey

Kendra_Little

Hi Godfrey,

Apologies that you hit this issue. SQL Compare does not currently support external data sources / external tables. We have a user voice item here that it would be great if you upvoted for tracking. I have also noted that you hit this problem and would like this feature to work in our relevant Slack channels as well for consideration for the future roadmap. 

I've set up the following workaround using SQL Source Control + SCA and it worked, but I haven't tested it yet in an SCA "only"/ migrations first approach. It should also work there, however:

1) Filter out external tables in Visual Studio using a Filter.scpf file (documentation)
2) Create a re-runnable pre-deployment script that checks for the existence of the external data sources and tables and creates them as needed. A rudimentary code sample is here,

I believe that you will also need to specify the  Filter.scpf on your build step as well.

Hope this helps! Thank you for letting us know about it here, finding out that more people want this functionality helps us a lot.

Kendra

godfrey_d

Hi Kendra, Thanks for getting back. I have upvoted on user voice. Not ideal to have this problem but work around should work, just means there are more steps/moving parts to the process around development/build. Nice work posting it on github.
thanks.