SqlException.Message returns wrong order of messages

Question

I am making a database update mechanism that is supposed to run sql scripts and insert their results into update log table.

I am using C# to run my scripts and to dynamically set my sql message that's supposed to be inserted into the update log table.

If there is an error in the script, I am catching it and using SqlException.Message to write it to the message.

if (exception != null)
{
    sqlMessage += exception.Message + Environment.NewLine;
    break;
}

My script looks like so (Numbers and arrow are not actually in the script):

1.   USE [Database]

     IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Users'))
     BEGIN
         print('Table exists - proceeding to insert');
     END
     ELSE
     BEGIN
         -- CREATING TABLE
         CREATE TABLE [dbo].[Users](
             [UserId] [int] NOT NULL,
             [UserFullName] [nvarchar](100) NOT NULL,
             [UserName] [nvarchar](100) NOT NULL,
             [UserShortName] [nvarchar](50) NOT NULL,
             [UserLogin] [varchar](50) NOT NULL
         )
2.       print 'Created table'
     END

3.-> IF(EXISTS(SELECT * FROM [Users_error] WHERE UserId = 1 ))
     BEGIN
         print('User exists - nothing to insert');
     END
     ELSE
     BEGIN
         -- INSERTING USER
         INSERT INTO [dbo].[Users]
               ([UserId]
               ,[UserFullName]
               ,[UserName]
               ,[UserShortName]
               ,[UserLogin])
         VALUES  
             (1,'System Administrator','Admin','SA',    'SA')
         print 'Inserted user'
     END

For testing purposed I changed the "Users" to "Users_error" to throw an error (Line with arrow).

I was expecting following log (numbers correspond to numbers in script):

----- STARTED 2.sql -----
1. Changed database context to 'Database'.
2. Created table
3. Invalid object name 'Users_error'.     
----- FINISHED 2.sql -----

Instead, I got following log:

----- STARTED 2.sql -----
3. Invalid object name 'Users_error'.
1. Changed database context to 'Database'.
2. Created table
----- FINISHED 2.sql -----

Is there a way to get it back to the normal order? It would be quite annoying to debug a script with log messages all over the place.

Thanks!

Answer 1

Invalid object name 'Users_error' is a compilation error. The other two are execution messages. Compilation of the batch occurs before execution. For more details, read Understanding how SQL Server executes a query.