MicrosoftHi Yochanan_Rachamim I managed to find the root cause and fix the issue! 🙂
When I tested the script for the first time, I created the stored procedure on the database manually through SQL Server Management Studio and everything worked successfully.
However, after I automated the creation of the stored procedure with sqlcmd, the defrag operation started failing on several indexes...
Then, I learned that QUOTED_IDENTIFIER is by default disabled on sqlcmd, and even though the stored procedure has a statement to turn it on, it has no effect at all and thus it continues to be off:
"Static Transact-SQL in a stored procedure is parsed using the QUOTED_IDENTIFIER setting in effect for the batch that created or altered the stored procedure. SET QUOTED_IDENTIFIER has no effect when it appears in the body of a stored procedure as static Transact-SQL."
Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver16#remarks
To be able to continue with my automated setup without running into issues, I deleted the stored procedure and recreated it with sqlcmd again, but now specifying the -I switch to enable QUOTED_IDENTIFIER:
sqlcmd -? | grep "Quoted"
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
Thanks a lot for your time/support on this.
Best regards,
Wellington