sql azure 语法_在Azure Data Studio中计划SQL笔记本

sql azure 语法

SQL Notebooks are an interactive way of creating documents, executing T-SQL queries along with Python and PowerShell codes. It is available in Azure Data Studio, a cross-platform client tool that works on both Windows and Linux operating systems.

SQL笔记本是创建文档，执行T-SQL查询以及Python和PowerShell代码的一种交互式方式。 Azure Data Studio中提供了该工具，Azure Data Studio是一个可在Windows和Linux操作系统上运行的跨平台客户端工具。

Before proceeding with this article, I would highly encourage you to go over the following articles to get familiar with SQL Notebooks.

在继续本文之前，强烈建议您阅读以下文章以熟悉SQL Notebook。

• SQL Notebooks introduction and overview SQL Notebook简介和概述
• A handy SQL Notebook for troubleshooting in Azure Data Studio 方便SQL笔记本，用于在Azure Data Studio中进行故障排除
• Learn Markdown language for SQL Notebooks in Azure Data Studio 在Azure Data Studio中学习用于SQL Notebook的Markdown语言

Azure Data Studio provides monthly enhancements, releases for bug fixes and new features. I am using the latest March 2020 version in this article. You can download the latest version of it from Microsoft docs.

Azure Data Studio每月提供增强功能，错误修复版本和新功能。 我正在使用本文中的最新2020年3月版本。 您可以从Microsoft docs下载它的最新版本。

Suppose we have a SQL Notebook for T-SQL scripts. Sometimes we want to run the notebook at a scheduled time similar to running scripts using a SQL Server agent job. In this article, we will explore how to schedule a notebook execution along with PowerShell command for it.

假设我们有一个用于T-SQL脚本SQL笔记本。 有时我们希望在计划的时间运行笔记本，类似于使用SQL Server代理作业运行脚本。 在本文中，我们将探讨如何安排笔记本执行以及PowerShell命令。

ADS中SQL代理扩展 (SQL Agent Extension in ADS)

We can increase functionality using the extension in the ADS. It is available in the market place and free to download and use. You can explore a few useful extensions using the following links.

我们可以使用ADS中的扩展程序来增加功能。 它在市场上有售，可以免费下载和使用。 您可以使用以下链接探索一些有用的扩展。

• Exploring the SandDance Visualizations extension in Azure Data Studio 在Azure Data Studio中探索SandDance可视化扩展
• SQL Server Schema Compare extension in Azure Data Studio Azure Data Studio中SQL Server架构比较扩展
• Database Administration Tool Extension for Windows in Azure Data Studio Azure Data Studio中Windows的数据库管理工具扩展
• Access PostgreSQL databases using the Azure Data Studio extension 使用Azure Data Studio扩展访问PostgreSQL数据库

SQL Server agent extension is available in the market place to manage the SQL Server agent job, configuration and troubleshooting failures, if any. It is a recommended extension by the ADS.

SQL Server代理扩展可在市场上获得，以管理SQL Server代理作业，配置和故障排除故障（如果有）。 这是ADS推荐的扩展。

Search for SQL Server Agent in the market place, as shown below.

在市场上搜索SQL Server代理 ，如下所示。

Click on Install, and it enables extension globally for all users.

单击安装，它将为所有用户全局启用扩展。

It opens the server dashboard along with a tab for SQL Agent. Click on it, and you get a view of all the current jobs on the connected SQL instance. Currently, I do not have a SQL agent job configured, so it shows only a default purge job.

它打开服务器仪表板以及“ SQL Agent”选项卡。 单击它，您可以查看连接SQL实例上的所有当前作业。 当前，我没有配置SQL代理作业，因此它仅显示默认的清除作业。

In the left-hand menu, you get a few options.

在左侧菜单中，您将获得一些选项。

• Jobs
  工作
• Notebooks
  笔记本电脑
• Alerts
  警报
• Operators
  经营者
• Proxies
  代理人

Click on the Notebooks. You get the following window for viewing and configuring notebook jobs.

单击笔记本。 您将获得以下窗口，用于查看和配置笔记本作业。

If you have configured a notebook job, it shows up here. I do not have any job as of now, so it does not show any details.

如果您配置了笔记本作业，它将显示在此处。 到目前为止，我没有任何工作，因此没有显示任何细节。

