Error After Introducting TransactionScope in .Net Application

[Skeldave]

Description of the issue
After introducing transaction scopes the index optimize has started failing.

The theory is that transaction scopes have introduced something within the database but I cannot find what this might be.

The script is executed within Azure Devops Pipelines, the index optimise script seems to work as a standalone script

SQL Server version and edition
Execute SELECT @@VERSION

Microsoft SQL Azure (RTM) - 12.0.2000.8 Aug 26 2025 08:30:18 Copyright (C) 2025 Microsoft Corporation

Version of the script
Check the header of the stored procedure

Version: 2025-08-23 17:25:24

What command are you executing?

EXEC sp_createstats

EXEC dbo.IndexOptimize
    @Databases = 'XXX_DBNAME_XXX',
    @Execute = 'Y',
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @Indexes = 'ALL_INDEXES',
    @LOBCompaction = 'Y',
    @LogToTable = 'Y',
    @MinNumberOfPages = 1000,
    @MSShippedObjects = 'N',
    @OnlyModifiedStatistics = 'Y',
    @PadIndex = 'N',
    @PartitionLevel = 'Y',
    @Resumable = 'N',
    @SortInTempdb = 'N',
    @StatisticsResample = 'N',
    @UpdateStatistics = 'INDEX';

- task: SqlAzureDacpacDeployment@1
                displayName: Index & Optimize
                inputs:
                  AuthenticationType: servicePrincipal
                  azureSubscription: ${{ variables.subscription }}
                  DatabaseName: $(databaseCurrent)
                  deployType: InlineSqlTask
                  IpDetectionMethod: AutoDetect
                  ServerName: $(sqlServerCurrent).database.windows.net
                  SqlAdditionalArguments: -verbose
                  SqlInline: |
                    EXEC sp_createstats

                    EXECUTE [dbo].[IndexOptimize]
                    @Databases = '$(databaseCurrent)',
                    @Execute = '${{ parameters.execute }}',
                    @FragmentationHigh = '${{ parameters.fragmentationHigh }}',
                    @FragmentationLevel1 = ${{ parameters.fragmentationLevel1 }},
                    @FragmentationLevel2 = ${{ parameters.fragmentationLevel2 }},
                    @FragmentationMedium = '${{ parameters.fragmentationMedium }}',
                    @Indexes = '${{ parameters.indexes }}',
                    @LOBCompaction = '${{ parameters.lobCompaction }}',
                    @LogToTable = '${{ parameters.logToTable }}',
                    @MinNumberOfPages = ${{ parameters.minNumberOfPages }},
                    @MSShippedObjects = '${{ parameters.MSShippedObjects }}',
                    @OnlyModifiedStatistics = '${{ parameters.onlyModifiedStatistics }}',
                    @PadIndex = '${{ parameters.padIndex }}',
                    @PartitionLevel = '${{ parameters.partitionLevel }}',
                    @Resumable = '${{ parameters.resumable }}',
                    @SortInTempdb = '${{ parameters.sortInTempdb }}',
                    @StatisticsResample = '${{ parameters.statisticsResample }}',
                    @UpdateStatistics = '${{ parameters.updateStatistics }}',`

**What output are you getting?**

`Invoke-Sqlcmd -AccessToken "**********" -ServerInstance "XXX-sql-server.database.windows.net" -Database "XXX_DBNAME_XXX"  -Inputfile "C:\Users\VssAdministrator\AppData\Local\Temp\tmp8257.tmp" 
##[error]Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column 'col_name', table 'tempdb.dbo.#colpostab__________________________________________________________________________________________________________000000000272'; column does not allow nulls. INSERT fails.
There are already statistics on table 'global_transaction_table' named 'transaction_id'.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
Table 'XXX_DBNAME_XXX.sys.global_transaction_table': Creating statistics for the following columns: 
 Msg 515, Level 16, State 2, Procedure sp_createstats, Line 158.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

Thank you!