Azure synapse deployment failing-CodePudding

I am trying to deploy SQL files to an Azure Synapse Analytics dedicated SQL pools using PowerShell script in an Azure Devops pipeline.

I have a folder with SQL files and after defining array of files I am trying to run foreach loop for array and trying to Invoke-Sqlcmd to deploy files but first SQL file get deployed (object is created) and then I get error:

Msg 104309, Level 16, State 1, Line 1 There are no batches in the input script.

Below is my piece of code:

$files='$(Build.SourcesDirectory)\folder1\'

foreach ($s in $files)
{
Invoke-sqlcmd -ServerInstance $(server) -Database $(db) -InputFile $s -Credential $(cred)}

CodePudding user response：

Azure Synapse Analytics dedicated SQL pools scripts are sensitive to empty batches, eg this script would generate the same error:

Msg 104309, Level 16, State 1, Line 1 There are no batches in the input script.

-- xx
GO

However the truth is you get the same error in Synapse Studio and SQL Server Management Studio (SSMS), so I suggest you run through your scripts and use the Parse button (Ctrl F5) in SSMS, which parses the script but does not execute it. This will help you track down your error:

In summary don't have batches that don't do anything.

I was able to get a simple local example added by including dir and using the FullName method to get the full path:

$files = dir "D:\temp\Powershell\*.sql"

foreach ($s in $files) {
    #$s.Name
    Invoke-Sqlcmd -ServerInstance '.\sql2019x' -Database 'tempdb' -InputFile $s.FullName
}