Joey D’Antoni provides a solution to a tricky SQL Server R Services error:
Recently, and unfortunately I don’t have an exact date on when this started failing (though it was around service pack 1 install time) with the following error:
Error
Msg 39012, Level 16, State 1, Line 10
Unable to communicate with the runtime for ‘R’ script. Please check the requirements of ‘R’ runtime.
STDERR message(s) from external script:DLL ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ cannot be loaded.
Error in eval(expr, envir, enclos) :
DLL ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ cannot be loaded.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
STDOUT message(s) from external script:Failed to load dll ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER1601\MSSQL\Binn\sqlsatellite.dll’ with 182 error.
Click through to see how to resolve this issue.
Adrian Buckman has a script which tracks error messages related to database restorations:
In one of my previous posts I went over a scenario where an Auto restore job was logging Restore errors to a table and the error that was being inserted was ‘3013 – RESTORE LOG is terminating abnormally’ and this was due to SQL Server only providing the Last most error produced which is stored within ERROR_NUMBER() and ERROR_MESSAGE() at point of error.
I found this error less than useful so I set out to try and log something more meaningful , which I ended up doing for the specific error (4305) which was being encountered at the time, but I wanted to make this better and less specific to the 4305 error.
This is a very interesting post and a good example of using built-in error handling functionality to help automate your processes.
Mark Wilkinson explains how to keep your SQL Server error logs in check:
We typically think of error logs as somewhere to go to find issues, but what if your error logs ARE the issue? Like most anything else in SQL Server, if you neglect your error logs you can run into trouble. Even on a low-traffic SQL Server instance, a bad piece of code, or a hardware issue, could easily fill your error logs, and with the introduction of Hekaton in SQL Server 2014, the SQL Server error log started getting a lot more data pumped into it than you might have been used to before. What this means for the DBA is that you can quickly start filling your main system drive (if your SQL install and error logs are in the default location) with massive error logs. So what questions should you be answering about error logs to make sure you don’t run into problems?
Read on to learn more.
Adam Bertram explains terminating versus non-terminating errors in Powershell:
Non-terminating errors are still “errors” in PowerShell but not quite as severe as terminating ones. Non-terminating errors aren’t as serious because they do not halt script execution. Moreover, you can silence them, unlike terminating errors. You can create non-terminating errors with the Write-Error cmdlet. This cmdlet writes text to the error stream.
You can also manipulate non-terminating errors with the common ErrorAction and ErrorVariable parameters on all cmdlets and advanced functions. For example, if you’ve created an advanced function that contains a Write-Error reference, you can temporarily silence this as shown below.
Adam also shows how to convert a non-terminating error into a terminating error in your script.
Chris Webb explains when you might get a Formula.Firewall error in Power BI or Power Query:
The important difference here is that there is now one step in this query instead of two: the query and the filtering take place in the same step. Even more importantly, regardless of the data privacy settings, the query fails with the error:
Formula.Firewall: Query ‘DimDate With Native Query Single Step Fails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The problem here is that the Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.
This is an interesting downside to putting in complex data privacy rules.
When the job fails, and the alert message compiled, this procedure gets called and the job name, step name, a delay value are passed to it. There is also a retry flag that comes back fro this procedure.
The first thing this procedure does is go and find the last failed step for the particular job. It then counts and based on the @retry value verifies if a retry job has already been created. This is in case some other process tries to do this same thing and should help prevent too many retries from firing off.
If a retry job does not exist, this process creates a new disposable job that will rerun the original from the beginning or the step that failed based on the checking for “Level 1” or “Level 2” in the job name. The job is prefixed with ‘Retry -‘ so it can be found easily in your server’s job list.
If a delay is specified, 2 minutes in this example, then it calculates a new run time for the retry job and finally creates the job.
This helps make SQL Agent jobs a little more robust.
Chris Lumnah troubleshoots an error in automatic seeding of an Availability Group:
In my lab, I decided to play around with the automatic seeding functionality that is part of Availability Groups. This was sparked by my last post about putting SSISDB into an AG. I wanted to see how it would work for a regular database. When I attempted to do so, I received the following error:
Cannot alter the availability group ‘Group1’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
Read on for the answer; it turns out automatic seeding itself was not the culprit.
The error message is a little misleading but let’s save the debugging debate for another day. The key observation is “Guildford” data is not available, simply because it comes after “Camberley” in the list. Whilst we want to see errors in a Query, we do not want them causing data loss.
Resolution
As I mentioned at the beginning of this article, using the Remove Errors function would prevent the loss of Guildford data. However, the user needs to handle errors as Unknown Members and conform to a typical Kimball Data Warehouse.
I am sure there are many ways to fulfil the requirement, but here is how I approached it:
Read on for the resolution.
Meagan Longoria went through a frustrating scenario:
On a recent project I used Azure Data Factory (ADF) to retrieve data from an on premises SQL Server 2014 instance and land them in Azure Data Lake Store (ADLS) as ORC files. This required the use of the Data Management Gateway (DMG). Setup was quick and easy in our development environment. We installed the DMG for development on a separate server in the client’s network, where we also installed SQL Server Management Studio (SSMS) for query development and data validation. We set up resource groups in Azure for development and production, and made sure the settings for development and production were the same. Then we set up a separate server for the production DMG.
Deployment and execution went well in the dev environment. Testing was completed, so we deployed to prod. Deployment went fine, but the pipelines failed execution and returned the following error on the output data sets.
Weird solution, but I’m going to guess that it makes perfect sense if you are able to look at the code.
I ran into an error in SQL Server R Services:
I recently worked through a strange error (with help from a couple sharp cookies at Microsoft) and wanted to throw together a quick blog post in case anybody else sees it.
I have SQL Server R Services set up, and in the process of running a fairly complex stored procedure, got the following error message:
Msg 39004, Level 16, State 22, Line 0
A ‘R’ script error occurred during execution of ‘sp_execute_external_script’ with HRESULT 0x80004005.
Check those output variable and result set definitions.
Kevin Feasel
2017-08-14
Error Handling, R
No Comment