Can you help? Erland said
The first I realised about this problem was when Erland Sommarskog b asked me if I could help with this forum post The poster was getting an error when trying to run a Notebook Agent Job. The error was a very generic instance not found error
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 error: 40 - Could not open a connection to SQL Server) The system cannot find the file specified
Reproduce the issue
How very odd I thought. I noticed that it was a named instance, so I tried to recreate the issue on my own named instance DAVE. I added a new notebook job and when I tried to run it. It failed
Hmm. But I got a different error.
‘Login failed for user ‘NT Service\SQLAGENT$DAVE’
That doesn’t make a lot of sense. Why is the login failing?
Where is the logon ?
I checked the error log using
Get-DbaErrorLog -SqlInstance $SqlInstance -Source logon
but there were no results.
Wait a minute.
I have the default instance running on this machine. Lets check the error log on that instance as well.
Get-DbaErrorLog -SqlInstance $ENV:COMPUTERNAME -Source logon
Login failed for user ‘NT Service\SQLAgent$DAVE’. Reason: Could not find a login matching the name provided. [CLIENT: ]
So the Agent service for the named instance is failing to logon to the default instance on the host.
Let’s recreate the error properly this time
Stop the default instance
Stop-Service MSSQLSERVER -Force
and rerun the job, which fails, and get the error message
Get-DbaAgentJobHistory -SqlInstance $SqlInstance -Job $JobName
So I have recreated the error.
Now to solve it
First we need to understand what is happening. If we look at the Job Step for the Agent Job in Azure Data Studio
There is some PowerShell running to execute the cells of the notebook and gather the results and place them in the database for later use.
The code uses
$(ESCAPE_SQUOTE(A THING)) which is passing the Agent Job tokens to the script. you can find the list of Agent Job tokens here
Then it calls
Invoke-SqlCmd without a
ServerInstance parameter. If you look at the documentation for the parameter web
it shows the Default Value as
None but this does not explain what it does. With no value set for the
Invoke-SqlCmd will try to connect to the default instance as we can see below.
So we can get the Agent Job Tokens for the host and the instance name and set them as a variable and pass them to the ServerInstance paramater every time that it is called.
To save a load of copy pasta, the risk of not identifying all of the calls to the cmdlet, and keep the exising coding standards we can instead use PsDefaultParameters
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 whether it is on a default or a named instance.
Most best solution
As this code is available on GitHub and anyone can create an issue or a Pull Request, I did just that :-)