Scheduling Python Notebook using SQL Server Agent

Question

Scheduling Python Notebook using SQL Server Agent

Muhammad Fawad 60

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

Accepted answer

5 additional answers

Your answer

Answer 1

Rob Sewell 86 MVP

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.

User's image

click in the job step and press edit

User's image

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.

User's image

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

User's image

Let me know how you get on

Muhammad Fawad 60

$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 121.4K 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 121.4K 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 121.4K 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.

Answer 2

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.

This error means that the SQL Server instance you are trying to connect to was not reached. It could be because the instance is not running, you have fat-fingered the name, there is a firewall etc.

Judging from the error message, you don't seem to have specified a server at all. The name of the service account suggests that this is a named instance, and if you are not specifying the server name, this means default instance on the local server.

But when I run the same notebook manually, it works fine. Help me please

Maybe you are specifying a correct server name then?

Muhammad Fawad 60 Reputation points

2023-12-22T04:07:03.19+00:00

I checked everything; the credentials are all correct. I think there can be a firewall issue. How can I check and what can I do about it?

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 4

Olaf Helper 47,436

12/21/2023 10:05:00,Duetto_Job,Error,1,SUN-SQL02\BI_SIHO

So you have a named instance "BI_SIHO" running?

You do not pass the server/instance name to the "Invoke-Sqlcmd" command using the "-ServerInstance" parameter. How could the cmd guess to which SQL Server you want to connect to? Impossible and so it fails.

See Invoke-Sqlcmd

Muhammad Fawad 60 Reputation points

2023-12-22T06:35:57.5833333+00:00

I am not much aware of this Indepth technicalities. But thanks, I'll check the link you've shared.
Olaf Helper 47,436 Reputation points

2023-12-22T09:35:01.2+00:00

Simply add this parameter:

-ServerInstance "SUN-SQL02\BI_SIHO"
Muhammad Fawad 60 Reputation points

2023-12-22T10:12:39.5433333+00:00

Where should I add this? in my notebook? or where?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-22T16:08:29.3266667+00:00

Maybe you can share the notebook? Or rather a simplified notebook which does not reveal any of your trade secrets.

I've played with notebooks occasionally, but I don't recall where you set up the connection information. Olaf suggested where you should add it, but he worked from the error message, and maybe that is a command invoked behind the scenes, using information you have added elsewhere.
Muhammad Fawad 60 Reputation points

2023-12-26T05:40:42.3566667+00:00

Variables

driver = "{ODBC Driver 17 for SQL Server}"

server = "xxxxxxxx"

database = "xxxxx"

username = "xxxxx"

password = "xxxxxxxxxxx"

conn_str = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"

folder_path = "C:\Users\MuhammadFawad\OneDrive - INTERNATIONAL OFFICE PRODUCTS PVT LTD\Duetto"

file_list = os.listdir(folder_path)

excel_files = [file for file in file_list if file.endswith('.xls') or file.endswith('.xlsx')]

destination_table = "Duetto_LY"

archive = "C:\Users\MuhammadFawad\OneDrive - INTERNATIONAL OFFICE PRODUCTS PVT LTD\Duetto\Archive"

conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxxxxxxxxxxx;DATABASE=xxxxx;UID=xxxx;PWD=xxxxxx'

params = urllib.parse.quote_plus(conn_str)

engine = create_engine(f"mssql+pyodbc:///?odbc_connect={params}")

error_log_table_name = 'ErrorLog'

This is what values I am using.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-26T09:37:19.69+00:00

But that looks like Python. The error messages comes from PowerShell when running invoke-sqlcmd.
Muhammad Fawad 60 Reputation points

2023-12-26T10:24:35.73+00:00

I'm trying to run this using SQL Server Agent, but IDK why it triggers power shell
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-26T10:58:25.5133333+00:00

Can you show a screen shot of the definition of the job step?
Muhammad Fawad 60 Reputation points

2023-12-26T11:15:53.6533333+00:00

job desc.PNG

This is what I am doing on Azure Data Studio
Muhammad Fawad 60 Reputation points

2023-12-26T11:21:08.7166667+00:00

This is on SQL Server Agent
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-26T11:25:34.41+00:00

Right-click DuettoETL and select Properties, then go the job step to see what's in there. I'm not really up to speed with notebooks to try this myself to see what it results in.
Olaf Helper 47,436 Reputation points

2023-12-27T07:37:03.0633333+00:00

