Azure Data Factory Stored Procedure fails with error code 2402

Boilard, Andre 46 Reputation points
2022-04-11T20:26:44.46+00:00

I have a Stored Procedure Activity in Azure Data Factory -- this SP creates indexes. They 'work' but one issues a warning because of too many bytes. As it's only a warning, what setting (tried turning off ansi warnings in SQL server to no avail) other than adding a failure output in the activity can be used to let warning messages continue but true errors stop?

Error code
2402
Failure type
User configuration issue
Details
Execution fail against sql server. Sql error number: 102. Error Message: Incorrect syntax near ')'.
Incorrect syntax near ')'.
Incorrect syntax near ')'.
Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'idx_rrs_include_x' has maximum length of 2438 bytes. For some combination of large values, the insert/update operation will fail.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,103 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.2K Reputation points MVP
    2022-04-11T21:04:46.39+00:00

    The only way to avoid that warning is make sure that the maximum key length of the index is below 1700 bytes. There is no setting to turn this off. After all, it is telling you that you have created an accident waiting to happen. Then again, if you know your data, you may be confident that it will not happen that all key columns are long, and in that case you can ignore the error.

    I should add that I'm purely talking from an SQL Server perspective. I don't work with Azure Data Factory, so I can't talk to the impact of this warning in ADF.

    I think you should fix the syntax errors. :-)


0 additional answers

Sort by: Most helpful