Category: Error Handling

Avoiding Division by Zero

Published 2021-02-25 by Kevin Feasel

Chad Callihan has a few methods for us to avoid dividing by zero:

In the real world, everyone knows that if you divide by zero a wormhole will open up and swallow the universe. In SQL Server, it’s not good, but it’s not nearly as dramatic. I encountered the following error this week and thought it would make a good topic:
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.
There are multiple ways to handle this error message in SQL Server and some are better than others. Let’s take a look at a few.

Click through for those methods and try to keep this universe existent—it’s where I keep all my stuff.

ML Services: PYTHONHOME and PATH

Published 2021-01-11 by Kevin Feasel

Niels Berglund troubleshoots some issues:

In the last post, which looks at using Python 3.9 in SQL Server Machine Learning Services, I wrote this at the very end:
It looks like all is good, but maybe not? In a future post we’ll look at an issue we have introduced – but for now, let us bask in the glory of having created a new Python language extension.
In the post, we wrote a new language extension to handle Python 3.9, and that just worked fine. However, when I was doing some other things, I noticed some side effects, and in this post, we look at those side effects and how to solve them.

Click through to learn more.

Comments closed

Troubleshooting an Error: Insufficient System Memory

Published 2020-12-10 by Kevin Feasel

Lee Markup walks us through an issue:

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

Read on to see how Lee was able to find and correct the issue without actually being able to start up SQL Server.

Comments closed

Msg 7390: The Requested Operation Could Not Be Performed

Published 2020-11-20 by Kevin Feasel

Jack Vamvas fixes a problem:

I have a SQL Server Linked Server configured , pointing to an ODBC – accessing a MongoDB driver . The test connections all work OK – and no problems running an OPENQUERY select statement using the Linked Server.
But when I attempt to run an EXECUTE AT , and attempt to INSERT the data into a #temp table – I get an error message:
Msg 7390, Level 16, State 2, Line 6
The requested operation could not be performed because OLE DB provider “MSDASQL” for linked server “my_linked_server” does
not support the required transaction interface.

Read on to see what the problem is and how you can solve it.

Comments closed

Error Handling in R

Published 2020-11-16 by Kevin Feasel

Adi Sarid compares a few methods for error handling in R:

Error catching can be hard to catch at times (no pun intended). If you’re not used to error handling, this short post might help you do it elegantly.
There are many posts about error handling in R (and in fact the examples in the purrr package documentation are not bad either). In this sense, this post is not original.
However, I do demonstrate two approaches: both the base-R approach (tryCatch) and the purrr approach (safely and possibly). The post contains a concise summary of the two methods, with a very simple example.

Read the whole thing. H/T R-Bloggers

Comments closed

Linked Server Data Does Not Match Expected Data Length

Published 2020-11-16 by Kevin Feasel

Jack Vamvas looks at a linked server error:

I’ve found an issue we have with BI Connector over MongoDB. I’m extracting data from MongoDB to SQL Server – using the MongoDB ODBC Data Source Configuration and Linked Server. Basically the BI Connector fails when the string in the column gets too long. When running the job to bring in the data to the production workflow data into a Datawarehouse application we get the following error.
Msg 7347, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ for linked server ‘MongoDB_PROD_mydb’ returned data that does not match expected data length for column ‘[MSDASQL].mywork.mytasks.mytaskInfo’. The (maximum) expected data length is 5332, while the returned data length is 5970.
Completion time: 2020-11-05T17:14:10.2206504+00:00

Read on for one solution.

Comments closed

In-Memory OLTP and HammerDB Setup Error

Published 2020-11-06 by Kevin Feasel

Erik Darling tracks down an error:

This a short post about a sort of quirky error message I got while trying to run the TPC-C load test against SQL Server’s in-memory tables.
Error in Virtual User1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The integer value XXXXXXXXXXXX is out of range.

Click through for the solution. Also, read down to the bottom of the post for a huge discount on Erik’s recorded training content. Erik is extremely knowledgeable and has a great way of explaining things, so take advantage of that knowledge.

Comments closed

FAIL_PAGE_ALLOCATION in SQL Server

Published 2020-11-04 by Kevin Feasel

Eric Cobb diagnoses an ugly issue:

I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load.
Here is a synopsis of what we were seeing:
This is a physical server and has 512GB of RAM installed. We have SQL Server 2016 installed, and fully patched (SP2 CU15 at this time). When load testing the server, it would start throwing errors such as:
“Failed allocate pages: FAIL_PAGE_ALLOCATION”
and
“There is insufficient system memory in resource pool ‘default’ to run this query.”
and
“Failed to allocate BUFs”
It would then write a memory dump to the log, and in most cases the server would become completely unresponsive and would have to be rebooted.

Read on to learn under what conditions this happens as well as the solution to the problem.

Comments closed

Query Processor Ran Out of Internal Resources

Published 2020-11-02 by Kevin Feasel

Andy Galbraith troubleshoots a problem:

Unfortunately a common error in many of our client environments is this:
—
Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.–There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed – a complex query.

Read on to see how to find this complex query, as well as a few examples of complex queries.

Comments closed

SQL Server Replication Requires Actual Server Names

Published 2020-10-26 by Kevin Feasel

Steve Stedman walks us through a pain point when using replication:

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name. (Replication.Utilities).
You might be thinking to yourself that you had a typo in the server name, but no, after checking the server name it matches what you can connect with.

When I’ve seen this error, often it will even tell me the server name it’s expecting, which then makes me ask why I have to type it in if it knows already.

Comments closed