Invoke-SqlNotebook

Reference

Module:: SqlServer

Executes a SQL Notebook file (.ipynb) and outputs the materialized notebook.

Syntax

Invoke-SqlNotebook
      [-ServerInstance <Object>]
      [-Database <Object>]
      [-Username <Object>]
      [-Password <Object>]
      [-Credential <PSCredential>]
      [-InputFile <Object>]
      [-InputObject <Object>]
      [-OutputFile <Object>]
      [-AccessToken <String>]
      [-Force]
      [<CommonParameters>]

Invoke-SqlNotebook
      [-ConnectionString <Object>]
      [-InputFile <Object>]
      [-InputObject <Object>]
      [-OutputFile <Object>]
      [-Force]
      [<CommonParameters>]

Invoke-SqlNotebook
      -InputFile <Object>
      [-OutputFile <Object>]
      [-Force]
      [<CommonParameters>]

Invoke-SqlNotebook
      -InputObject <Object>
      [-OutputFile <Object>]
      [-Force]
      [<CommonParameters>]

Description

The Invoke-SqlNotebook cmdlet executes a SQL Notebook file (.ipynb) and outputs the materialized notebook.

The Notebook will be executed on the ServerInstance and Database provided.

When the cmdlet is run, the resulting Notebook file will be in the location the user defines or in the same directory of the input notebook file.

The cmdlet outfile may be omitted: if that's the case, it will be created with the input file name and the _out appended to the file.

Examples

Example 1: Execute a Notebook against the local server (default instance)

PS C:\> Invoke-SqlNotebook -ServerInstance localhost -Database TestNotebook -InputFile C:\notebook.ipynb

   Directory: C:\

Mode           LastWriteTime         Length Name
----           -------------         ------ ----
-a----         8/1/2019  1:00 PM     4656   notebook_out.ipynb

This command runs the notebook against the default instance of SQL Server running on the the machine where the cmdlet is executed from. By default, since no -OutputFile was passed in, the materialized notebook is saved on disk with the same name as the InputFile with a _out as a suffix to the filename (notebook.ipynb -> notebook_out.ipynb)

Example 2: Execute Notebook on the local server (defaut instance) and saves the materialized Notebook to a specified file

PS C:\> Invoke-SqlNotebook -ServerInstance localhost -Database TestNotebook -InputFile C:\notebook.ipynb -OutputFile C:\new_notebook.ipynb

   Directory: C:\

Mode           LastWriteTime         Length Name
----           -------------         ------ ----
-a----         8/1/2019  1:00 PM     44656  new_notebook.ipynb

This command runs the notebook against the default instance of SQL Server running on the the machine where the cmdlet is executed from. The executed notebook is then saved to the file specified with the -OutputFile parameter.

Example 3: Execute Notebook using -ConnectionString parameter

PS C:\> Invoke-Sqlnotebook -ConnectionString 'Server=Localhost;Database=TestNotebook;Trusted_Connection=True;' -InputFile C:\notebook.ipynb

   Directory: C:\

Mode           LastWriteTime         Length Name
----           -------------         ------ ----
-a----         8/1/2019  1:00 PM     44656  notebook_out.ipynb

This is the same as Example 1, only that the connection to the server is specified via the -ConnectionString parameter.

Example 4: Execute Notebook Against All Registered Servers

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' |
WHERE { $_.Mode -ne 'd'} |            
foreach {
    $datetime = Get-Date -Format yyyyMMddhhmm;
    Get-SqlInstance -ServerInstance $_.Name |
    foreach {
            Invoke-SqlNotebook -ServerInstance $_.Name -Database master -InputFile '$home\Documents\SQL Server Management Studio\BPCheck.ipynb' `
            -OutputFile "BPCheck_output_$($_.NetName)_$($datetime).ipynb";
            }
        }

Use Registered Servers or Central Management Server to run Invoke-SqlNotebook against multiple servers. In this example, the NetName property of the SQL Server instance will be included in the name of the output file; the materialized notebook is going to be time-stamped with year-month-day-hour-minute.

Example 5: Execute Notebook, Open Results in Azure Data Studio

Install the PowerShell extension from the Azure Data Studio marketplace. Use the PowerShell Integrated Console in Azure Data Studio to run Invoke-SqlNotebook and use Open-EditorFile to open the results of the SQL Notebook directly in Azure Data Studio.

Invoke-SqlNotebook -ServerInstance ServerA -Database master -InputFile "$home\Documents\SQL Server Management Studio\BPCheck.ipynb" |
Open-EditorFile

Note: The Open-EditorFile command is only available in the PowerShell Integrated Console.

Example 6: Execute Notebook, Connect to Azure SQL Databases (or Managed Instance) using a Service Principal

Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0

### Obtain the Access Token interactively.
### Note: help for Invoke-Sqlcmd has examples on other ways to acquire the token.
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-SqlNotebook -ServerInstance myserver.database.windows.net -Database master -AccessToken $access_token`
                   -InputFile MyNotebook.ipynb

Parameters

-AccessToken

A valid access token to be used to authenticate to SQL Server, in alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity (see references at the bottom of this page)

Do not specify UserName, Password, or Credential when using this parameter.

Type:	String
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-ConnectionString

Specifies a connection string to connect to the server.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Credential

The PSCredential object whose Username and Password fields will be used to connect to the SQL instance.

Type:	PSCredential
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Database

This cmdlet connects to this database in the instance that is specified in the ServerInstance parameter.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Force

By default, when the cmdlet writes the materialized notebook to a file, a check is performed to prevent the user from accidentally overwriting an existing file. Use -Force to bypass this check and allow the cmdlet to overwrite the existing file.

Type:	SwitchParameter
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-InputFile

Specifies a Notebook File (.ipynb) that will be executed through the cmdlet.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-InputObject

Specifies the Notebook as a Json string that will be used as the input notebook.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-OutputFile

Specifies the desired output Notebook file for which the executed Notebook will be saved.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Password

Specifies the password for the SQL Server Authentication login ID that was specified in the Username parameter.

Passwords are case-sensitive. When possible, use Windows Authentication, or consider using the -Credential parameter instead.

If you specify the Password parameter followed by your password, the password is visible to anyone who can see your monitor.

If you code Password followed by your password in a .ps1 script, anyone reading the script file will see your password.

Assign the appropriate NTFS permissions to the file to prevent other users from being able to read the file.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-ServerInstance

Specifies a character string or SQL Server Management Objects (SMO) object that specifies the name of an instance of the Database Engine.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Username

Specifies the login ID for making a SQL Server Authentication connection to an instance of the Database Engine.

The password must be specified through the Password parameter.

If Username and Password are not specified, this cmdlet attempts a Windows Authentication connection using the Windows account running the Windows PowerShell session. When possible, use Windows Authentication.

Type:	Object
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

System.Object

System.Management.Automation.PSCredential

Outputs