2

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)}
MITI SHAH
  • 33
  • 3
  • Are you missing a dash before InputFile? – wBob Feb 17 '22 at 17:39
  • Nah.. that was typo here.. without which even one file won't get deployed :) – MITI SHAH Feb 17 '22 at 17:47
  • Just taking a step back, has this ever worked? I'm looking at the PowerShell and thinking a lot of stuff would have to be different for this to work. – wBob Feb 17 '22 at 17:49
  • Also, how are you controlling the order? Using a naming convention? – wBob Feb 17 '22 at 18:00
  • No it didn't work.. tried lot many changes in powershell but it never worked.. only one file gets deployed everytime and then error... Not controlling the order ..since all are SQL files – MITI SHAH Feb 17 '22 at 18:04
  • Did you look at my example with `dir`? I'm wondering why you don't have to do this. Try removing the `Invoke-Sqlcmd` from your file, and just print out `$s`. – wBob Feb 17 '22 at 18:23
  • Yes Write-host "x=" $s.Name.. gives all the files name. I have tried $s.FullName as well.. but same error... I have also tried with get-child item for files inside folder.... Did you try to synapse db? – MITI SHAH Feb 17 '22 at 18:36
  • I hope Microsoft Azure Synapse folks are listening ... Please fix synapse to setup a table copy from source to destination like Dedicated SQL pool ... this OP is resorting to running Azure DevOps pipeline to replace the current terrible Azure Synapse pipeline web UI with its very poor errors and arcane domain specific syntax for example in trying to avoid duplicate data during a table copy ... for others listening synapse is NOT production ready here in 20230323 – Scott Stensland Mar 23 '23 at 16:52

1 Answers1

1

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
}
wBob
  • 13,710
  • 3
  • 20
  • 37
  • I have 5 SQL scripts in folder 3 for table creation and 2 for SP creation 1st script for table creation gets deployed and object is created in DB but then I get this error. I have rearranged the order scripts.. so whichever is the 1st SQL file in folder gets deployed properly.. but then this error comes. Is there something with invoke-sql cmd?? Deploying multiple SQL files to Azure synapse DB at a time??.. – MITI SHAH Feb 17 '22 at 15:07
  • So go through all the scripts in SSMS. Parse each one. Do you get this error for any of those scripts? – wBob Feb 17 '22 at 15:10
  • I have gone through all the scripts in SSMS.. parsed each one... No error..... That is the catch.. why does 1st file the in folder gets deployed.. and then i get the error.. – MITI SHAH Feb 17 '22 at 15:49
  • Things inside a batch (ie `GO`) will execute, and then it will go on to the next one. You don't get any kind of overarching transaction. – wBob Feb 17 '22 at 16:37
  • So what changes needs to be done??.. l need to deploy all SQL files inside the folder to Azure synapse DB using powershell script.. – MITI SHAH Feb 17 '22 at 17:11
  • You say you've checked all the scripts, but I would try and narrow it down. Remove all scripts, add them back in one by one and test each time. Do you need to a .sql filter to your path? Are there any other files in the directory? – wBob Feb 17 '22 at 17:23
  • Checked all the scripts.. one at time gets deployed.. strange.. but when I have all together in folder.. only 1st one gets deployed and then the error... Which is also strange...no other files in directory..only SQL files – MITI SHAH Feb 17 '22 at 17:34
  • 2
    Thanks .. it was GO statement at the end of table creation script causing error ... It works fine in SSMS but with invoke-sqlcmd it doesn't. – MITI SHAH Feb 18 '22 at 18:20