SQL Server and Windows PowerShell FAQs

Abstract

PowerShell offers advantages such as multiserver management and advanced .NET integration capabilities that aren't present in Windows shell scripting and T-SQL. Another way to do this is to start the PowerShell command prompt with the sqlps module loaded by opening SQL Server Management Studio (SSMS). right-clicking in Object Explorer, and selecting the Start PowerShell option from the context menu.

Windows PowerShell is quickly becoming Microsoft's management standard tor all of its server producís. However, PowerShell has never really taken ??G in the SQL Server community. There's no denying that it's more complex than Windows shell scripting, or T-SQL for thai matter. And who needs yet another thing Io learn? However. PowerShell offers advantages such as multiserver management and advanced .NET integration capabilities that aren't present in Windows shell scripting and T-SQL. Here are some of the common PowerShcii problems thai you're likely to run into and lheir solutions.

Q: Why don't SQL Server cmdlets run from the PowerShell command line!

A: Before you can use the SQL Server PowerShell provider, you need to import the sqlps module inio your Windows Server PowerShell environment. The easiest way to do so is lo just type sqlps at either the Windows command shell or the PowerShell command prompt. Another way to do this is to start the PowerShell command prompt with the sqlps module loaded by opening SQL Server Management Studio (SSMS). right-clicking in Object Explorer, and selecting the Start PowerShell option from the context menu.

Q: Why won't my PowerShell script runl My script ends with .ps I , but when I try to run it, I get an error.

A: The error File <filename> cannot he loaded became the execution of .scripts is disabled un this xy.stcni occurs because, by default, PowerShell is set so that it runs only interactive commands. This is a security measure to protect systems from running unauthorized scripts. PowerShell controls the execution of scripts with its Scripting Execution Policy. The default value of the Scripting Execution Policy is Restricted. However, you can change the Scripting Execution Policy selling to I In restricted, by typing lhe following command:

This allows all PowerShell scripts to be executed. For a somewhat more secure way. you can choose the RemoteSigncd value. RcmoteSigned allow.s any scripts that you have written to run Other scripts that have an associated digital signature from a trusted publisher. You set the RemoleSigned Execution Policy as follows:

Q: How can I use PowerShell to list databases?

A: One thing that makes PowerShetl difficult lo learn is that it provides multiple ways to do one thing. For example, you can use PowerShell to get a list of your server's databases by using PowerShell's path navigation. From the sqlps command prompt, enter the following:

A her natively, you can use ine SMO objects from PowerShell to list databases, as you can see in the following code:

Q: How do I run a SQL Server query using Powers hell I

A: Again, there are multiple ways to execute queries on SQL Server from PowerShell. After the sqlps module has been loaded, the most straightforward method is to use the Invoke-Sqlcmd cmdlet, as shown in the following example: