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

Improve this question
3
  • 1
    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 (stackoverflow.com/questions/3887989/…). I would suggest to combine the files into 1 and run the full file in a single command. Commented Oct 1, 2019 at 1:30
  • You are absolutely right. I was hoping to find an easier way to do this in powershell. I guess there isn't any Commented Oct 1, 2019 at 1: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 Commented Oct 1, 2019 at 2:14
Related questions
4
PowerShell Function multiple SQL queries output as CSV
1
Powershell script to run list of sql files
1
1 Powershell Script 2 SQL Tables
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.