Executing SQLPS scripts from your SQLPS/PowerShell Window

I’ve seen many good SQLPS scripts on the internet but they asked you to copy/paste the code into the prompt. Then during my “PowerShell and SQL Server” presentation, I realized there’s a better way to stop doing copy/pasting code. I already got it saved to my scripts folder and the only thing I need to do is running from the SQLPS prompt.

So, how can we do this?

I – From SQL Server Management Studio

1. At the SQLPS prompt, Type the location of your Script Repository:

2. Now you can type the Script name and press enter. You can use the “tab expansion” to auto filled your script name.

II – Run “SQLPS” from within PowerShell Windows

1. Open Windows PowerShell Console prompt, then run SQLPS.exe or

2. Use the WindowsPowerShell user profile to include SQLPS.exe at the end of the script.

The following SQLPS script will create a table Employee in a NewDB database.

Keep in mind, that while doing using SQLPS.exe within Windows PowerShell, you do have two processes running.

So, next time you download your next SQLPS scripts, forget copy/paste into the SQLPS prompt, save the file, and run it from your scripts folder. I guarantee you will have a better user experience with SQLPS.

By the way, have you notice that at the end of the script, we end up with a long directory name.

Well, here’s a short PowerShell script to make it better:

################################################

## 1_chg_Prompt2.ps1

## Author: Max Trinidad, 11/08/2008

################################################

function prompt

{

if ($host.UI.RawUI.CursorPosition.Y -eq 0) { "< $pwd > `n`r" + "PS ["+$host.UI.RawUI.CursorPosition.Y+"] > "}

Else {"PS ["+$host.UI.RawUI.CursorPosition.Y+"] > "}

}

Now, because this is a PowerShell function, we need to running in a different way. We use the dot source method to execute functions in our PowerShell script:

####################################################

# NAME: DotSourcing01.ps1

# AUTHOR: Max Trinidad , PutItTogether

# DATE : 6/24/2009

# COMMENT: To help load function: 1_chgprompt2.ps1

####################################################

. .\1_chg_PSprompt2.ps1

cls