Errors when running Sync-DatabaseSchema

PeterDaniels

I am using SQL Change Automation and attempting to sync a DB from the project.  I have been following: https://documentation.red-gate.com/sca3/tutorials/getting-started-with-the-sql-change-automation-powershell-components

Here's my PS code:
*******************************************************************

# Validate the SQL Change Automation project

$project = "C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC" #SCAPOC\SCAPOC.sqlproj" # The SQL Change Automation project to validate, test and sync

$validatedProject = Invoke-DatabaseBuild $project

# Run tSQLt tests

#$testResults = Invoke-DatabaseTests $validatedProject

#Export-DatabaseTestResults $testResults -OutputFile "C:\Users\...TestResults\scripts.junit.xml"

# Sync to dev intergation

$deploymentTargetConnection = New-DatabaseConnection -ServerInstance "localhost" -Database "SCAPOC_DEV_INT" # Update this to use the blank database created earlier

Test-DatabaseConnection $deploymentTargetConnection

Sync-DatabaseSchema -Source $validatedProject -Target $deploymentTargetConnection

**************************************************
And here is the error:
*****************************************************************************************************8

Invoke-DatabaseBuild, SQL Change Automation 3.0.7.2861, Copyright © Red Gate Software Ltd 2014-2018

WARNING: No 'RedGateDatabaseInfo.xml' file located at the root of the scripts folder 'C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC'. Some featur

es, such as static data, may not work as expected.

Validating project 'scripts folder 'C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC''.

Invoke-DatabaseBuild : 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.SCAPOC' and 'np:\\.\pipe\LOCALDB#7D024C59\tsql\query.dlmautomation_9d2dc3b5-2457-43c7-849b-1cf739c044b3' failed: Errors 

occurred whilst parsing file C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC\SCAPOC\bin\Debug\SCAPOC.sql '' at line 32, column 2 '' at line 47, 

column 2

Comparison of 'Scripts.SCAPOC' and 'np:\\.\pipe\LOCALDB#7D024C59\tsql\query.dlmautomation_9d2dc3b5-2457-43c7-849b-1cf739c044b3' failed: Errors 

occurred whilst parsing file C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC\SCAPOC\bin\Debug\SCAPOC.sql

'' at line 32, column 2

'' at line 47, column 2

RedGate.Legacy.CommandLine.FatalExecutionException

   at RedGate.SQLToolsCommandLine.CommandProcessor.FatalExceptionHelper(String message, Exception ex)

   at RedGate.SQLToolsCommandLine.CommandProcessor.RunComparison()

   at RedGate.SQLToolsCommandLine.CommandProcessor.Run()

Caused by:

  Errors occurred whilst parsing file C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC\SCAPOC\bin\Debug\SCAPOC.sql

'' at line 32, column 2

'' at line 47, column 2

  RedGate.SQLCompare.Engine.ReadFromFolder.ErrorsOccurredDuringScriptFileParsingException

     at RedGate.SQLCompare.Engine.ReadFromFolder.DatabaseFromParseResultPopulator.AddToModel(FileInfo file, ParseResult parseResult, 

ReadScriptAnalysis analysis, LazyFileContents fullFileText, Boolean throwOnFileParseFailed, List`1 parserMessages)

     at RedGate.SQLCompare.Engine.ReadFromFolder.DatabaseFromParseResultPopulator.Populate(ParseResult parseResult)

     at RedGate.SQLCompare.Engine.ReadFromFolder.DatabaseFromScriptFilesPopulator.AddFromFiles(List`1 sortedFiles)

     at RedGate.SQLCompare.Engine.ReadFromFolder.DatabaseFromScriptFilesPopulator.Populate(IEnumerable`1 filesToUse)

     at RedGate.SQLCompare.Engine.Model.DatabaseObjects.DatabaseFromFolderPopulator.DatabaseFromFolderPopulator.PopulateFromFolder(Database database, 

DirectoryInfo path, ScriptDatabaseInformation dbinfo, Options options, IEnumerable`1 filesToUse, IReadFromFolderCache cache, CancellationToken 

cancellationToken)

     at RedGate.SQLCompare.Engine.Model.DatabaseObjects.DatabaseFromFolderPopulator.DatabaseFromFolderPopulator.Populate(String path, 

CancellationToken cancellationToken)

     at RedGate.SQLCompare.Engine.Utils.GetTimeToRun[T](Func`1 action)

     at RedGate.SQLCompare.Engine.Database.Register(String path, ScriptDatabaseInformation dbinfo, Nullable`1 optionsParam, IEnumerable`1 filesToUse, 

IReadFromFolderCache cache, IDatabaseFromFolderPopulator databasePopulator)

     at RedGate.SQLCompare.Common.CompareEngineExecutor.DoRegisterDatabaseForCompare(Database database, FolderDataSource dataSource)

     at RedGate.SQLCompare.CommandLine.CommandLineCompareEngineExecutor.DoRegisterDatabaseForCompare(Database database, FolderDataSource dataSource)

     at RedGate.Legacy.ProgressTasks.ProgressTask.ExecuteSubTasks()

     at RedGate.Legacy.ProgressTasks.SimpleProgressTaskExecutor.DoTasks()

At C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC\MyDeploy\ValidateProjectAndDeployToDevInt.ps1:9 char:21

+ $validatedProject = Invoke-DatabaseBuild $project

+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Invoke-DatabaseBuild], TerminatingException

    + FullyQualifiedErrorId : SQLServerError,RedGate.Versioning.Automation.PowerShell.Commands.InvokeDatabaseBuildCommand
***************************************************************************

I have tried setting $project to the *.sln file folder, the *.sqlproj folder and the actual *.sqlproj file.  Oddly, only when I set it to the project files itself do I get no warning about the "No 'RedGateDatabaseInfo.xml'", but when I do that, it gives me a different error:
"Sync-DatabaseSchema : A invalid type was passed to Sync-DatabaseSchema for the Source parameter: 'SCAPOC.sqlproj''

TIA,
-Peter

Dan B

Hi PeterDaniels.

Thanks for posting on the Redgate forums.

I believe that you initially had this setup correctly. You can disregard this message 
He can disregard this error:

WARNING: No 'RedGateDatabaseInfo.xml' file located at the root of the scripts folder 'C:\Users\peter\Documents\IncommSVN_PPD\SCAPOC\SCAPOC'. Some featur
es, such as static data, may not work as expected.

It will always show when you use SCA projects and it is not relevant in this case.

When using an SCA project you should point this to the .sqlproj file.