Before we move forward, let’s create a new SQL Notebook for this demo.

在继续进行之前，让我们为该演示创建一个新SQL Notebook。

• Specify a heading in the notebook and format it using the markdown language, as highlighted in the article referenced earlier
  在笔记本中指定标题，并使用markdown语言对其进行格式化，如先前所引用的文章中突出显示的那样
• Specify a T-SQL script. This script drops a SQL table and creates a new table using SELECT INTO statement
  指定一个T-SQL脚本。 此脚本删除SQL表并使用SELECT INTO语句创建新表

To save this notebook, go to File-> Save AS and specify a name and directory for the notebook.

要保存此笔记本，请转到“文件”->“保存AS”并为笔记本指定名称和目录。

You can also check the path of the notebook file in the header, as shown below.

您还可以在标题中检查笔记本文件的路径，如下所示。

Now, move back to SQL Agent in Azure Data Studio and navigate to Notebook job. Click on New Notebook Job.

现在，移回到Azure Data Studio中SQL Agent并导航到Notebook作业。 单击“新建笔记本作业” 。

It opens the pop-up for the configuration of a new notebook job.

它将打开用于配置新笔记本作业的弹出窗口。

It requires the following details.

它要求以下细节。

• Notebook Path: In this column, specify the path of the SQL notebook that we wish to schedule 笔记本路径：在此列中，指定我们希望安排SQL笔记本的路径
• Storage database: As you know, SQL agent job stores in the MSDB system database. For a notebook, we can choose the storage database of the job. The notebook is a JSON format file so we can store it in any database. It creates two tables in the storage database for storing template and execution results. We will look at this later once configured the notebook job 存储数据库：如您所知，SQL代理作业存储在MSDB系统数据库中。 对于笔记本，我们可以选择作业的存储数据库。 笔记本是JSON格式的文件，因此我们可以将其存储在任何数据库中。 它在存储数据库中创建两个表，用于存储模板和执行结果。 配置了笔记本作业后，我们将在后面介绍
• Execution database: It is the database content under which the query will execute. It can be the same database we specified in the storage database 执行数据库：它是查询将在其下执行的数据库内容。 它可以是我们在存储数据库中指定的数据库
• Job details: Specify a name for the SQL notebook job 作业详细信息：指定SQL Notebook作业的名称
• Owner: It is the job owner. By default, It takes the account by which we connected to SQL instance. This account should have permission to execute the query specified in the notebook 所有者：是工作所有者。 默认情况下，它采用我们用来连接SQL实例的帐户。 该帐户应具有执行笔记本中指定的查询的权限
• Schedule: We can pick a schedule that already exists for the SQL Server agent job. Right-now, it does not allow configuring a new schedule in Azure Data Studio. We need to switch to SQL Server Management Studio for it 日程安排：我们可以为SQL Server代理作业选择一个已经存在的日程安排。 现在，它不允许在Azure Data Studio中配置新计划。 我们需要为此切换到SQL Server Management Studio

Fill out the information for configuring a notebook job as per the notebook created earlier.

填写有关根据先前创建的笔记本配置笔记本作业的信息。

Click on the Pick Schedule, and it gives you a list of available schedules.

单击选择计划，它会为您提供可用计划的列表。

Suppose this schedule does not suit our requirements. Switch to SSMS and expand SQL Server Agent.

假设此时间表不符合我们的要求。 切换到SSMS并展开SQL Server代理。

Click on Manage Schedules, and it shows a similar list that we saw in ADS.

单击“管理计划”，它会显示一个类似于我们在ADS中看到的列表。

Click on New and configure a new job schedule. We can easily create a new schedule here. In the following screenshot, I configured a new schedule to run every 5 minutes.

单击新建，然后配置新的作业计划。 我们可以在这里轻松创建新时间表。 在下面的屏幕截图中，我配置了一个新的计划，每5分钟运行一次。

Now, go back to ADS, and you can see the newly created schedule. Select the schedule and click ok.

现在，返回到ADS，您可以看到新创建的时间表。 选择计划，然后单击确定。

You can see the job schedule in the notebook job configuration.

您可以在笔记本作业配置中查看作业计划。

Click ok, and you get the notebook job, as shown here.

单击确定，您将获得笔记本作业，如下所示。

