Get line number in file following TSQL error message

Question

Consider the following sql script

:ON ERROR EXIT

PRINT 'Line 3'
GO

PRINT 'Line 6'
GO

SELECT * FROM NonExistingTable
GO

PRINT 'Line 12'
GO

When you run with SQLCMD

> sqlcmd -i MyScript.sql
Line 3
Line 6
Msg 208, Level 16, State 1, Server MyServer, Line 2
Invalid object name 'NonExistingTable'.

When you run in SQL Server Management Studio with SQLCMD Mode enabled you get

Line 3
Line 6
Msg 208, Level 16, State 1, Server MyServer, Line 2
Invalid object name 'NonExistingTable'.
** An error was encountered during execution of batch. Exiting.

But when you double click on the error line the query editor will jump to the problematic line.

Reported Line 2 means a line number relative to the batch. Batches are separated by GO statement. We want to get a real Line 9 answer.

I've also tried PowerShell's Invoke-Sqlcmd but it is even worse, since it does not detect such errors at all (Error detection from Powershell Invoke-Sqlcmd not always working?).

Is there a simple way to wrap our sql script with some helpers to get the desired real error lines?

UPD: I've changed the error script to make sure it would fail for sure...

Answer 1

Here is the solution I came up with: https://github.com/mnaoumov/Invoke-SqlcmdEx

And now

> .\Invoke-SqlcmdEx.ps1 -InputFile .\MyScript.sql
Line 3
Line 6
Msg 208, Level 16, State 1, Server MyServer, Script .\MyScript.ps1, Line 9
Invalid object name 'NonExistingTable'.

sqlcmd failed for script .\MyScript.ps1 with exit code 1
At C:\Dev\Invoke-SqlcmdEx\Invoke-SqlcmdEx.ps1:77 char:18
+             throw <<<<  "sqlcmd failed for script $InputFile with exit code $LASTEXITCODE"
    + CategoryInfo          : OperationStopped: (sqlcmd failed f...ith exit code 1:String) [], RuntimeException
    + FullyQualifiedErrorId : sqlcmd failed for script .\MyScript.ps1 with exit code 1

And it has a proper Line 9 output

Just in case I inline the script here as well. The script may look like an overkill but it is written like that in order to fully support all SQLCMD script features and deal correctly with transactions

Invoke-SqlcmdEx.ps1

#requires -version 2.0

[CmdletBinding()]
param
(
    [string] $ServerInstance = ".",
    [string] $Database = "master",
    [string] $User,
    [string] $Password,

    [Parameter(Mandatory = $true)]
    [string] $InputFile
)

$script:ErrorActionPreference = "Stop"
Set-StrictMode -Version Latest
function PSScriptRoot { $MyInvocation.ScriptName | Split-Path }

trap { throw $Error[0] }

function Main
{
    if (-not (Get-Command -Name sqlcmd.exe -ErrorAction SilentlyContinue))
    {
        throw "sqlcmd.exe not found"
    }

    $scriptLines = Get-Content -Path $InputFile
    $extendedLines = @()

    $offset = 0
    foreach ($line in $scriptLines)
    {
        $offset++
        if ($line -match "^\s*GO\s*$")
        {
            $extendedLines += `
                @(
                    "GO",
                    "PRINT '~~~ Invoke-SqlcmdEx Helper - Offset $offset'"
                )
        }

        $extendedLines += $line
    }

    $tempFile = [System.IO.Path]::GetTempFileName()

    try
    {
        $extendedLines > $tempFile

        $sqlCmdArguments = Get-SqlCmdArguments

        $ErrorActionPreference = "Continue"
        $result = sqlcmd.exe $sqlCmdArguments -i $tempFile 2>&1
        $ErrorActionPreference = "Stop"

        $offset = 0
        $result | ForEach-Object -Process `
            {
                $line = "$_"
                if ($line -match "~~~ Invoke-SqlcmdEx Helper - Offset (?<Offset>\d+)")
                {
                    $offset = [int] $Matches.Offset
                }
                elseif (($_ -is [System.Management.Automation.ErrorRecord]) -and ($line -match "Line (?<ErrorLine>\d+)$"))
                {
                    $errorLine = [int] $Matches.ErrorLine
                    $realErrorLine = $offset + $errorLine
                    $line -replace "Line \d+$", "Script $InputFile, Line $realErrorLine"
                }
                else
                {
                    $line
                }
            }

        if ($LASTEXITCODE -ne 0)
        {
            throw "sqlcmd failed for script $InputFile with exit code $LASTEXITCODE"
        }
    }
    finally
    {
        Remove-Item -Path $tempFile -ErrorAction SilentlyContinue
    }
}

function Get-SqlCmdArguments
{
    $sqlCmdArguments = `
        @(
            "-S",
            $ServerInstance,
            "-d",
            $Database,
            "-b",
            "-r",
            0
        )

    if ($User)
    {
        $sqlCmdArguments += `
            @(
                "-U",
                $User,
                "-P",
                $Password
            )
    }
    else
    {
        $sqlCmdArguments += "-E"
    }

    $sqlCmdArguments
}

Main

UPD: @MartinSmith provided a neat idea to use LINENO aproach.

Here is the version which uses this approach: https://github.com/mnaoumov/Invoke-SqlcmdEx/blob/LINENO/Invoke-SqlcmdEx.ps1 It basically inserts LINENO [corresponding-line-number] after each GO statement.

But if we consider the following script

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.MyFunction') AND type = 'FN')
    EXEC sp_executesql N'CREATE FUNCTION dbo.MyFunction() RETURNS int AS BEGIN RETURN 0 END'
GO
LINENO 3

ALTER FUNCTION dbo.MyFunction()
RETURNS int
AS
BEGIN
    RETURN 42
END
GO

It will fail with

> sqlcmd -i MyScript.sql
Msg 111, Level 15, State 1, Server MyServer, Line 5
'ALTER FUNCTION' must be the first statement in a query batch.
Msg 178, Level 15, State 1, Server MyServer, Line 9
A RETURN statement with a return value cannot be used in this context.

So LINENO approach won't work for the statements that have to be the first in a query batch. Here is the list of such statements: http://msdn.microsoft.com/en-us/library/ms175502.aspx: CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW. ALTER statements are not mentioned but I think the rule is applied for them as well