CODEWITHSUNDEEP
sql-serverpowershellsqlcmdpowershell-cmdlet
Rob Berry
Rob Berry

Reputation: 315

Invoke-Sqlcmd leaves script at 'PS SQLSERVER: \>' prompt

I have a script that queries a MS SQL database as below:

$query = "SELECT Path FROM repoLocations WHERE hostId = '$($hostId)'"
$results = Invoke-Sqlcmd -Query $query -ServerInstance $dbHost -Database $dbName -Username $dbUser -Password $dbPass -ErrorAction Stop

This works absolutely fine with one slight issue. The first time the script is run, or indeed any script that uses Invoke-Sqlcmd, in a newly opened PowerShell console session, there is a delay of a few seconds, presumably where the module loads before completing. The SQL query does work and always returns the correct result but on its first run, the prompt will be left at:

PS SQLSERVER: \>

Which then causes subsequent file operations to fail.

I have a workaround/solution, which is to add the following line of code immediately following the Invoke-Sqlcmd, which enables the script and prompt to work as expected everytime:

$query = "SELECT Path FROM repoLocations WHERE hostId = '$($hostId)'"
$results = Invoke-Sqlcmd -Query $query -ServerInstance $dbHost -Database $dbName -Username $dbUser -Password $dbPass -ErrorAction Stop
Set-Location -Path $PSScriptRoot

As long as I used the same PowerShell session, the issue won't happen again and the pause before the SQL command is not present.

There are a number of other questions of similar topic that contain 'workarounds', but I'm curious as to whether there is a better fix, possibly to preload the SQL cmdlets at the start of the script before proceding? If not, I will need to use Set-Location at every Invoke-Sqlcmd that could possibly be used by the script.

Upvotes: 0

Views: 2786

Answers (3)

Angela
Angela

Reputation: 11

I know this is an old thread, but I just hit the same issue today and found this question: the way I resolved was to install/import the SqlServer module, rather than using SQLPS and setting location on every DB query. That way $PWD remains unchanged after invoke-sqlcmd (PowerShell 5.1, SQL2017, Windows Server 2012)

Upvotes: 1

Trey Mack
Trey Mack

Reputation: 4705

possibly to preload the SQL cmdlets at the start of the script before proceding?

That's my solution.

    try { Push-Location \ # importing sqlps switches you to the sql psdrive...
        Import-Module SQLPS
    } finally { Pop-Location }

The upside of this implementation is the script will Pop-Location you back to wherever you were.

Upvotes: 2

Vivek Kumar Singh
Vivek Kumar Singh

Reputation: 3350

I had faced similar issue earlier. You are right. The path of the script changes toPS SQLSERVER: \> after an Invoke-Sqlcmd is executed. In my case, I had other operation too in the script which required the use of cmdlets like Get-ChildItem and Get-Content. As expected, these commands didn't work because the path was not a Windows Directory path.

In such cases, I used push-location after Invoke-Sqlcmd like this

$query = "SELECT Path FROM repoLocations WHERE hostId = '$($hostId)'"
$results = Invoke-Sqlcmd -Query $query -ServerInstance $dbHost -Database $dbName -Username $dbUser -Password $dbPass -ErrorAction Stop
Push-Location -Path $env:USERPROFILE

Upvotes: 4

Related Questions