0

I need to find all the stored procs that use Transactions, as I want to enable transaction abort to those procedures. However, --I didn't do this; it's inherited-- many of the stored procedures contain testing procedures with in comment blocks and most of the tests contain transaction blocks. I am only interested in changing stored procs that actually use transactions. AND I want to be able to monitor when stored procs are updated so that I can make sure that this flag is set.

SET XACT_ABORT ON;

Addendum: based on comments, here're some examples from my system.

/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/

/*
Use case: 147
BEGIN TRANSACTION
....
*/
  • 1
    . I am only interested in changing stored procs that actually use transactions - are you looking for an explicit begin tran .. end tran ? – Kin Shah Jul 29 at 16:22
  • Yes, I'm currently only looking for: %BEGIN TRANSACTION% – Display name Jul 29 at 17:15
  • I think that writing your own parser that eliminates comments would be a lot harder than just manually investigating to see false positives. YMMV. – Aaron Bertrand Jul 29 at 17:54
  • I'm hoping that there is a parser or SP_ proc that will strip out the comments. I agree with Aaron that I do not want to write my own. – Display name Jul 29 at 18:54
2

Here is a solution that I put together that worked, at least in my testing.

It relies on dbo.DelimitedSplit8K by Jeff Moden which you can find here https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

You just need to change it to use sys.sql_modules to sub in for the procname and proccode portions.

DECLARE @Sample AS TABLE
    (
    procname NVARCHAR(128) NOT NULL
    , proccode NVARCHAR(512) NOT NULL
    )

