I am trying to schedule a Python notebook by creating a job Using an SQL Server agent but when I try to execute this it gives me the following error. 12/21/2023 10:05:00,Duetto_Job,Error,1,SUN-SQL02\BI_SIHOT,Duetto_Job,Exec-Notebook,,Executed as user: NT Service\SQLAgent$BI_SIHOT. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 6 in a PowerShell script. The corresponding line is '$sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647 '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server) The system cannot find the file specified ' A job step received an error at line 6 in a PowerShell script. The corresponding line is '$sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647 '. Correct the script and reschedule the job. The error information returned by PowerShell is: ' '. Process Exit Code -1. The step failed.,00:00:17,0,0,,,,0 But when I run the same notebook manually, it works fine. Help me please

Hi Muhammad Fawad You have created a Python Notebook which runs successfully as an individual notebook but you are trying to run the Notebook in a SQL Agent Job as a Notebook Job like this https://blog.robsewell.com/blog/running-jupyter-notebooks-as-agent-jobs/ The error that you are receiving is from the Agent Job Step 'Exec Notebook' which you can find by editing the Agent Notebook Job in ADS and then the Job Step. My feeling is that it is not correctly locating the instance. click in the job step and press edit The problem is that in this code there is no value set for the Invoke-SqlCmd cmdlet so it is using the hostname which is obviously no good when you have a named instance. I get login failures on my default instance for the named instance agent service account when I try to run a Notebook Agent Job on a named instance. A quick solution is to add this to the PowerShell code step at the top $SqlInstance = '{0}\{1}' -f "$(ESCAPE_SQUOTE(MACH))", "$(ESCAPE_SQUOTE(INST))" $PSDefaultParameterValues = @{ "Invoke-SqlCmd:ServerInstance" = $SqlInstance } What is this doing? It is creating a variable named SqlInstance that is made up of the SQL Agent Job Tokens for the machine and the instance name Then it is setting that variable as the default value for the ServerInstance paramater of the Invoke-SqlCmd cmdlet for this session only This means that all of the times that the Invoke-SqlCmd cmdlet is called it will use the correct value. You should probably make this a default for you to add to any Notebook Agent job This resolved this error for me Let me know how you get on

Scheduling Python Notebook using SQL Server Agent

Answer accepted by question author

Rob Sewell 86 Reputation points MVP

2024-01-02T12:46:41.7266667+00:00
Hi Muhammad Fawad

You have created a Python Notebook which runs successfully as an individual notebook but you are trying to run the Notebook in a SQL Agent Job as a Notebook Job like this https://blog.robsewell.com/blog/running-jupyter-notebooks-as-agent-jobs/

The error that you are receiving is from the Agent Job Step 'Exec Notebook' which you can find by editing the Agent Notebook Job in ADS and then the Job Step. My feeling is that it is not correctly locating the instance.

click in the job step and press edit

The problem is that in this code there is no value set for the Invoke-SqlCmd cmdlet so it is using the hostname which is obviously no good when you have a named instance. I get login failures on my default instance for the named instance agent service account when I try to run a Notebook Agent Job on a named instance.

A quick solution is to add this to the PowerShell code step at the top

$SqlInstance = '{0}\{1}' -f "$(ESCAPE_SQUOTE(MACH))", "$(ESCAPE_SQUOTE(INST))" $PSDefaultParameterValues = @{ "Invoke-SqlCmd:ServerInstance" = $SqlInstance }

What is this doing?

It is creating a variable named SqlInstance that is made up of the SQL Agent Job Tokens for the machine and the instance name

Then it is setting that variable as the default value for the ServerInstance paramater of the Invoke-SqlCmd cmdlet for this session only

This means that all of the times that the Invoke-SqlCmd cmdlet is called it will use the correct value.

You should probably make this a default for you to add to any Notebook Agent job

This resolved this error for me

Let me know how you get on
Please sign in to rate this answer.

1 person found this answer helpful.
Muhammad Fawad 60 Reputation points

2024-01-03T04:16:36.53+00:00

