CODEWITHSUNDEEP
powershellazure-synapse
MITI SHAH
MITI SHAH

Reputation: 35

Azure synapse deployment failing

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)}

Upvotes: 2

Views: 1161

Answers (1)

wBob
wBob

Reputation: 14389

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:

SSMS

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
}

Upvotes: 1

Related Questions