INSERT INTO @Sample
(procname, proccode)
VALUES ('test1-false'
    , N'/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/
Do Stuff
')
, ('test2-true'
    , N'
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
')
, ('test3-both'
    , N'
/*
-- clean up after tests
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
*/
BEGIN TRANSACTION
EXEC dbo.AR_Cleanup_MoveEqualAndOppositeSBPLiabilities
ROLLBACK TRANSACTION
')

DECLARE @Split NCHAR(1) = N'
'

/** NOTE: Using dbo.DelimitedSplit8K by Jeff Moden
    from https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function
    */

;WITH CTE_Split AS
    (
    SELECT S.procname
        , T.ItemNumber AS LineNumber
        , T.Item AS Line
        , LineType = CASE   WHEN T.Item LIKE '%/*%' THEN 'CommentStart'
                            WHEN T.Item LIKE '%*/%' THEN 'CommentEnd'
                            WHEN T.Item LIKE '%--%BEGIN TRAN%' THEN 'Commented'
                            WHEN T.Item LIKE '%--%ROLLBACK%' THEN 'Commented'
                            WHEN T.Item LIKE '%--%COMMIT%' THEN 'Commented'
                            WHEN T.Item LIKE '%BEGIN TRAN%' AND NOT(T.Item LIKE '%--%BEGIN TRAN%') THEN 'TransactionStart'
                            WHEN T.Item LIKE '%ROLLBACK%' AND NOT(T.Item LIKE '%--%ROLLBACK%') THEN 'TransactionRollback'
                            WHEN T.Item LIKE '%COMMIT%' AND NOT(T.Item LIKE '%--%COMMIT%') THEN 'TransactiionCommit'
                            ELSE ''
                            END
    FROM @Sample AS S 
        CROSS APPLY [dbo].[DelimitedSplit8K](proccode, @Split) AS T
    )
, CTE_CommentBlocks AS
    (
    SELECT procname, CommentStart, CommentEnd
    FROM (SELECT procname, LineType, LineNumber FROM CTE_Split WHERE LineType IN ('CommentStart', 'CommentEnd')) AS S
        PIVOT (MIN(LineNumber) FOR LineType IN (CommentStart, CommentEnd)) AS P
    )
, CTE_Commented AS
    (
    SELECT S.*
        , CommentedOut = CASE WHEN B.procname IS NOT NULL THEN 'Y' ELSE 'TRANSACTION' END
    FROM CTE_Split AS S
        LEFT OUTER JOIN CTE_CommentBlocks AS B ON B.procname = S.procname AND S.LineNumber BETWEEN B.CommentStart AND B.CommentEnd
    WHERE LineType <> ''
    )
SELECT * FROM CTE_Commented
  • Thank you, just what I needed. – Display name Jul 30 at 19:47
  • False positive though if something that looks like a comment is just inside a string or an entity, e.g. SELECT [/* Not a comment */] = 'This is /* not a comment */' FROM sys.objects AS [/* also not a comment */]; – Aaron Bertrand Aug 2 at 16:43
  • True, it is imperfect, and I should have done a better job detailing all the ways it could break. But given the specific use case of searching for begin transactions that aren’t commented out, I think it’s probably good enough for purpose. – Jonathan Fite Aug 2 at 16:45
  • Yep was just adding a disclaimer for the OP. Parsing is by definition imperfect. – Aaron Bertrand Aug 2 at 16:55
4

Below is a PowerShell example that uses the Microsoft.SqlServer.TransactSql.ScriptDom to parse procs and identify those with BEGIN TRAN statements. This version will download the assembly from NuGet if Microsoft.SqlServer.TransactSql.ScriptDom.dll doesn't already exist in the specified location. You could use a package manager instead for that task.

param (
    # Location of Microsoft.SqlServer.TransactSql.ScriptDom.dll assembly.
    $scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll",
    # Url of Microsoft.SqlServer.DacFx.x64 in official Microsoft NuGet repository.
    $scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/150.4200.1"
)

$procQuery = @"
SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(p.name) AS ProcedureName
    , m.definition
FROM sys.procedures AS p
JOIN sys.sql_modules AS m ON m.object_id = p.object_id;
"@

# Add Microsoft.SqlServer.TransactSql.ScriptDom assembly, downloading if needed
Function Add-ScriptDomAssemblyType ($scriptDomAssemblyPath, $scriptDomNuGetUrl) {

    # if T-SQL script DOM assembly does not exist, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
    if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
        if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
            throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
        }
        $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
        if ($response.StatusCode -ne 200) {
            throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
        }
        $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
        [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
        $response.BaseResponse.Dispose()
        $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
        Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
        $tempZipFilePath | Remove-Item
        Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
        $tempUnzipFolderPath | Remove-Item -Recurse
    }

    # load assembly into app domain so it can be used in this script
    Add-Type -Path $scriptDomAssemblyPath
}

# parse CREATE PROCEDURE script, returning true if it contains a BEGIN TRAN T-SQL statement
Function Test-HasBeginTrasaction ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a BeginTransactionStatement is found
        if ($statement.GetType().Name -eq "BeginTransactionStatement") {
            return $true
        }
    }

    # return false if no BeginTransactionStatement is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]

        if( $hasBeginTransaction ) {
            Write-Host "Has BEGIN TRANSACTION: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}

This can be easily extended to also check for SET XACT_ABORT ON in order to include only those procs that need attention, assuming SET XACT_ABORT ON is properly placed in the proc code. Below is an additional function and body code to do this.

# parse CREATE PROCEDURE script, returning true if it contains a SET XACT_ABORT ON
Function Test-HasSetXactAbortOn ($procText) {

    # use TSql parser appropriate for targetted SQL Server version
    $parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql140Parser($true)
    $parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
    $scriptReader = New-Object System.IO.StringReader($procText)
    $fragment = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0)
    {
        # parsing errors may occur for existing stored procs if the code is invalid due to obsolete T-SQL syntax
        throw "$($parseErrors.Count) parsing errors: $( ( $parseErrors | ConvertTo-Json ) )"
    }

    # evaluate each statement in stored procedure
    foreach ($statement in $fragment.Batches[0].Statements[0].StatementList.Statements) {
        # return true if a SET XACT_ABORT ON is found
        if (($statement.GetType().Name -eq "PredicateSetStatement") -and
            ($statement.Options -band [Microsoft.SqlServer.TransactSql.ScriptDom.SetOptions]::XactAbort) -and
            $statement.IsOn ) {
            return $true
        }
    }

    # return false if no SET XACT_ABORT ON is found
    return $false

}

############
### main ###
############

try {

    Add-ScriptDomAssemblyType -scriptDomAssemblyPath $scriptDomAssemblyPath -scriptDomNuGetUrl $scriptDomNuGetUrl

    $connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand($procQuery, $connection)
    $connection.Open()
    $reader = $command.ExecuteReader()
    while($reader.Read()) {

        $hasBeginTransaction = Test-HasBeginTrasaction -procText $reader["definition"]
        $hasSetXactAbortOn = Test-HasSetXactAbortOn -procText $reader["definition"]

        if( $hasBeginTransaction -and !$hasSetXactAbortOn ) {
            Write-Host "Has BEGIN TRANSACTION without SET_XACT_ABORT ON: $($reader["ProcedureName"])"
        }

    }
    $reader.Close()
    $connection.Close()

}
catch {
    throw
}
  • This would be great but for the fact that I need a sql based solution. I'm sure others will be able to use it though. – Display name Jul 30 at 19:47
  • @Displayname Given that using a DOM is much more reliable than parsing text, can you explain why the only possible solution can be in T-SQL? – Aaron Bertrand Aug 2 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.