Необходимо объявить скалярную переменную "@varname" в скрипте создания базы данных

Question

Я пытаюсь создать скрипт для базовой базы данных и создания логина / пользователя для SqlServer. Когда я запускаю свой скрипт, я получаю много ошибок, таких как:

Должен объявить скалярную переменную "@varname"

когда переменная используется в EXEC заявление во второй раз во время выполнения скрипта.

declare
     @dbname nvarchar(50) ,
     @userName nvarchar(50) ,
     @password nvarchar(50);
SET @dbname = N'DBNAME';
SET @userName = N'DBUSERNAME';
SET @password= N'PASSWORD';

Begin transaction
    IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
    BEGIN
        exec ('
            DROP login '+ @userName + '
            DROP USER '+  @userName + '
            alter database '+  @dbname + '
            SET OFFLINE 
            WITH ROLLBACK IMMEDIATE;
            ALTER DATABASE '+  @dbname + '
            SET ONLINE;
            DROP DATABASE '+  @dbname)
    END 

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    EXEC ('CREATE DATABASE '+ @dbname)
    GO 
    EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''')
    GO 
    EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100')
    GO 
     ...
    EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF')
    GO

    EXEC('create login '+ @userName + 'with password = '+ @password);
    EXEC('use  '+ @dbname+ '
    create user '+ @userName + ' for login '+ @userName);
    EXEC ('EXEC sp_addrolemember  ''db_owner'', '+  @userName)

commit transaction

Должен ли я использовать sp_executesql для каждого запроса? Есть ли более короткое решение?

Answer 1

Благодаря комментариям Радж и Вайбхава Пармара я изменил свой сценарий, и теперь он работает нормально.

Основной ошибкой был оператор GO, который следует удалить, поскольку он используется для группировки команд SQL в пакеты, которые отправляются на сервер вместе. Команды, включенные в пакет, то есть набор команд со времени последней команды GO или начала сеанса, должны быть логически согласованы. Вы не можете определить переменную в одном пакете, а затем использовать ее в другом, поскольку область действия переменной ограничена пакетом, в котором она определена.

Вторая ошибка заключалась в том, что я пытался использовать транзакцию с оператором, который не позволяет им

Я оставляю исправленный скрипт в качестве ссылки:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

declare
     @dbname nvarchar(50) ,
     @userName nvarchar(50) ,
     @password nvarchar(50);
SET @dbname = N'DATABASENAME';
SET @userName = N'DBUSERNAME';
SET @password= N'DBUSERPASSWORD';

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
    exec ('
    DROP USER '+  @userName + '
    DROP login '+ @userName + '
    alter database '+  @dbname + '
    SET OFFLINE 
    WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE '+  @dbname + '
    SET ONLINE;
    DROP DATABASE '+  @dbname)
END 

EXEC ('CREATE DATABASE '+ @dbname);
EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''');
EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULL_DEFAULT OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_NULLS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_PADDING ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CLOSE OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_SHRINK OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET QUOTED_IDENTIFIER ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET RECOVERY FULL');
EXEC ('ALTER DATABASE '+ @dbname + ' SET PAGE_VERIFY CHECKSUM');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ANSI_WARNINGS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET ARITHABORT ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_CREATE_STATISTICS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS ON');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_CLOSE_ON_COMMIT OFF');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CURSOR_DEFAULT  GLOBAL');
EXEC ('ALTER DATABASE '+ @dbname + ' SET CONCAT_NULL_YIELDS_NULL OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET NUMERIC_ROUNDABORT OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET RECURSIVE_TRIGGERS OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  ENABLE_BROKER'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET DATE_CORRELATION_OPTIMIZATION OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET TRUSTWORTHY OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET ALLOW_SNAPSHOT_ISOLATION OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET PARAMETERIZATION SIMPLE'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET READ_COMMITTED_SNAPSHOT OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET HONOR_BROKER_PRIORITY OFF'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  READ_WRITE'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET  MULTI_USER'); 
EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF');

EXEC ('create login '+  @userName + ' with password = '''+ @password+ ''', default_database = ' + @dbname);
EXEC ('use  '+ @dbname+ ' create user '+ @userName + ' for login '+ @userName);
EXEC ('use  '+ @dbname+ ' EXEC sp_addrolemember  ''db_owner'', '+  @userName);

Answer 2

Оператор GO сообщает анализатору запросов, что пакет завершен.

http://technet.microsoft.com/en-us/library/ms188037.aspx

Следовательно, объявленные переменные, которые установлены, выходят за рамки времени выполнения динамического кода.

Если вы действительно хотите это сделать в транзакции, оберните ее с помощью BEGIN TRY/END TRY в BEGIN CATCH / END CATCH, выполните ROLLBACK.

http://craftydba.com/?p=5930

Я никогда не пробовал это с оператором CREATE DATABASE. Это может быть забавным упражнением. Отменяет ли это создание базы данных? Что-то, чтобы добавить в мой список ведра, чтобы попробовать.

Также вам нужно использовать точку с запятой (;) при объединении нескольких команд. В противном случае вы получите синтаксическую ошибку.

Answer 3

Просто используйте приведенный ниже код, это может быть полезно:

declare @dbname nvarchar(50) ,@userName nvarchar(50) ,@password nvarchar(50);
SET @dbname = N'DBNAME';
SET @userName = N'DBUSERNAME';
SET @password= N'PASSWORD';

Begin transaction
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
    exec ('
        DROP login '+ @userName + '
        DROP USER '+  @userName + '
        alter database '+  @dbname + '
        SET OFFLINE 
        WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE '+  @dbname + '
        SET ONLINE;
        DROP DATABASE '+  @dbname)
END 
   else
   begin
--SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
EXEC ('CREATE DATABASE '+ @dbname)
--GO 
EXEC (@dbname+ '.[dbo].[sp_fulltext_database] @action = ''disable''')
--GO 
EXEC ('ALTER DATABASE '+ @dbname + ' SET COMPATIBILITY_LEVEL = 100')
--GO 

EXEC ('ALTER DATABASE '+ @dbname + ' SET DB_CHAINING OFF')
--GO

EXEC('create login '+ @userName + 'with password = '+ @password);
EXEC('use  '+ @dbname+ '
create user '+ @userName + ' for login '+ @userName);
EXEC ('EXEC sp_addrolemember  ''db_owner'', '+  @userName)
end

commit transaction