SQL Server – Script Not Working After Adding Try and Catch

I have a working script that I modified to work with SQLCMD since I need to invoke this script remotely.

Here is my script:

:ON ERROR EXIT
USE [master]
GO

BEGIN TRY

/****** Declare Database name ******/
DECLARE @server varchar(8000);
SET @server = N'script_test';

/****** Automatically set username since we have a convention for it ******/
DECLARE @user varchar(800);
SET @user = @server + '_sys';

/****** First kill all active connections to the db and then drop it if the db already exists ******/
IF EXISTS(select * from sys.databases where name= @server )
begin
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@server)

EXECUTE(@kill);

EXECUTE('DROP DATABASE ' + @server)
end

/****** Create the database again ******/
DECLARE @Create_Template varchar(MAX) = 'CREATE DATABASE {SERVERNAME}
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = {SERVERNAME}, FILENAME = N''D:\MSSQL16.DATA\MSSQL\DATA\{SERVERNAME}.mdf'' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = {SERVERNAME}_log, FILENAME = N''D:\MSSQL16.DATA\MSSQL\DATA\{SERVERNAME}_log.ldf'' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )'

DECLARE @Create_Script varchar(MAX) = REPLACE(@Create_Template, '{SERVERNAME}', @server)

EXECUTE(@Create_Script)

EXECUTE('ALTER DATABASE ' + @server + ' SET COMPATIBILITY_LEVEL = 130')


IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
DECLARE @Action_Template varchar(MAX) = '[{SERVERNAME}].[dbo].[sp_fulltext_database] @action = ''enable'''

DECLARE @Action_Script varchar(MAX) = REPLACE(@Action_Template, '{SERVERNAME}', @server)

EXECUTE(@Action_Script)
end

EXECUTE('ALTER DATABASE ' + @server + ' SET ANSI_NULL_DEFAULT OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET ANSI_NULLS OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET ANSI_PADDING OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET ANSI_WARNINGS OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET ARITHABORT OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET AUTO_CLOSE OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET AUTO_SHRINK OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET AUTO_UPDATE_STATISTICS ON')

EXECUTE('ALTER DATABASE ' + @server + ' SET CURSOR_CLOSE_ON_COMMIT OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET CURSOR_DEFAULT  GLOBAL')

EXECUTE('ALTER DATABASE ' + @server + ' SET CONCAT_NULL_YIELDS_NULL OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET NUMERIC_ROUNDABORT OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET QUOTED_IDENTIFIER OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET RECURSIVE_TRIGGERS OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET  DISABLE_BROKER')

EXECUTE('ALTER DATABASE ' + @server + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET DATE_CORRELATION_OPTIMIZATION OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET TRUSTWORTHY OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET ALLOW_SNAPSHOT_ISOLATION ON')

EXECUTE('ALTER DATABASE ' + @server + ' SET PARAMETERIZATION SIMPLE')

EXECUTE('ALTER DATABASE ' + @server + ' SET READ_COMMITTED_SNAPSHOT ON')

EXECUTE('ALTER DATABASE ' + @server + ' SET HONOR_BROKER_PRIORITY OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET RECOVERY FULL')

EXECUTE('ALTER DATABASE ' + @server + ' SET  MULTI_USER')

EXECUTE('ALTER DATABASE ' + @server + ' SET PAGE_VERIFY CHECKSUM')

EXECUTE('ALTER DATABASE ' + @server + ' SET DB_CHAINING OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )')

EXECUTE('ALTER DATABASE ' + @server + ' SET TARGET_RECOVERY_TIME = 60 SECONDS')

EXECUTE('ALTER DATABASE ' + @server + ' SET DELAYED_DURABILITY = DISABLED')

EXECUTE('ALTER DATABASE ' + @server + ' SET QUERY_STORE = OFF')

EXECUTE('ALTER DATABASE ' + @server + ' SET  READ_WRITE')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;')

EXECUTE('USE '+ @server + '; ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;')

/****** Create the user, then assign the user as DB_OWNER to the table ******/
EXECUTE('USE master; CREATE LOGIN ' + @user +' WITH PASSWORD=N'''+ @user +''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;')

EXECUTE('USE '+ @server + '; CREATE USER ' + @user + ' FOR LOGIN ' + @user)
EXECUTE('USE '+ @server + '; ALTER ROLE [db_owner] ADD MEMBER ' + @user)

GO

END TRY
BEGIN CATCH
    DECLARE @msg NVARCHAR(255);
    SET @msg = 'An error occurred: ' + ERROR_MESSAGE();
    RAISERROR (50002, 10, 127);
END CATCH

Without the TRY and CATCH this script was working completely fine. It was run directly on the management Studio.

However how this script is now I get the following error message:

Der Datenbankkontext wurde in "master" geändert.
Msg 102, Level 15, State 1, Server SQL, Line 131
Falsche Syntax in der Nähe von ")".

It suddenly has an error on line 131 which would be the line at the end with CREATE LOGIN after the comment.

Could you please tell me why there is suddenly a problem with this line and how to solve it?