1

I have SQL scripts that I run using Invoke-SqlCmd. At the top of each file, I have a USE statement. I noticed that after an error occurs, the user is sent back to their default database (usually master). The problem is that it continues running the script; it just dumps everything under the master schema. Then I have to go through and remove a bunch of tables, views, etc.

Right now I am just relying on -AbortOnError to quit immediately. However, most of the time, my errors are actually not a big deal. Running the script multiple times would actually resolve the issues, so long as everything ran under the same database. The long-term solution is to fix my SQL scripts, obviously. I was wondering if there was a way to prevent Invoke-SqlCmd from switching to a different database in the meantime.

Example:

# Use Windows Authentication
Invoke-SqlCmd -InputFile "test.sql"
Travis Parks
  • 8,435
  • 12
  • 52
  • 85
  • 1. Could you post the code? 2. Is your issue that the SQL provider path is changing in Powershell? – briantist Sep 18 '14 at 17:47
  • What happens if you set the database using the `-Database` parameter of `Invoke-Sqlcmd`? – Bryan Sep 18 '14 at 17:56
  • @BryanEargle - Part of the problem is that my scripts are creating the database. It doesn't exist when the scripts start running. – Travis Parks Sep 18 '14 at 17:58
  • You need to separate out the Create Database "command" from anything else that Alter's the Database..... To create the database, you actually are using the master db, and should use the "-database Master" for this command. And from that point on, specify the -Database parameter (as previously mentioned). – granadaCoder Sep 18 '14 at 18:26
  • @granadaCoder - I will give using the `-Database` parameter a try. – Travis Parks Sep 18 '14 at 18:31
  • 1
    "Running the script multiple times would actually resolve the issues"...You should write your scripts to run in an idempotent manner. Aka, no errors and the same results...no matter how many times you call it. writing code that needs "multiple runs to clear up issues".... would fail in my code-reviews.....just saying' http://stackoverflow.com/questions/1077412/what-is-an-idempotent-operation – granadaCoder Sep 18 '14 at 18:44
  • @granadaCoder I totally agree with you. Instead of building the scripts in a nice, clean way, someone just has SSMS spit out a script. It tries to drop things that don't even exist yet. It tries to create things before their dependencies. I brought it up to the team and we will be doing some major clean up. As part of automating database deployments (what I'm working on), I want to come up with a consistent migration process. I think a clean set of script is step 1. – Travis Parks Sep 18 '14 at 20:08
  • "someone just has SSMS spit out a script"............Gaaaaaaaaaaaaaaaaa! I've been there dude. Good luck. http://odetocode.com/blogs/scott/archive/2008/01/30/three-rules-for-database-work.aspx – granadaCoder Sep 18 '14 at 20:22

1 Answers1

0

I learned that I did not have a GO immediately following the USE statement. Without it, the USE statement is in the same batch as all the statements following it up to the next GO. The USE wouldn't apply to the other batches.

Travis Parks
  • 8,435
  • 12
  • 52
  • 85