Skip to content

Latest commit

 

History

History
117 lines (83 loc) · 6.28 KB

sqlps-utility.md

File metadata and controls

117 lines (83 loc) · 6.28 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
sqlps Utility
The sqlps utility starts a Windows PowerShell session with the SQL Server PowerShell provider and cmdlets loaded and registered.
markingmyname
maghan
03/14/2017
sql
tools-other
conceptual
sqlps utility
PowerShell [SQL Server], sqlps utility
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017

sqlps Utility

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW] The sqlps utility starts a Windows PowerShell session with the [!INCLUDEssNoVersion] PowerShell provider and cmdlets loaded and registered. You can enter PowerShell commands or scripts that use the [!INCLUDEssNoVersion] PowerShell components to work with instances of [!INCLUDEssNoVersion] and their objects.

Important

[!INCLUDEssNoteDepFutureAvoid] Use the sqlps PowerShell module instead. For more information about the sqlps module, see Import the SQLPS Module.

Syntax

  
sqlps   
[ [ [ -NoLogo ][ -NoExit ][ -NoProfile ]  
    [ -OutPutFormat { Text | XML } ] [ -InPutFormat { Text | XML } ]  
  ]  
  [ -Command { -  
             | script_block [ -args argument_array ]  
             | string [ command_parameters ]  
             }  
  ]  
]  
[ -? | -Help ]

Arguments

-NoLogo
Specifies that the sqlps utility hide the copyright banner when it starts.

-NoExit
Specifies that the sqlps utility continue running after the startup commands have completed.

-NoProfile
Specifies that the sqlps utility not load a user profile. User profiles record commonly used aliases, functions, and variables for use across PowerShell sessions.

-OutPutFormat { Text | XML }
Specifies that the sqlps utility output be formatted as either text strings (Text) or in a serialized CLIXML format (XML).

-InPutFormat { Text | XML }
Specifies that input to the sqlps utility is formatted as either text strings (Text) or in a serialized CLIXML format (XML).

-Command
Specifies the command for the sqlps utility to run. The sqlps utility runs the command and then exits, unless -NoExit is also specified. Do not specify any other switches after -Command, they will be read as command parameters.

-
-Command- specifies that the sqlps utility read the input from the standard input.

script_block [ -argsargument_array ]
Specifies a block of PowerShell commands to run, the block must be enclosed in braces: {}. Script_block can only be specified when the sqlps utility is called from either PowerShell or another sqlps utility session. The argument_array is an array of PowerShell variables containing the arguments for the PowerShell commands in the script_block.

string [ command_parameters ]
Specifies a string that contains the PowerShell commands to be run. Use the format "&{command}". The quotation marks indicate a string, and the invoke operator (&) causes the sqlps utility to run the command.

[ -? | -Help ]
Shows the syntax summary of the sqlps utility options.

Remarks

The sqlps utility starts the PowerShell environment (PowerShell.exe) and loads the [!INCLUDEssNoVersion] PowerShell module. The module, also named sqlps, loads and registers these [!INCLUDEssNoVersion] PowerShell snap-ins:

  • Microsoft.SqlServer.Management.PSProvider.dll

    Implements the [!INCLUDEssNoVersion] PowerShell provider and associated cmdlets such as Encode-SqlName and Decode-SqlName.

  • Microsoft.SqlServer.Management.PSSnapin.dll

    Implements the Invoke-Sqlcmd and Invoke-PolicyEvaluation cmdlets.

You can use the sqlps utility to do the following:

  • Interactively run PowerShell commands.

  • Run PowerShell script files.

  • Run [!INCLUDEssNoVersion] cmdlets.

  • Use the [!INCLUDEssNoVersion] provider paths to navigate through the hierarchy of [!INCLUDEssNoVersion] objects.

By default, the sqlps utility runs with the scripting execution policy set to Restricted. This prevents running any PowerShell scripts. You can use the Set-ExecutionPolicy cmdlet to enable running signed scripts, or any scripts. Only run scripts from trusted sources, and secure all input and output files by using the appropriate NTFS permissions. For more information about enabling PowerShell scripts, see Running Windows PowerShell Scripts.

The version of the sqlps utility in [!INCLUDEsql2008-md] and [!INCLUDEsql2008r2] was implemented as a Windows PowerShell 1.0 mini-shell. Mini-shells have certain restrictions, such as not allowing users to load snap-ins other than those loaded by the mini-shell. These restrictions do not apply to the [!INCLUDEssSQL11] and higher versions of the utility, which have been changed to use the sqlps module.

Examples

A. Run the sqlps utility in default, interactive mode without the copyright banner

sqlps -NoLogo

B. Run a SQL Server PowerShell script from the command prompt

sqlps -Command "&{.\MyFolder.MyScript.ps1}"

C. Run a SQL Server PowerShell script from the command prompt, and keep running after the script completes

sqlps -NoExit -Command "&{.\MyFolder.MyScript.ps1}"

See Also

Enable or Disable a Server Network Protocol
SQL Server PowerShell