PowerShell Invoke-Sqlcmd switches into sqlps session

Question

I am writing a script in PowerShell ISE and I am using Invoke-Sqlcmd. After the command is executed the Powershell session switches into sqlps session (PS SQLSERVER:>) and I can't execute script for the second time. I have to quit PowerShell ISE and start it again.

So my question is: how to switch back from sqlps to regular ps or how to prevent Invoke-Sqlcmd from switching session.

Invoke-Sqlcmd -ServerInstance $server -Database master -Username $user -Password $password   -InputFile $file `
   -ErrorAction Stop -OutputSqlErrors $true -Variable $variable

This doesn't work:

Push-Location
Invoke-Sqlcmd -ServerInstance $server -Database master -Username $user -Password $password   -InputFile $file `
-ErrorAction Stop -OutputSqlErrors $true -Variable $variable
Pop-Location

alroc · Accepted Answer · 2016-10-20T20:32:04.303

39

The sqlps module's behavior is to leave you in the psdrive that it creates. I'm fairly certain that people have asked Microsoft to fix this, as it's very annoying and disruptive.

The automatic importing of modules introduced by PowerShell 3.0 makes this even more annoying, because you may not even realize that you're importing the module until after the fact.

When I use sqlps, I explicitly import it so that I can control my working directory as follows:

push-location
import-module sqlps -disablenamechecking
pop-location

This returns you to your previous directory after the module is loaded.

Very late edit: With the advent of SQL Server Management Studio 2016 we have a new PowerShell module, sqlserver, which supersedes sqlps and resolves this problem.

edited Oct 20 '16 at 20:32

answered Jun 11 '14 at 15:30

alroc

27,574
6
51
97

I am not importing the module explicitly. I am just using Invoke-Sqlcmd. I tried with push-location and it doesn't work: – scar80 Jun 12 '14 at 06:55
Now It worked. The solution is to use snippet provided by @alroc to import sqlps module. And after that just use Invole-Sqlcmd without any push-location. – scar80 Jun 12 '14 at 07:09
Whether you do it implicitly (auto import) or explicitly, you're still importing that module, which causes that behavior. – alroc Jun 12 '14 at 10:04
How to prevent the issue when the `SQLPS` module is loaded by a `Require` statement? – Akaizoku Oct 03 '18 at 09:40
@Florian fix the script/module that's doing it. `sqlps` was superseded several years ago by `sqlserver` and any code still attempting to use it should be considered outdated and either revised or replaced. – alroc Oct 04 '18 at 02:56

score 4 · Answer 2 · answered Nov 10 '16 at 17:07

4

A simple cd 'directory of your choice' will put you back to the previous directory you were in.

PS SQLSERVER:\> cd D:

Result:

PS D:\>

answered Nov 10 '16 at 17:07

TaylorJ

41
3

score 1 · Answer 3 · edited Mar 20 '17 at 10:18

1

There was a similar question over at SuperUser today: https://superuser.com/questions/767427/stuck-in-powershell-sqlserver

You should be able to just go back to C:

edited Mar 20 '17 at 10:18

Community

1
1

answered Jun 11 '14 at 15:31

Tim Ferrill

1,648
1
12
15

Yes I can type c: to go back to ps. But I would like to automatically use push-location if it is possible. – scar80 Jun 12 '14 at 07:06

PowerShell Invoke-Sqlcmd switches into sqlps session

3 Answers3

Linked