1

We have a View in Azure SQL server that is erroring - but only when utilizing TOP.

  • SELECT * FROM View = fine.
  • SELECT [list every column individually] FROM View = fine
  • Take the underling code from the View and run it = fine.

It's just including TOP (anything) that causes the ERROR. Even SELECT TOP (1) * FROM View - repeatedly errors.

(It's not that everyone on my team simultaneously forgot the syntax for TOP - plus it does it when selecting SELECT TOP 1000... via the SSMS interface as well).

The specific error is:

Msg 537, Level 16, State 3, Line 1. Invalid length parameter passed to the LEFT or SUBSTRING function.

Which, sure - that's a likely error given the query: There are things being split by a delimiter, but one needs to remove the entries that lack that delimiter, in the WHERE clause.

But why is it only throwing this when using TOP? I'm kind of at a loss.

Dale K
  • 25,246
  • 15
  • 42
  • 71
bbb0777
  • 165
  • 14

1 Answers1

2

The most likely cause is that the select top changes the execution plan -- perhaps because of the columns being chosen.

Then, one of two things might be happening:

  1. Some row in the table generates an error but is not in the result set with the initial execution plans. You don't see the error in the first queries, because they have not yet returned the row. But the altered execution plan returns the row sooner.

  2. SQL Server has pushed a predicate around in the execution graph. The row may be filtered out, but in the mean time . . . it generates an error.

Also, these are hard to figure out. However, the code might have an obvious call to left() or substring() that is causing the problem. If you can find the code that causes the problem, it should be pretty simple to fix it.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks! Fixing it was no problem (already done in the interim between my question and your comment). But regardless I was really stumped as to *why* the error wouldn't always be there. For 1.) The error didn't show up with doing SELECT * at all - even upon query completion, it was fine. Or am I misunderstanding 1.) maybe? – bbb0777 Sep 16 '20 at 00:32
  • 1
    @bbb0777 . . . The query may both have to complete *and* you may need to read all the results. But the second possibility may be what is really happening -- if rows are being filtered out in `where` or `on` conditions. Note: I consider (2) to be a bug. However, Microsoft clearly disagrees. – Gordon Linoff Sep 16 '20 at 00:40