{PowerShell] Created a new NOSQLPS include and updated all SQL Server Agent articles with it

markingmyname · markingmyname · commit 10f3af508ff9 · 2021-02-24T14:58:02.000-06:00
diff --git a/docs/includes/sql-server-powershell-no-sqlps.md b/docs/includes/sql-server-powershell-no-sqlps.md
@@ -0,0 +1,17 @@
+---
+title: Using NOSQLPS for SQL Server Agent with PowerShell
+description: Message to explain to use the sqlserver powershell cmdlet instead of the sqlps cmdlet with SQL Server Agent
+ms.topic: include
+author: markingmyname
+ms.author: maghan
+ms.reviewer: drskwier
+---
+
+Starting with SQL Server 2019, you can disable SQLPS. On the first line of a job step of the type PowerShell you can add `#NOSQLPS`, which stops the SQL Agent from auto-loading the SQLPS module. Now your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.
+
+To use the [**SqlServer module**](https://www.powershellgallery.com/packages/Sqlserver/21.1.18235) in your SQL Agent Job step, you can place this code on the first two lines of your script.
+
+```powershell
+#NOSQLPS
+Import-Module -Name SqlServer
+```
diff --git a/docs/powershell/download-sql-server-ps-module.md b/docs/powershell/download-sql-server-ps-module.md
@@ -8,7 +8,7 @@ author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
 ms.custom: ""
-ms.date: 10/14/2020
+ms.date: 10/14/2020 
 ---
 
 # Install the SQL Server PowerShell module
diff --git a/docs/powershell/encode-and-decode-sql-server-identifiers.md b/docs/powershell/encode-and-decode-sql-server-identifiers.md
@@ -4,7 +4,6 @@ description: Some characters that can appear in SQL Server-delimited identifiers
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: bb9fe0d3-e432-42d3-b324-64dc908b544a
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/escape-sql-server-identifiers.md b/docs/powershell/escape-sql-server-identifiers.md
@@ -4,7 +4,6 @@ description: Some characters that can appear in SQL Server-delimited identifiers
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 8a73e945-daa6-4e5d-93da-10f000f1f3a2
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/how-to-enable-tcp-sqlps.md b/docs/powershell/how-to-enable-tcp-sqlps.md
@@ -4,7 +4,6 @@ description: Learn how to enable TCP protocols using SQLPS
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 89b70725-bbe7-4ffe-a27d-2a40005a97e7
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot
diff --git a/docs/powershell/load-the-smo-assemblies-in-windows-powershell.md b/docs/powershell/load-the-smo-assemblies-in-windows-powershell.md
@@ -4,7 +4,6 @@ description: Learn how to load the SQL Server Management Object (SMO) assemblies
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 8ca42b69-da5a-47f4-9085-34e443f0e389
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/manage-authentication-in-database-engine-powershell.md b/docs/powershell/manage-authentication-in-database-engine-powershell.md
@@ -5,7 +5,6 @@ titleSuffix: SQL Server on Linux
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: ab9212a6-6628-4f08-a38c-d3156e05ddea
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/manage-tab-completion-sql-server-powershell.md b/docs/powershell/manage-tab-completion-sql-server-powershell.md
@@ -4,7 +4,6 @@ description: Learn how to control Windows PowerShell tab completion by making pr
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 6296848a-890f-4ad3-8d9f-92ed6a79aa00
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/navigate-sql-server-powershell-paths.md b/docs/powershell/navigate-sql-server-powershell-paths.md
@@ -4,7 +4,6 @@ description: Learn how to use PowerShell cmdlets to navigate the path structures
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: d68aca48-d161-45ed-9f4f-14122ed30218
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
@@ -14,7 +13,7 @@ ms.date: 10/14/2020
 
 # Navigate SQL Server PowerShell Paths
 
-[!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW ](../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
+[!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW](../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
 
 The [!INCLUDE[ssDE](../includes/ssde-md.md)] PowerShell provider exposes the set of objects in an instance of SQL Server in a structure similar to a file path. You can use Windows PowerShell cmdlets to navigate the provider path, and create custom drives to shorten the path you have to type.  
 
diff --git a/docs/powershell/query-expressions-and-uniform-resource-names.md b/docs/powershell/query-expressions-and-uniform-resource-names.md
@@ -1,5 +1,5 @@
 ---
-title: "Query Expressions and Uniform Resource Names | Microsoft Docs"
+title: Query Expressions and Uniform Resource Names
 description: Learn about Query Expressions, which enumerate one or more objects in an object model hierarchy, and about Uniform Resource names (URNs), which uniquely identify a single object.
 ms.prod: sql
 ms.technology: sql-server-powershell
@@ -8,7 +8,6 @@ helpviewer_keywords:
   - "query expressions"
   - "unique resource names"
   - "URN"
-ms.assetid: e0d30dbe-7daf-47eb-8412-1b96792b6fb9
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
@@ -18,7 +17,7 @@ ms.date: 10/14/2020
 
 # Query Expressions and Uniform Resource Names
 
-[!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW ](../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
+[!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW](../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
 
 The [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Management Object (SMO) models and [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] PowerShell snap-ins use two types of expression strings that are similar to XPath expressions. Query expressions are strings that specify a set of criteria used to enumerate one or more objects in an object model hierarchy. A Uniform Resource Name (URN) is a specific type of query expression string that uniquely identifies a single object.  
 
diff --git a/docs/powershell/run-windows-powershell-from-sql-server-management-studio.md b/docs/powershell/run-windows-powershell-from-sql-server-management-studio.md
@@ -1,26 +1,27 @@
 ---
-title: "Run Windows PowerShell from SQL Server Management Studio | Microsoft Docs"
+title: Run Windows PowerShell from SQL Server Management Studio
 description: Learn how to start a Windows PowerShell session from Object Explorer in SQL Server Management Studio, with the path preset to the location of your choice of objects.
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 1f841825-da1f-4062-9a81-3cdbab03845b
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
 ms.custom: ""
-ms.date: "03/14/2017"
+ms.date: 03/14/2017
 ---
 
 # Run Windows PowerShell from SQL Server Management Studio
 
 [!INCLUDE[SQL Server Azure SQL Database Synapse Analytics PDW ](../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw.md)]
 
-You can start Windows PowerShell sessions from **Object Explorer** in [!INCLUDE[ssManStudioFull](../includes/ssmanstudiofull-md.md)]. [!INCLUDE[ssManStudio](../includes/ssmanstudio-md.md)] launches Windows PowerShell, loads the **SqlServer** module, and sets the path context to the associated node in the **Object Explorer** tree.  
+You can start Windows PowerShell sessions from **Object Explorer** in SQL Server Management Studio (SSMS). SSMS launches Windows PowerShell, loads the **SqlServer** module, and sets the path context to the associated node in the **Object Explorer** tree.
 
 [!INCLUDE [sql-server-powershell-version](../includes/sql-server-powershell-version.md)]
 
-When you specify running PowerShell for an object in **Object Explorer**, [!INCLUDE[ssManStudioFull](../includes/ssmanstudiofull-md.md)] starts a Windows PowerShell session in which the [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] PowerShell snap-ins have been loaded and registered. The path for the session is preset to the location of the object you right-clicked in Object Explorer. For example, if you right-click the [!INCLUDE[ssSampleDBobject](../includes/sssampledbobject-md.md)] database object in Object Explorer and select **Start PowerShell**, the Windows PowerShell path is set to the following:  
+When you specify running PowerShell for an object in **Object Explorer**, SQL Server Management Studio starts a Windows PowerShell session in which the SQL Server PowerShell snap-ins have been loaded and registered. The path for the session is preset to the location of the object you right-clicked in Object Explorer.
+
+For example, if you right-click the AdventureWorks database object in Object Explorer and select **Start PowerShell**, the Windows PowerShell path is set as seen below:
 
 ```powershell
 SQLSERVER:\SQL\MyComputer\MyInstance\Databases\AdventureWorks2012>  
@@ -38,8 +39,8 @@ SQLSERVER:\SQL\MyComputer\MyInstance\Databases\AdventureWorks2012>
 
 ## Permissions
 
-When opened from [!INCLUDE[ssManStudio](../includes/ssmanstudio-md.md)], PowerShell does not run with Administrator privileges, which may prevent some activities such as calls to WMI.  
-  
+When opened from SQL Server Management Studio, PowerShell does not run with Administrator privileges, which may prevent some activities such as calls to WMI.
+
 ## See Also
 
 - [SQL Server PowerShell](sql-server-powershell.md)
diff --git a/docs/powershell/run-windows-powershell-steps-in-sql-server-agent.md b/docs/powershell/run-windows-powershell-steps-in-sql-server-agent.md
@@ -1,75 +1,77 @@
 ---
-title: "Run Windows PowerShell Steps in SQL Server Agent"
+title: Run Windows PowerShell Steps in SQL Server Agent
 description: Learn how to run Windows PowerShell steps in a SQL Server Agent job. 
-ms.custom: seo-lt-2019
-ms.date: "03/16/2017"
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: f25f7549-c9b3-4618-85f2-c9a08adbe0e3
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
+ms.custom: seo-lt-2019
+ms.date: 03/16/2017
 ---
+
 # Run Windows PowerShell Steps in SQL Server Agent
 
 [!INCLUDE[appliesto-ss-xxxx-asdw-pdw-md](../includes/appliesto-ss-xxxx-asdw-pdw-md.md)]
 
-Use SQL Server Agent to run SQL Server PowerShell scripts at scheduled times.  
-  
-**To run PowerShell from SQL Server Agent, using:**  [PowerShell Job Step](#PShellJob), [Command Prompt Job Step](#CmdExecJob)  
-  
-[!INCLUDE [sql-server-powershell-version](../includes/sql-server-powershell-version.md)]
+Use SQL Server Agent to run SQL Server PowerShell scripts at scheduled times.
+
+[!INCLUDE[sql-server-powershell-version](../includes/sql-server-powershell-version.md)]
 
-There are several types of [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent job steps. Each type is associated with a subsystem that implements a specific environment, such as a replication agent or command prompt environment. You can code Windows PowerShell scripts, and then use [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent to include the scripts in jobs that run at scheduled times or in response to [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] events. Windows PowerShell scripts can be run using either a command prompt job step or a PowerShell job step.  
+[!INCLUDE[sql-server-powershell-no-sqlps](../includes/sql-server-powershell-no-sqlps.md)]
 
-- Use a PowerShell job step to have the [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent subsystem run the **sqlps** utility, which launches PowerShell and imports the **sqlps** module.
+## To run PowerShell from SQL Server Agent
+
+There are several types of SQL Server Agent job steps. Each type is associated with a subsystem that implements a specific environment, such as a replication agent or command prompt environment. You can code Windows PowerShell scripts, and then use SQL Server Agent to include the scripts in jobs that run at scheduled times or in response to SQL Server events. Windows PowerShell scripts can be run using either a command prompt job step or a PowerShell job step.
+
+- Use a PowerShell job step to have the SQL Server Agent subsystem run the **sqlps** utility, which launches PowerShell and imports the **sqlps** module. If you're running SQL Server 2019 or later, we recommend using the **[SqlServer](sql-server-powershell.md#sql-server-agent)** module in your SQL Agent Job step.
 
 - Use a command prompt job step to run PowerShell.exe, and specify a script that imports the **sqlps** module.
 
 ### <a name="LimitationsRestrictions"></a> Caution about memory consumption
 
-Each [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent job step that runs PowerShell with the **sqlps** module launches a process, which consumes approximately **20 MB** of memory. Running large numbers of concurrent Windows PowerShell job steps can adversely impact performance.  
-
-[!INCLUDE[Freshness](../includes/paragraph-content/fresh-note-steps-feedback.md)]
-
-##  <a name="PShellJob"></a> Create a PowerShell Job Step  
- **To create a PowerShell job step**  
-  
-1.  Expand **SQL Server Agent**, create a new job or right-click an existing job, and then click **Properties**. For more information about creating a job, see [Creating Jobs](../ssms/agent/create-jobs.md).  
-  
-2.  In the **Job Properties** dialog, click the **Steps** page, and then click **New**.  
-  
-3.  In the **New Job Step** dialog, type a job **Step name**.  
-  
-4.  In the **Type** list, click **PowerShell**.  
-  
-5.  In the **Run as** list, select the proxy account with the credentials that the job will use.  
-  
-6.  In the **Command** box, enter the PowerShell script syntax that will be executed for the job step. Alternately, click **Open** and select a file containing the script syntax.  
-  
-7.  Click the **Advanced** page to set the following job step options: what action to take if the job step succeeds or fails, how many times [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent should try to execute the job step, and how often retry attempts should be made.  
-  
-##  <a name="CmdExecJob"></a> Create a Command Prompt Job Step  
- **To create a CmdExec job step**  
-  
-1.  Expand **SQL Server Agent**, create a new job or right-click an existing job, and then click **Properties**. For more information about creating a job, see [Creating Jobs](../ssms/agent/create-jobs.md).  
-  
-2.  In the **Job Properties** dialog, click the **Steps** page, and then click **New**.  
-  
-3.  In the **New Job Step** dialog, type a job **Step name**.  
-  
-4.  In the **Type** list, choose **Operating system (CmdExec)**.  
-  
-5.  In **Run as** list, select the proxy account with the credentials that the job will use. By default, CmdExec job steps run under the context of the SQL Server Agent service account.  
-  
-6.  In the **Process exit code of a successful command** box, enter a value from 0 to 999999.  
-  
-7.  In the **Command** box, enter powershell.exe with parameters specifying the PowerShell script to be run.  
-  
-8.  Click the **Advanced** page to set job step options, such as: what action to take if the job step succeeds or fails, how many times [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent should try to execute the job step, and the file where [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] Agent can write the job step output. Only members of the **sysadmin** fixed server role can write job step output to an operating system file.  
-  
-## See Also  
- [SQL Server PowerShell](sql-server-powershell.md)  
-  
-  
+Each SQL Server Agent job step that runs PowerShell with the **sqlps** module launches a process, which consumes approximately **20 MB** of memory. Running large numbers of concurrent Windows PowerShell job steps can adversely impact performance.
+
+## <a name="PShellJob"></a> Create a PowerShell Job Step
+
+### To create a PowerShell job step
+
+1. Expand **SQL Server Agent**, create a new job or right-click an existing job, and then select **Properties**. For more information about creating a job, see [Creating Jobs](../ssms/agent/create-jobs.md).
+
+2. In the **Job Properties** dialog, select the **Steps** page, and then select **New**.
+
+3. In the **New Job Step** dialog, type a job **Step name**.
+
+4. In the **Type** list, select **PowerShell**.
+
+5. In the **Run as** list, select the proxy account with the credentials that the job will use.
+
+6. In the **Command** box, enter the PowerShell script syntax that will be executed for the job step. Alternately, select **Open** and select a file containing the script syntax.
+
+7. Select the **Advanced** page to set the following job step options: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and how often retry attempts should be made.
+
+## <a name="CmdExecJob"></a> Create a Command Prompt Job Step
+
+### To create a CmdExec job step
+
+1. Expand **SQL Server Agent**, create a new job or right-click an existing job, and then select **Properties**. For more information about creating a job, see [Creating Jobs](../ssms/agent/create-jobs.md).
+
+2. In the **Job Properties** dialog, select the **Steps** page, and then select **New**.
+
+3. In the **New Job Step** dialog, type a job **Step name**.
+
+4. In the **Type** list, choose **Operating system (CmdExec)**.
+
+5. In **Run as** list, select the proxy account with the credentials that the job will use. By default, CmdExec job steps run under the context of the SQL Server Agent service account.
+
+6. In the **Process exit code of a successful command** box, enter a value from 0 to 999999.
+
+7. In the **Command** box, enter powershell.exe with parameters specifying the PowerShell script to be run.
+
+8. select the **Advanced** page to set job step options, such as: what action to take if the job step succeeds or fails, how many times SQL Server Agent should try to execute the job step, and the file where SQL Server Agent can write the job step output. Only members of the **sysadmin** fixed server role can write job step output to an operating system file.
+
+## See Also
+
+- [SQL Server PowerShell](sql-server-powershell.md)
+- [SQL Server Agent with PowerShell](sql-server-powershell.md#sql-server-agent)
diff --git a/docs/powershell/specify-instances-in-the-sql-server-powershell-provider.md b/docs/powershell/specify-instances-in-the-sql-server-powershell-provider.md
@@ -6,7 +6,6 @@ ms.date: "03/14/2017"
 ms.prod: sql
 ms.technology: sql-server-powershell
 ms.topic: conceptual
-ms.assetid: 9373de68-fd43-45f2-b9a6-149c96610aeb
 author: markingmyname
 ms.author: maghan
 ms.reviewer: matteot, drskwier
diff --git a/docs/powershell/sql-server-identifiers-in-powershell.md b/docs/powershell/sql-server-identifiers-in-powershell.md
diff --git a/docs/powershell/sql-server-powershell-provider.md b/docs/powershell/sql-server-powershell-provider.md
diff --git a/docs/powershell/work-with-sql-server-powershell-paths.md b/docs/powershell/work-with-sql-server-powershell-paths.md
