0

I have to run multiple sql files in order. sql1 files uses temp tables to store the constraints. Once it is done with the insert from sql3, sql4 uses the constraints stored in a temp table by sql1 to restore the constraints. I want to do this using an automated powershell scripts. Basically I am looking for a way to execute multiple sql queries using powershell inside a single transaction

  • sql1= "D:\DB\00-StoreConstraints"
  • sql2= "D:\DB\01-DropConstraints"
  • sql3= "D:\DB\03-insert"
  • sql4= "D:\DB\05-RestoreConstraints"

This is how I am calling these sql files:

Invoke-Sqlcmd -inputfile "D:\DB\00-StoreConstraints.sql" -ServerInstance localhost  -$(DBUserName) BrightChoicesPortal_User -Password $(Password) -QueryTimeout 0

The error I get is below: enter image description here

techguy1029
  • 743
  • 10
  • 29
imba22
  • 651
  • 1
  • 13
  • 25
  • It looks like you have a `sequencial coupling` and using `global temp table`. With `mssql` clears global temp table `##` when the creator session is closed, In your case each `Invoke-SqlCmd` creates and close a new SQL Session. See (https://stackoverflow.com/questions/3887989/global-temporary-tables-in-sql-server). I would suggest to combine the files into 1 and run the full file in a single command. – Jonathan Larouche Oct 01 '19 at 01:30
  • You are absolutely right. I was hoping to find an easier way to do this in powershell. I guess there isn't any – imba22 Oct 01 '19 at 01:49
  • No other way if scripts are using global temp tables, you can probably change the scripts to use real table instead (longer work) but that will work – Jonathan Larouche Oct 01 '19 at 02:14

0 Answers0