$SqlInstance = '{0}\{1}' -f "$(ESCAPE_SQUOTE(MACH))", "$(ESCAPE_SQUOTE(INST))" $PSDefaultParameterValues = @{ "Invoke-SqlCmd:ServerInstance" = $SqlInstance } $TargetDatabase = "SSISDB" $JobId = "$(ESCAPE_SQUOTE(JOBID))" $StartTime = "$(ESCAPE_SQUOTE(STRTTM))" $StartDate = "$(ESCAPE_SQUOTE(STRTDT))" $JSONTable = "select * from notebooks.nb_template where job_id = $JobId" $sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647 -Server "SUN-SQL02\BI_SIHOT" $FirstNotebookError = "" function ParseTableToNotebookOutput { param ( [System.Data.DataTable] $DataTable, [int] $CellExecutionCount ) $TableHTMLText = "<table>" $TableSchemaFeilds = @() $TableHTMLText += "<tr>" foreach ($ColumnName in $DataTable.Columns) { $TableSchemaFeilds += @(@{name = $ColumnName.toString() }) $TableHTMLText += "<th>" + $ColumnName.toString() + "</th>" } $TableHTMLText += "</tr>" $TableSchema = @{ } $TableSchema["fields"] = $TableSchemaFeilds $TableDataRows = @() foreach ($Row in $DataTable) { $TableDataRow = [ordered]@{ } $TableHTMLText += "<tr>" $i = 0 foreach ($Cell in $Row.ItemArray) { $TableDataRow[$i.ToString()] = $Cell.toString() $TableHTMLText += "<td>" + $Cell.toString() + "</td>" $i++ } $TableHTMLText += "</tr>" $TableDataRows += $TableDataRow } $TableDataResource = @{ } $TableDataResource["schema"] = $TableSchema $TableDataResource["data"] = $TableDataRows $TableData = @{ } $TableData["application/vnd.dataresource+json"] = $TableDataResource $TableData["text/html"] = $TableHTMLText $TableOutput = @{ } $TableOutput["output_type"] = "execute_result" $TableOutput["data"] = $TableData $TableOutput["metadata"] = @{ } $TableOutput["execution_count"] = $CellExecutionCount return $TableOutput } function ParseQueryErrorToNotebookOutput { param ( $QueryError ) $ErrorString = "Msg " + $QueryError.Exception.InnerException.Number + ", Level " + $QueryError.Exception.InnerException.Class + ", State " + $QueryError.Exception.InnerException.State + ", Line " + $QueryError.Exception.InnerException.LineNumber + "`r`n" + $QueryError.Exception.Message $ErrorOutput = @{ } $ErrorOutput["output_type"] = "error" $ErrorOutput["traceback"] = @() $ErrorOutput["evalue"] = $ErrorString return $ErrorOutput } function ParseStringToNotebookOutput { param ( [System.String] $InputString ) $StringOutputData = @{ } $StringOutputData["text/html"] = $InputString $StringOutput = @{ } $StringOutput["output_type"] = "display_data" $StringOutput["data"] = $StringOutputData $StringOutput["metadata"] = @{ } return $StringOutput } $TemplateNotebook = $sqlResult.notebook $executeDatabase = $sqlResult.execute_database try { $TemplateNotebookJsonObject = ConvertFrom-Json -InputObject $TemplateNotebook } catch { Throw $_.Exception } $DatabaseQueryHashTable = @{ } $DatabaseQueryHashTable["Verbose"] = $true $DatabaseQueryHashTable["ErrorVariable"] = "SqlQueryError" $DatabaseQueryHashTable["OutputAs"] = "DataTables" $DatabaseQueryHashTable["Database"] = $executeDatabase $CellExcecutionCount = 1 foreach ($NotebookCell in $TemplateNotebookJsonObject.cells) { $NotebookCellOutputs = @() if ($NotebookCell.cell_type -eq "markdown" -or $NotebookCell.cell_type -eq "raw" -or $NotebookCell.source -eq "") { continue; } switch($NotebookCell.source.getType()){ System.Object[] { $DatabaseQueryHashTable["Query"] = ($NotebookCell.source -join "`r`n" | Out-String) } String { $DatabaseQueryHashTable["Query"] = $NotebookCell.source } } $SqlQueryExecutionTime = Measure-Command { $SqlQueryResult = @(Invoke-Sqlcmd @DatabaseQueryHashTable 4>&1) } $NotebookCell.execution_count = $CellExcecutionCount++ $NotebookCellTableOutputs = @() if ($SqlQueryResult) { foreach ($SQLQueryResultElement in $SqlQueryResult) { switch ($SQLQueryResultElement.getType()) { System.Management.Automation.VerboseRecord { $NotebookCellOutputs += ParseStringToNotebookOutput($SQLQueryResultElement.Message) } System.Data.DataTable { $NotebookCellTableOutputs += ParseTableToNotebookOutput $SQLQueryResultElement $CellExcecutionCount } Default { } } } } if ($SqlQueryError) { if(!$FirstNotebookError){ $FirstNotebookError = $SqlQueryError.Exception.Message.Replace("'", "''") } $NotebookCellOutputs += ParseQueryErrorToNotebookOutput($SqlQueryError) } if ($SqlQueryExecutionTime) { $NotebookCellExcutionTimeString = "Total execution time: " + $SqlQueryExecutionTime.ToString("hh\:mm\:ss\.fff") $NotebookCellOutputs += ParseStringToNotebookOutput($NotebookCellExcutionTimeString) } $NotebookCellOutputs += $NotebookCellTableOutputs $NotebookCell.outputs = $NotebookCellOutputs } $result = ($TemplateNotebookJsonObject | ConvertTo-Json -Depth 100) Write-Output $result $result = $result.Replace("'","''") $InsertQuery = "INSERT INTO notebooks.nb_materialized (job_id, run_time, run_date, notebook, notebook_error) VALUES ($JobID, '$StartTime', '$StartDate','$result','$FirstNotebookError')" $SqlResult = Invoke-Sqlcmd -Query $InsertQuery -Database $TargetDatabase $InsertQuery = "UPDATE notebooks.nb_template SET last_run_notebook_error = '$FirstNotebookError' where job_id = $JobID" $sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647 -Server "SUN-SQL02\BI_SIHOT"

