0

I exported a database to a .bacpac but when I tried to import said .bacpac again an error is raised:

"Data plan execution failed with message One or more errors occurred" and the error that triggered this failure was "Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated. (Microsoft SQL Server, Error: 537)"

When I looked at the imported database I find that one table that contained data when it was exported does not contain data after the import.

When I delete everything out of that table and create a new .bacpac, the new .bacpac imports without problems.

Question: why might the mere fact that there are data in a particular table cause an import to fail like that?

DinahMoeHumm
  • 185
  • 10

1 Answers1

1

I discovered what the problem was and I decided to share this as it is something that might catch you out as well....

I ran a script to see whether there was anything referencing the table that was causing this problem:

select [SO].*
  from sys.sql_modules [SM]
       inner join sys.objects [SO]
               on [SO].object_id = [SM].object_id
 where [SM].[definition] LIKE '%name_of_the_problematic_table_here%'

When I did that, I discovered that the table in question was referenced in a Schema Bound View and some of the calculations in the columns for that view did indeed use SUBSTRING functions.

To test whether this was the cause of the problem I ran a few manual insert statements to the table, and wouldn't you know it... the same error was raised when inserting data into that table.

The view was a bit unusual in that it had a SUBSTRING calculation for one of the columns and a WHERE clause excluding rows where that SUBSTRING wouldn't work. For some reason this doesn't cause a problem in the existing view, even when there are rows in the original table that would be excluded in the VIEW, but on an insert they still caused an error to be thrown.

I rewrote the Schema Bound View so that the calculation would never throw an error, even when applied to rows that would be exluded through the WHERE clause, and the problem went away.

DinahMoeHumm
  • 185
  • 10
  • 2
    There is no "short circuiting" in sql. The engine is free to rearrange the query to actually execute it. You have been "lucky" that the error was not discovered earlier. – SMor Aug 08 '22 at 11:17
  • 1
    Sounds like the proper solution is the fix the `VIEW` so that a value <0 can't be passed to the `LEFT`/`SUBSTRING` function. – Thom A Aug 08 '22 at 11:18
  • exactly! Thanks folks. – DinahMoeHumm Aug 08 '22 at 11:25
  • Yes... so instead of doing SUBSTRING(somevalue, etc, etc) I modified it so the calculation looks more like CASE WHEN (conditions where substring will fail) THEN (some default value) ELSE SUBSTRING...... END – DinahMoeHumm Aug 08 '22 at 11:30