You have to add it to the Invoke-SqlCmd line

$sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase
Muhammad Fawad 60 Reputation points

2023-12-28T06:13:30.0933333+00:00

Can you specify where should I add this?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-28T22:10:02.0766667+00:00

Can you specify where should I add this?

You still haven't shown us the job step. Do you expect us to have crystal balls.
Muhammad Fawad 60 Reputation points

2023-12-29T04:34:14.14+00:00

Sorry! here you go.
Muhammad Fawad 60 Reputation points

2023-12-29T04:34:57.48+00:00

Sorry! Here you go.
Muhammad Fawad 60 Reputation points

2023-12-29T04:47:34.17+00:00

Also, this.

Muhammad Fawad 60

$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
$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 $InsertQuery -Database $TargetDatabase

Muhammad Fawad 60 Reputation points

2023-12-29T04:52:50.9466667+00:00

The above is the code in my Exec-Notebook

Answer 5

Erland Sommarskog 121.4K MVP Volunteer Moderator

Now that you have seen the PowerShell code, the fix should be quite apparent. Modify this line:

$sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647

To read:

$sqlResult = Invoke-Sqlcmd -Query $JSONTable -Database $TargetDatabase -MaxCharLength 2147483647 -Server "SUN-SQL02\\BI_SIHOT"

I'm a little uncertain whether the backslash should be doubled. If the above fails, try without doubling.

However, I can't say that I feel that this is satisfactory. There should be a way to specify the server name when you create the jobs. Or the PowerShell script should be generated to include the server name - after all, it seems to know on which instance the job was created.

I've reached out to an MVP colleague who have worked more with notebooks than I do to see if there is something we are missing.

Muhammad Fawad 60

$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
$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"

This is the updated code, tried with both double and single backslash. Still got this error:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

12/30/2023 05:29:23,Duetto_Job,Error,,SUN-SQL02\BI_SIHOT,Duetto_Job,,,The job failed. The Job was invoked by User SUNSTAR\bi.consultant. The last step to run was step 1 (Exec-Notebook).,00:00:15,0,0,,,,0

12/30/2023 05:29:23,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:15,0,0,,,,0

Muhammad Fawad 60 Reputation points

2023-12-30T04:47:27.5233333+00:00

I deleted and recreated the job again and it shows the same error. Is this possible that the job is running in the background? Cuz the error does not show that I have edited the code even tho I have
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-30T10:39:32.26+00:00

You failed to update the call to Invoke-Sqlcmd that is in the beginning of the script.
Muhammad Fawad 0 Reputation points

2023-12-30T10:47:06.8233333+00:00

I updated and saved it multiple times but still it's giving the same error
Muhammad Fawad 0 Reputation points

2023-12-30T10:48:03.65+00:00

Oops got it
Muhammad Fawad 60 Reputation points

2023-12-30T11:02:46.0333333+00:00

12/30/2023 11:57:40,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 114 in a PowerShell script. The corresponding line is ' $SqlQueryExecutionTime = Measure-Command { $SqlQueryResult = @(Invoke-Sqlcmd @DatabaseQueryHashTable 4>&1) } '. 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 114 in a PowerShell script. The corresponding line is ' $SqlQueryExecu

Different error
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-30T11:08:47.3+00:00
Seems like this feature is designed to only run on default instances. What a joke!

Anyway, if you add this line:

$DatabaseQueryHashTable["Server"] = 'SUN-SQL02\BI_SIHOT'

maybe it will work.
Muhammad Fawad 60 Reputation points

2023-12-30T11:11:02.53+00:00

Add this after the $sqlresult?
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Muhammad Fawad 60 Reputation points

2023-12-30T11:25:30.0766667+00:00

12/30/2023 12:23:53,Duetto_Job,Error,1,SUN-SQL02\BI_SIHOT,Duetto_Job,Exec-Notebook,,Executed as user: NT Service\SQLAgent$BI_SIHOT. A job step received an error at line 2 in a PowerShell script. The corresponding line is '$DatabaseQueryHashTable["Server"] = 'SUN-SQL02\BI_SIHOT' '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot index into a null array. '. Process Exit Code -1. The step failed.,00:00:02,0,0,,,,0

Share via

Scheduling Python Notebook using SQL Server Agent

5 additional answers

Variables

conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxxxxxxxxxxx;DATABASE=xxxxx;UID=xxxx;PWD=xxxxxx'

Your answer