Thanks for the response. I have updated the code and added the code block you have shared. But when I parse the code it says, "The Command Failed". Can you check where am I going wrong? I know the thread has gone way longer than expected.

Rob Sewell 86 Reputation points MVP

2024-01-03T06:37:04.0533333+00:00

As I only edited the job step in ADS, I did not need to parse the code. The code will run successfully inside the Agent job now as you can see from my screenshot

Muhammad Fawad 60 Reputation points

2024-01-03T06:46:20.1766667+00:00

Can you confirm if I've placed the code in correctly?

Rob Sewell 86 Reputation points MVP

2024-01-03T06:51:22.7966667+00:00

On my phone it sure looks like it. Nothing else has been changed from default?

Muhammad Fawad 60 Reputation points

2024-01-03T07:12:31.6633333+00:00

Nope.

Muhammad Fawad 60 Reputation points

2024-01-03T08:43:40.12+00:00

How can I make this a default, instead of writing it all the time?

Rob Sewell 86 Reputation points MVP

2024-01-03T08:58:09.8466667+00:00

At present you will have to alter the step for named instances each time they are created unfortunately.

I have raised an issue

https://github.com/microsoft/sqltoolsservice/issues/2305

and a PR

https://github.com/microsoft/sqltoolsservice/pull/2306

to fix the issue in the GitHub repository. Keep your eye on those and vote them up.

Muhammad Fawad 60 Reputation points

2024-01-03T10:39:11.1866667+00:00

The job succeeds, but now I'm getting Syntax errors. Stating that my syntax isn't correct tho everything is fine. I have selected Python3 as a Kernel in my notebook. Is it even possible to run jupyter notebooks having python code using SQL Server agent?

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-01-03T19:19:16.17+00:00

Hard to say, since we don't know what sort of errors you get. Does it seem like it is botching the script entirely? Or could it be that you have different versions of Python on your machine, and Agent sees an older version and you are using new syntax?

Muhammad Fawad 60 Reputation points

2024-01-04T06:03:57.71+00:00

I think is this because PowerShell can't recognize Python Code? As I am running Python as a job in powershell

Rob Sewell 86 Reputation points MVP

2024-01-04T08:40:57.5066667+00:00

I owe you an apology @Muhammad , I was so focused on the SQL error message and the named instance that I missed that you were running a Python notebook (and yes, it is right in the title !)

The Notebook Jobs will only work for SQL Notebooks as the code parses the code cells in the Notebook and runs them as SQL queries against the instance.

Your options are either to convert your python code to T-SQL and schedule the notebook that way or to schedule the python notebook in a different way and if you wish you can parse the results into the table using the PowerShell code above. It depends on your need.

Again, I apologise for the wasting of your time.

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-01-05T20:02:06.79+00:00

I can only echo what Rob said. You were really in for some bad luck here, Muhammad. You tried to use a feature on named instance, but the feature did not support it. Which would have alright if it had told you up front, but it just pretended that everything was fine. And then you try to use the feature for a Python notebook, when it only supports SQL notebooks. Again, it should have stopped you. (Rob, maybe you should file an issue about this as well.)

On top of all, when you asked the question, you were only met by a bunch of clowns with little experience of notebooks. No, Rob is not a clown, but he does not usually hang out here, but I asked him to take a look at this thread, since I know that he knows about notebooks.

Muhammad Fawad 60 Reputation points

2024-01-07T11:28:48.4166667+00:00

Thank you for sharing the responses. I appreciate the time and effort that @Rob Sewell and @Erland Sommarskog took to provide their insights and suggestions. I apologize if there was any confusion on my part that led to the issue. I will consider the options provided and choose the one that best suits my needs. If there is nothing else to discuss, should we close the thread? Thank you again for your help and I hope you have a great day!

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-01-07T12:00:51.9366667+00:00

You can close the thread by accepting Rob's first post as the answer. If you would prefer to accept Rob's final comment as the answer, I can convert that to a comment. However, I feel that Rob's first post still has a merit as it answers to how run a notebook job on a named instance, even if does not work for a non-SQL notebook.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.