Press "Enter" to skip to content

Category: Error Handling

Row-Level Security and UseRelationship

Published 2021-03-10 by Kevin Feasel

Teo Lachev points out an issue when combining row-level security with the USERELATIONSHIP() function in a Tabular model:

You’ve created a beautiful, wide-open Tabular model. You use USERELATIONSHIP() to switch relationships on and off. Everything works and everyone is pleased. Then RLS sneaks in, such as when external users need access, and you must secure on some dimension table. You create a role, specify a row filter, test the role, and get greeted with:

The UseRelationship() and CrossFilter() functions may not be used when querying ‘<dimension table>’ because it is constrained by row-level security defined on ‘<dimension table>’ or related tables.

Read on to learn what the issue is and one potential workaround.

Comments closed

Power BI: Parquet Files and Streamed Binary Values Error

Published 2021-03-08 by Kevin Feasel

Chris Webb explains an error:

If you’re using the new Parquet connector in Power BI there’s a chance you will run into the following error:

Parameter.Error: Parquet.Document cannot be used with streamed binary values.
Details:
[Binary]

This isn’t a bug or anything that can be fixed, so it’s important to understand why it occurs and what you can do about it.

Click through for the explanation.

Comments closed

The Importance of Configuring SQL Server Agent Alerts

Published 2021-03-08 by Kevin Feasel

Chad Callihan has a story in three acts:

Once upon a time, I was having a relaxing morning…until I received an e-mail alert about an Error Number 825 on a database. The error stated:

‘D:\sql_dat\DatabaseName.mdf_MSSQL_DBCC47’ at offset 0x00000004b9e000 succeeded after failing 1 time(s) with error: 121(The semaphore timeout period has expired.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

That’s not how you want your day to go. Fortunately, the database was not one that needed to be online 24/7 so I felt I could relax a little bit while starting to investigate. At this point, the issue seemed to be with one database.

Click through to see if everyone lived happily ever after.

Comments closed

Clarifying Key Errors on Graph Tables in SQL Server

Published 2021-03-05 by Kevin Feasel

Louis Davidson clears up some noise:

This causes the following error message:

Msg 2627, Level 14, State 1, Line 14Violation of UNIQUE KEY constraint 'AKEdge'. Cannot insert duplicate key in object 'dbo.Edge'. The duplicate key value is (455672671, 0, 455672671, 1).

So what is this: (455672671, 0, 455672671, 1)?

Click through to understand what this all means. Louis also has a quick procedure which looks up those details.

Comments closed

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.

Comments closed

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