You can also run it manually. Right-click on the job and run it manually.

您也可以手动运行它。 右键单击该作业，然后手动运行它。

You get the job started message, as shown below.

您将收到作业启动消息，如下所示。

You can also view this job under SQL Server Agent in the SSMS.

您也可以在SSMS中SQL Server代理下查看此作业。

使用PowerShell执行SQL Notebook查询 (Using PowerShell to execute SQL Notebook queries)

We can also use the PowerShell command for executing a notebook. It uses Invoke-SqlNotebook cmdlet for this. To use this command, we need to add PowerShell extension in Azure Data Studio. You can search for it in the market place and install it.

我们还可以使用PowerShell命令执行笔记本。 为此，它使用Invoke-SqlNotebook cmdlet。 若要使用此命令，我们需要在Azure Data Studio中添加PowerShell扩展。 您可以在市场上搜索并安装。

You should also have the SqlServer PowerShell module in your environment. In case the sqlserver module is not installed, you can use the following command in the administrative PowerShell prompt.

您的环境中还应该具有SqlServer PowerShell模块。 如果未安装sqlserver模块，则可以在管理PowerShell提示符中使用以下命令。

>install-module -Name sqlserver

Let’s use the Invoke-SqlNotebook for executing the notebook. Here we need to specify the following parameters.

让我们使用Invoke-SqlNotebook执行笔记本。 在这里，我们需要指定以下参数。

• ServerInstance: SQL instance details
  ServerInstance：SQL实例详细信息
• Database: Database in which query needs to be executed
  数据库：需要在其中执行查询的数据库
• InputFile: It is the path of the stored notebook file
  InputFile：这是存储的笔记本文件的路径

In the ADS, open the integrated PowerShell terminal and execute the following command.

在ADS中，打开集成的PowerShell终端并执行以下命令。

>Invoke-SqlNotebook -ServerInstance KASHISH\SQL2019GA -Database Master -Inputfile c:\sqlshack\SQLNoteBook\NotebookJob.ipynb

>调用SqlNotebook -ServerInstance KASHISH \ SQL2019GA -Database Master -Inputfile c：\ sqlshack \ SQLNoteBook \ NotebookJob.ipynb

You get the following output along with the last write time in the notebook.

您将获得以下输出以及笔记本中的最后写入时间。

If you go in the directory that holds a notebook, you see an additional file with suffix _out. It is the file that holds output after the query execution in the notebook.

如果进入保存笔记本的目录，则会看到带有后缀_out的其他文件。 它是在笔记本中执行查询后保存输出的文件。

Double-click on this _out file, and you can see query output along with total execution time.

双击此_out文件，您可以看到查询输出以及总执行时间。

Let’s open the notebook and add another script to check the result of the sys.databases system view.

让我们打开笔记本并添加另一个脚本来检查sys.databases系统视图的结果。

Now, if we execute the notebook using PowerShell, it should execute both the queries. Rerun the Invoke-SqlNotebook command specified above. I used argument -Force because the output file exists and you get an error message if it is already there. To overwrite the output file, use the -Force argument.

现在，如果我们使用PowerShell执行笔记本，则它应同时执行两个查询。 重新运行上面指定的Invoke-SqlNotebook命令。 我使用了-Force参数，因为输出文件存在，并且如果该文件已经存在，则会收到一条错误消息。 要覆盖输出文件，请使用-Force参数。

Open the output notebook. You see, it executed both T-SQL queries, and it includes the output of both in this file.

打开输出笔记本。 您会看到，它执行了两个T-SQL查询，并且在此文件中都包含了两者的输出。

结论 (Conclusion)

In this article, we explored executing SQL Notebook in Azure Data Studio from an SQL Agent job and PowerShell command. It is an interesting feature to use as an interactive notebook. You can explore this feature as per your requirement. I will be exploring more on notebooks and cover the same in my future articles. Stay tuned!

在本文中，我们探讨了通过SQL Agent作业和PowerShell命令在Azure Data Studio中执行SQL Notebook。 用作交互式笔记本是一个有趣的功能。 您可以根据需要探索此功能。 我将在笔记本上进行更多探索，并在以后的文章中进行介绍。 敬请关注！

翻译自: https://www.sqlshack.com/scheduling-sql-notebooks-in-azure-data-studio/

sql azure 语法