Errors when running Sync-DatabaseSchema

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
Tagged:

Best Answer

  • Dan BDan B Posts: 287 Gold 5
    Accepted Answer
    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.
    Kind regards,
    Dan Bainbridge
    Product Support Engineer | Redgate Software
Sign In or Register to comment.