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

Now, change back to your SQL Server folder to get back to your final folder location:

Keep in mind, every time you do a clear screen “CLS”, it will return the folder location on the top of the screen:

Now, you have room to type your script and/or one-liner cmdlets.

Hope you find this information useful so you can enjoy working with both PowerShell and SQLPS.

Here’s some additional resource link on SQPS scripts and SQL Server PowerShell community extensions:

1. Sethu’s Blog: http://blogs.msdn.com/sethus/default.aspx

2. Chad Miller Blog: http://chadwickmiller.spaces.live.com/default.aspx

3. Chad Miller, SQL Server PowerShell Extensions: http://sqlpsx.codeplex.com/

Have fun!!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Executing SQLPS scripts from your SQLPS/PowerShell Window

  1. szumigalski says:

    VMware Certified Advanced Professional 6 (Desktop and Mobility Deployment) – The industry-recognized VCAP6-DTM Deploy certification validates that you know how to deploy and optimize VMware Horizon 6 (with View) environments. It demonstrates that you have the understanding and expertise vital to leverage best practices to provide a scalable and reliable Business Mobility platform for your company. Some of the topics include: Configuring and managing Horizon View components, configuring cloud pod archituecture, configuring Group Policy settings related to Horizon View, Configuring and optimizing desktop images for Horizon View & Mirage, Configuring and managing App Volumes AppStacks, Configuring desktop pools, Configuring and deploying ThinApp packaged applications, Configuring VMWare Identity Manager, etc.Szumigalski.com was created by Sebastian to spread his enthusiasm for PowerShell & VMWare. Sebastian is an IT specialist working in Singapore for longer than fifteen years who is at all times interested in brand new solutions to develop his technical competencies & knowledge. Since then, Sebastian has joined PowerShell User Group & VMWare VMug group, and has also been taking part in every one of the get-togethers held in Singapore. This weblog will show you exactly how Sebastian are able to automate some of his everyday works by making use of PowerShell. You’ll find study courses readily available for the VCAP6-DTM test, which are professionally created by Sebastian. Sebastian is qualified with VCAP6-DTM, and is proficient with virtualization & server maintenance from 4 years experience in automation. The need for VMWare knowledgable admins and technicians are ever-increasing in today’s competitive technology industry. Start to learn more details on PowerShell & VMWare at Szumigalski.com!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s