0

I want to upgrade SQL server from 2008 to 2014 versiyon. I checked all of the deprecated features including raiserror syntax. But I have many stored procedures to check. Some of them use new syntax, some of them use old syntax and I need to alter the old syntax from them. How can I check all of the procedures to detect old raiserror syntax?

2

Below is a PowerShell script example that uses the T-SQL Script DOM parser to parse all existing T-SQL modules in the specified database and display errors if any cannot be parsed for the target SQL version. This will identify discontinued syntax for RAISERROR and any other discontinued T-SQL syntax as well.

$scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/140.3881.1"

# if T-SQL script DOM assembly doesn't already exist at specifed path, 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" $scriptDomAssemblyPath
    $tempUnzipFolderPath | Remove-Item -Recurse
}

Add-Type -Path $scriptDomAssemblyPath

# use the appropriate TSqlParser version for target SQL version (TSql120Parser for SQL Server 2014)
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

# parse existing T-SQL modules in database
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=Parse Modules"
$connection = New-object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-object System.Data.SqlClient.SqlCommand("SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS ObjectName, definition FROM sys.sql_modules;", $connection);
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read()) {

    $scriptReader = New-Object System.IO.StringReader($reader["definition"])
    $frament = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0) {

        Write-Host "error parsing [$($reader["SchemaName"])].[$($reader["ObjectName"])]"
        foreach ($parseError in $parseErrors) {
            Write-Host "`t$($parseError.Message)"
        }
    }

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

According to Discontinued Database Engine Functionality in SQL Server 2012, the deprecated version of raiserror is

RAISERROR integer 'string'.

Notice there is no parenthesis and there is a number after RAISERROR.

Using @HandyD's approach as a starting point, you could do something like this (sqltable simulates what is returned from INFORMATION_SCHEMA.ROUTINES).

--demo setup
Declare @sqltable table(sqlvar varchar(max))
Declare @CrLf varchar(2) = char(13) + char(10)

insert into @sqltable(sqlvar) values('Raiserror 50000 ''This is old syntax'')')
insert into @sqltable(sqlvar) values('Raiserror 
50000 ''This is old syntax where the command is on multiple lines'')')
insert into @sqltable(sqlvar) values('RAISERROR (''This is new syntax'',16,0)')

--the query
select * from @sqltable where replace(replace(sqlvar,@CrLf,''),' ','') like '%raiserror[0-9]%'

| sqlvar                                                                         |
|--------------------------------------------------------------------------------|
| Raiserror 50000 'This is old syntax')                                          |
| Raiserror   50000 'This is old syntax where the command is on multiple lines') |
  • It does not enough. My procedures include multiple lines. – Banu Akkus Feb 13 at 12:12
  • @BanuAkkus - I updated my example to handle the command raiserror being on multiple lines. See if that helps. – Scott Hodgin Feb 13 at 12:25
0

Use this script to identify any procedures or functions using the old syntax:

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%RAISERROR%'

This needs to be executed in the context of the database you're checking. For multiple databases, you will need to run it against each database.

More info on INFORMATION_SCHEMA.ROUTINES.

  • But it returns all of stored procedures that contain “raiserror”. I want to detect only need to alter old syntax. – Banu Akkus Feb 13 at 9:00

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.