Why do i get this error message ?

  • Hi,

    i try to create a script that drops a database and import a backup to create it again.

    Now, i get a stupid error message, i think i missed something in sql syntax..

    here is what i created :

    ==================================

    DECLARE @DB_Name varchar(128)

    DECLARE @DB_Bak_file varchar(128)

    DECLARE @DB_Import_file varchar(128)

    DECLARE @DB_Datas_Path varchar(260)

    DECLARE @DB_Logs_Path varchar(260)

    DECLARE @DB_Login varchar(128)

    DECLARE @DB_Password varchar(128)

    DECLARE @SQLString nvarchar (2000)

    -- Nom de la base de données à créer --

    SET @DB_Name = 'PROD'

    -- Chemin du backup temporaire de l'ancienne base --

    SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'

    -- Chemin du fichier de backup à importer --

    SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'

    -- Chemin du fichier des DATAS de la base --

    SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'

    -- Chemin du fichier des DATAS de la base --

    -- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'

    SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'

    -- Login utilisé par l'accès via l'espace client --

    SET @DB_Login = 'usr_app_eclient'

    -- Nom de la base de données à créer --

    SET @DB_Password = 'ZePa$$w0rd'

    USE master

    GO

    -- Suppression de la base de données actuelle

    SET @SQLString = 'DROP DATABASE ' + @DB_Name

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    ============================================

    Here is the error message (sorry, it's in French...):

    ============================================

    Serveur : Msg 137, Niveau 15, État 2, Ligne 2

    La variable '@DB_Name' doit être déclarée.

    Serveur : Msg 137, Niveau 15, État 1, Ligne 3

    La variable '@SQLString' doit être déclarée.

    Serveur : Msg 137, Niveau 15, État 1, Ligne 4

    La variable '@SQLString' doit être déclarée.

    ============================================

    Thanks for your help, i'm really a beginner at SQL scripting...

    Florent

  • USE master

    GO

    DECLARE @DB_Name varchar(128)

    DECLARE @DB_Bak_file varchar(128)

    DECLARE @DB_Import_file varchar(128)

    DECLARE @DB_Datas_Path varchar(260)

    DECLARE @DB_Logs_Path varchar(260)

    DECLARE @DB_Login varchar(128)

    DECLARE @DB_Password varchar(128)

    DECLARE @SQLString nvarchar (2000)

    -- Nom de la base de données à créer --

    SET @DB_Name = 'PROD'

    -- Chemin du backup temporaire de l'ancienne base --

    SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'

    -- Chemin du fichier de backup à importer --

    SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'

    -- Chemin du fichier des DATAS de la base --

    SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'

    -- Chemin du fichier des DATAS de la base --

    -- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'

    SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'

    -- Login utilisé par l'accès via l'espace client --

    SET @DB_Login = 'usr_app_eclient'

    -- Nom de la base de données à créer --

    SET @DB_Password = 'ZePa$$w0rd'

    -- Suppression de la base de données actuelle

    SET @SQLString = 'DROP DATABASE ' + @DB_Name

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

     

    Try it out

  • ok, it worked, now i get a new error few lines after :

    -- Creation de la nouvelle base de données

    SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + @DB_Datas_Path + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + @DB_Logs_Path + ' )'

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

     

    What is the rule concerning the ' character ?

    thanks

    Florent

  • SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    Try it out and you you know about the Single quote

  • DECLARE @DBFileName VARCHAR(100)

    DECLARE @SQLString VARCHAR(500)

    SELECT @DBFileName = 'D:\SQL2000\Data\xxx.mdf'

    SELECT @SQLString = 'FILE = '+@DBFileName

    PRINT @SQLString

    -- The Syntax is FILE = 'D:\SQL2000\Data\xxx.mdf'

    -- Hence

    SELECT @SQLString = 'FILE = '''+@DBFileName+''''

    -- or

    SELECT @SQLString = 'FILE = '+QUOTENAME(@DBFileName,'''')

    PRINT @SQLString

     

  • Very strange, there is something i must miss somewhere :

     

    USE master

    GO

    DECLARE @DB_Name varchar(128)

    DECLARE @DB_Bak_file varchar(128)

    DECLARE @DB_Import_file varchar(128)

    DECLARE @DB_Datas_Path varchar(260)

    DECLARE @DB_Logs_Path varchar(260)

    DECLARE @DB_Login varchar(128)

    DECLARE @DB_Password varchar(128)

    DECLARE @SQLString nvarchar (2000)

    -- Nom de la base de données à créer --

    SET @DB_Name = 'PROD'

    -- Chemin du backup temporaire de l'ancienne base --

    SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'

    -- Chemin du fichier de backup à importer --

    SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'

    -- Chemin du fichier des DATAS de la base --

    SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'

    -- Chemin du fichier des DATAS de la base --

    -- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'

    SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'

    -- Login utilisé par l'accès via l'espace client --

    SET @DB_Login = 'usr_app_eclient'

    -- Nom de la base de données à créer --

    SET @DB_Password = 'ZePa$$w0rd'

    -- Suppression de la base de données actuelle

    SET @SQLString = 'DROP DATABASE ' + @DB_Name

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    -- Creation de la nouvelle base de données

    SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

     

    Won't work and asks me to declare the @DB_Name variable...

    My goal was to declare this at the top of the script only 1 time.

    Now, if i add the dECLARE lines and the SET lines just before the "SET @SQLString = 'CREATE DATABASE " line, all is ok...

    Any idea ?

  • When you use GO, the script changes 'scope'. Your @DB_Name is no longer declared in the new scope.

    Refer to the BOL, use the Index tab, enter Variables, then select the option Transact SQL. Then in the pop-up window, select Transact SQL Variables.

    -SQLBill

  • Actually, upon re-reading, I didn't state it very correctly. Your issue is that a variable is only good for one batch or stored procedure.

    This is one batch:

    -- Suppression de la base de données actuelle

    SET @SQLString = 'DROP DATABASE ' + @DB_Name

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    This is a second batch:

    -- Creation de la nouvelle base de données

    SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    -SQLBill

  • Sorry Bill, i'm not sure to understand.

    My goa lis t ocreate 1 only sql script that will perform many tasks in order to:

    delete a nexisting Database

    Create a new daztabase with the same name

    restaure that database using a dump created on another server

    create correct access logins/rights to new created database....

    My goal was to have variables only at the top of the script in order to be able to quickly change one or more variable for the entire script (as i do in vb script for example)

    Florent

  • ok, i understood, i just have removed all the "GO" statments and all seems to work now ;-))

    But should i add a "GO" anywhere then ?

  • No. A GO isn't really necessary. It just allows you to break a long script into separate batches. One way to look at it...a GO is similar to a COMMIT. When SQL Server comes to a GO, it commits that transaction and starts the next one.

    -SQLBill

  • ok, now that i understand the syntax, could someone please help me giving me the order / commands to run to :

    create a new database using a dump made on another server (*.bak file) and forcing the location + name of *.mdf and *.ldf file.

    thanks a lot.

     

    Here is my actual script :

    ============================

    USE master

    GO

    set nocount on

    DECLARE @DB_Name varchar(128)

    DECLARE @DB_Bak_file varchar(128)

    DECLARE @DB_Import_file varchar(128)

    DECLARE @DB_Datas_Path varchar(260)

    DECLARE @DB_Logs_Path varchar(260)

    DECLARE @DB_Login varchar(128)

    DECLARE @DB_Password varchar(128)

    DECLARE @SQLString nvarchar (2000)

    -- Nom de la base de données à créer --

    SET @DB_Name = 'PROD'

    -- Chemin du backup temporaire de l'ancienne base --

    SET @DB_Bak_file = 'd:\mssql2000\MSSQL\BACKUP\tmp_DB_DAISY-BAK_PROD.bak'

    -- Chemin du fichier de backup à importer --

    SET @DB_Import_file = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'

    -- Chemin du fichier des DATAS de la base --

    SET @DB_Datas_Path = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'

    -- Chemin du fichier des DATAS de la base --

    -- SET @DB_Logs_Path = 'e:\Logs\PROD_Logs.LDF'

    SET @DB_Logs_Path = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'

    -- Login utilisé par l'accès via l'espace client --

    SET @DB_Login = 'usr_app_eclient'

    -- Nom de la base de données à créer --

    SET @DB_Password = 'ZePa$$w0rd'

    -- Restriction d'accès au DBO et sysadmins seulement

    SET @SQLString = 'ALTER DATABASE ' + @DB_Name + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'

    PRINT @SQLString

    EXECUTE(@SQLString)

    -- Suppression de la base de données actuelle

    SET @SQLString = 'DROP DATABASE ' + @DB_Name

    PRINT @SQLString

    EXECUTE(@SQLString)

    -- Creation de la nouvelle base de données

    SET @SQLString = 'CREATE DATABASE ' + @DB_Name + ' ON ( NAME = PROD_datas, FILENAME = ' + QUOTENAME(@DB_Datas_Path,'''') + ' ) LOG ON ( NAME = PROD_logs, FILENAME = ' + QUOTENAME(@DB_Logs_Path,'''') + ' )'

    PRINT @SQLString

    EXECUTE(@SQLString)

    -- Restriction d'accès au DBO et sysadmins seulement

    SET @SQLString = 'ALTER DATABASE ' + @DB_Name + ' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'

    PRINT @SQLString

    EXECUTE(@SQLString)

    -- Restauration de la liste des fichiers associés à la base

    SET @SQLString = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@DB_Import_file,'''')

    PRINT @SQLString

    EXECUTE(@SQLString)

    -- Restauration des datas de la base

    SET @SQLString = 'RESTORE DATABASE ' + @DB_Name + ' FROM DISK = ' + QUOTENAME(@DB_Import_file,'''') + ' WITH RECOVERY, MOVE ''PROD_datas'' TO ' + QUOTENAME(@DB_Datas_Path,'''') + ' , MOVE ''PROD_logs'' TO ' + QUOTENAME(@DB_Logs_Path,'''')

    PRINT @SQLString

    EXECUTE(@SQLString)

    GO

    ============================

    Here is the output of this actual script :

    ============================

    ALTER DATABASE PROD SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

    DROP DATABASE PROD

    Suppression du fichier de base de données 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'.

    Suppression du fichier de base de données 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF'.

    CREATE DATABASE PROD ON ( NAME = PROD_datas, FILENAME = 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF' ) LOG ON ( NAME = PROD_logs, FILENAME = 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF' )

    Le processus CREATE DATABASE alloue 0.75 Mo sur le disque 'PROD_datas'.

    Le processus CREATE DATABASE alloue 1.00 Mo sur le disque 'PROD_logs'.

    ALTER DATABASE PROD SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

    RESTORE FILELISTONLY FROM DISK = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak'

    RESTORE DATABASE PROD FROM DISK = 'd:\mssql2000\MSSQL\BACKUP\DB_DAISY_PROD.bak' WITH RECOVERY, MOVE 'PROD_datas' TO 'd:\mssql2000\MSSQL\data\PROD_Datas.MDF' , MOVE 'PROD_logs' TO 'd:\mssql2000\MSSQL\log\PROD_Logs.LDF'

    Serveur : Msg 3234, Niveau 16, État 2, Ligne 1

    Le fichier logique 'PROD_datas' ne fait pas partie de la base de données 'PROD'. Utilisez RESTORE FILELISTONLY pour lister les noms de fichier logiques.

    Serveur : Msg 3013, Niveau 16, État 1, Ligne 1

    RESTORE DATABASE s'est terminé anormalement.

    ============================

    Florent

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply