none
Passing db name as a parameter in the command batch file

    Question

  • Hi All,

    I've a batch file which runs the .sql file to drop and create the database along with all the db objects needed(as per need).

    in the .sql file I've hard coded the database name, and from the batch file I call the .sql file to run.

    The issue here is the db name keeps changing every now and then, it is becoming difficult for me to every time go and change the db name in the .sql file,.  "Now I want to pass the db name as a parameter from the batch file" and use the variable in .sql file.

    Can anyone of you help me with the simple script to do this?

    Below is the sample code which calls the .sql file from batch file

    SET DatabaseServer=localhost
    SET DatabaseName=TEST
      sqlcmd -b -S "%DatabaseServer%" -d master -i CreateObject.sql >> "%LogFile%"

    Sample lines from CreateObject.sql file...

    USE [master]
    GO
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST')
    BEGIN
    ALTER DATABASE [TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE  [TEST]
    END
    CREATE DATABASE [TEST]
    GO



    Neil

    Wednesday, March 04, 2015 10:55 PM

Answers

  • Try to put Script_Objects into c:\deploy folder or if you want it in c:\deploy\tables do this

    sqlcmd -b -S "%DatabaseServer%" -d master -i %RootFolder%tables\Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"

    Again, it's a path issue, can't find your Script_objects file

    • Marked as answer by Neilcse Friday, March 06, 2015 4:43 AM
    Thursday, March 05, 2015 11:57 PM

All replies

  • Hi

    You could pass a variable (e.g. var_db) into the file. For example your batch file could look like this

    SET DatabaseServer=localhost
    SET DatabaseName=Sandbox
    sqlcmd -b -S "%DatabaseServer%" -d master -i CreateObject.sql -v var_db = "%DatabaseName%"
    

    And your sql file could reference var_db like this:

    ALTER DATABASE $(var_db) SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Hope that helps

    Wednesday, March 04, 2015 11:30 PM
  • Lead suggested as below ( He just gave a hint)   

    sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  CreateObject.sql  >> "%LogFile%"

    Can I use something like this. where var_db is the name of the db, if yes i'll test this.

    sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  CreateObject.sql  -v var_db >> "%LogFile%"

    --- as of now can ignore  "%LogFile%"



    Neil

    Thursday, March 05, 2015 8:06 PM
  • Hi Neil

    I'm not sure how you're calling the batch file but I think you're best to follow the suggestion in my original post. The -d parameter specifies the initial database that you're going to connect to, master is the right choice in this case as you want to take the other database offline, and you can't do this if you're currently connected to it.

    The -v parameter needs to be set when you're passing it down so the code you hope to use won't work, i.e.

    -v var_db = "%DatabaseName%"

    It essentially replaces any value in CreateObject.sql which is $(var_db) with the one you've set in %DatabaseName%

    So if you can set or pass %DatabaseName% in your original batch file it will work better. 

     

    Thursday, March 05, 2015 8:35 PM
  • Thanks Jm,

    Yes I wast to set the database name in the original batch file

    SET var_db =  SandBox
    SET DatabaseServer = ServerName
    SET DatabaseName = master
    sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  CreateObject.sql  -v var_db >> "%LogFile%"

    Will the above works?

    and one more question, I was correcting the CreateObject.sql to accept db name as variable, the entire file is working fine independently except one statement which is  USE @DBName this one is not working

    USE [master]
    GO
    DECLARE @DBName NVARCHAR(150)
    DECLARE @SQL NVARCHAR(1000)
    SET @DBName = N'MySandBox'
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = @DBName)
    BEGIN
    SELECT @SQL = 'ALTER DATABASE '+@DBName+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    EXECUTE (@SQL)
    SELECT @SQL ='DROP DATABASE '+@DBName
    EXECUTE (@SQL)
    END
    
    SELECT @SQL ='CREATE DATABASE '+@DBName
    EXECUTE (@SQL)
    
    SELECT @SQL ='ALTER DATABASE '+@DBName+ ' SET RECOVERY SIMPLE WITH NO_WAIT'
    EXECUTE (@SQL)
    
    --Need to make changes here this is not working
    --how to code the use db name here which is in varialble
    SELECT @SQL ='USE '+@DBName
    EXECUTE (@SQL)
    select * from information_schema.tables



    Neil

    Thursday, March 05, 2015 9:01 PM
  • Hi Neil

    You don't need to use dynamic sql if you're passing down a value to ObjectCreate via sqlcmd.

    Taking a database with a name TestDB as an example this is what I'd suggest

    Batch file:

    SET DatabaseServer=localhost
    SET DatabaseName=TestDB
    sqlcmd -b -S "%DatabaseServer%" -d master -i CreateObject.sql -v var_db = "%DatabaseName%"

    I'd leave the call to master as is, as it should be where you connect to. The important thing is that you'll be replacing the database name in CreateObject

    CreateObject.sql:

    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'$(var_db)')
    BEGIN
    ALTER DATABASE $(var_db) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE  $(var_db)
    END
    CREATE DATABASE $(var_db)
    GO
    ALTER DATABASE $(var_db) SET RECOVERY SIMPLE WITH NO_WAIT

    So to change the database that you want to drop/re-create you just need to change this line in the batch file, nothing else

    SET DatabaseName=TestDB

    Hope that helps



    • Edited by jmcmullen Thursday, March 05, 2015 9:22 PM typo
    Thursday, March 05, 2015 9:15 PM
  • Getting error it says scripting variable var_db not defined

    SET DatabaseServer=localhost
    SET DatabaseName=SandBox

    sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  Create_Objects.sql -v var_db = "%DatabaseName%" >> "%LogFile%"



    Neil

    Thursday, March 05, 2015 10:09 PM
  • it's working fine for me. can you post the exact contents of your batch and .sql file?
    Thursday, March 05, 2015 10:17 PM
  • I was using the below statement, sorry for the previous post

    sqlcmd -b -S "%DatabaseServer%" -d master -i Create_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"



    Neil

    USE [master]
    GO
    
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'$(var_db)')
    BEGIN
    	ALTER DATABASE $(var_db) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    	DROP DATABASE $(var_db)
    END
    
    CREATE DATABASE $(var_db)
    GO
    
    ALTER DATABASE $(var_db) SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    
    USE $(var_db)
    GO


    • Edited by Neilcse Thursday, March 05, 2015 10:28 PM
    Thursday, March 05, 2015 10:26 PM
  • that looks ok

    Here's the exact code I'm using and that's working for me:

    batch_test.bat

    SET DatabaseServer=localhost
    SET DatabaseName=SandBox
    SET LogFile=c:\temp\log.txt
    
    sqlcmd -b -S "%DatabaseServer%" -d master -i Create_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"
    pause

    Create_Objects.sql

    USE [master]
    GO
    
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'$(var_db)')
    BEGIN
    	ALTER DATABASE $(var_db) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    	DROP DATABASE $(var_db)
    END
    
    CREATE DATABASE $(var_db)
    GO
    
    ALTER DATABASE $(var_db) SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    
    USE $(var_db)
    GO

    it's successfully dropping and re-creating the SandBox database.

    Can you try the exact same code as me to see if you're still having an issue? What version of SQL Server?



    • Edited by jmcmullen Thursday, March 05, 2015 10:56 PM
    Thursday, March 05, 2015 10:49 PM
  • Thanks I am using 2008 R2, below is the error i am getting, if I remove all the added code from the batch and .sql file, it is working fine, I think some were I am messing up with the path by adding this variable var_db

    The system cannot find the path specified.
    Creating database with table script from C:\WINDOWS\system32
    Sqlcmd: 'Create_Objects.sql': Invalid filename.
    17:59:48.64 - ERROR: Unable to process table script [1001]
    Press any key to continue . 


    Neil


    • Edited by Neilcse Thursday, March 05, 2015 11:02 PM
    Thursday, March 05, 2015 10:56 PM
  • ok the sqlcmd switches are the same for 2008R2 and 2012 so it *should* work for you :)
    Thursday, March 05, 2015 11:02 PM

  • The system cannot find the path specified.
    Creating database with table script from C:\WINDOWS\system32

    Looks like it's trying to find your Create_Object.sql file in the system path. Try specifying the full path (c:\myfolder\mysubfolder\Create_Object.sql) in your batch

    Thursday, March 05, 2015 11:08 PM
  • Below the major part of the code in my batch file, the .sql is more or less same as above, here the other Create_ObjectsUpdates.sql file is empty with comments as of now nothing in it. see if I am messing up the path somewhere by adding the var_db variable

    SET DatabaseServer=localhost
    SET DatabaseName=SandBox
    
    SET ThisFile=BatchFileName
    SET RootFolder=%CD%\
    SET LogFile=%RootFolder%%ThisFile%.txt
    
    ECHO ================================================================== > "%LogFile%"
    ECHO %ThisFile% >> "%LogFile%"
    ECHO. >> "%LogFile%"
    ECHO ================================================================== >> "%LogFile%"
    ECHO. >> "%LogFile%"
    ECHO Database Server: %DatabaseServer% >> "%LogFile%"
    ECHO Database Name:   %DatabaseName% >> "%LogFile%"
    ECHO. >> "%LogFile%"
    ECHO ==================================================================
    ECHO %ThisFile%
    ECHO.
    ECHO ==================================================================
    ECHO. 
    ECHO Database Server: %DatabaseServer%
    ECHO Database Name:   %DatabaseName%
    ECHO.
    :: ========================================================================
    :: CREATE DATABASE
    :: ========================================================================
    PUSHD %RootFolder%Tables
    
    ECHO %time% - Creating database with table script from %CD% >> "%LogFile%"
    ECHO %time% - Creating database with table script from %CD%
    
    sqlcmd -b -S "%DatabaseServer%" -d master -i Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"
    
    IF %ERRORLEVEL% NEQ 0 (
      ECHO %time% - ERROR: Unable to process table script [1001] >> "%LogFile%"
      ECHO %time% - ERROR: Unable to process table script [1001]
      GOTO end
    )
    
    ECHO %time% - Updating table defintions with script from %CD% >> "%LogFile%"
    ECHO %time% - Updating table defintions with script from %CD%
    
    sqlcmd -b -S "%DatabaseServer%" -d %DatabaseName% -i  Script_ObjectsUpdates.sql -v var_db=%DatabaseName% >> "%LogFile%"
    
    IF %ERRORLEVEL% NEQ 0 (
      ECHO %time% - ERROR: Unable to update table defintions [1002] >> "%LogFile%"
      ECHO %time% - ERROR: Unable to update table defintions [1002]
      GOTO end
    )


    Neil


    • Edited by Neilcse Thursday, March 05, 2015 11:10 PM
    Thursday, March 05, 2015 11:09 PM
  • ok when I ran it i got this, indicating a problem finding the Script_Objects.sql

    I tweaked the batch, qualifying the path, as follows 

    sqlcmd -b -S "%DatabaseServer%" -d master -i %RootFolder%Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"
    

    The above change assumes the .sql is in the 'root' folder that you specify, you can tweak it from there.

    It worked fine after this! Database re-created... :)

    Thursday, March 05, 2015 11:34 PM
  • now it says 

    Database Server: localhost
    Database Name: SandBox

    18:48:17.19 - Creating database with table script from C:\Deploy\Tables
    Sqlcmd: 'C:\Deploy\Script_Objects.sql': Invalid filename.
    18:48:17.31 - ERROR: Unable to process table script [1001]
    Press any key to continue . . .



    Neil

    Thursday, March 05, 2015 11:52 PM
  • Try to put Script_Objects into c:\deploy folder or if you want it in c:\deploy\tables do this

    sqlcmd -b -S "%DatabaseServer%" -d master -i %RootFolder%tables\Script_Objects.sql -v var_db=%DatabaseName% >> "%LogFile%"

    Again, it's a path issue, can't find your Script_objects file

    • Marked as answer by Neilcse Friday, March 06, 2015 4:43 AM
    Thursday, March 05, 2015 11:57 PM
  • Thank you so very  much Jm,  you made my day! :)

    I saw that, I was about to key in the same!!! Thank you so much, now I copy pasted the .sql files to Deploy folder it worked.

    I don't know how it was working earlier, the .sql files were always in tables folder only, it is only when I added the var_db it stopped working.. 

    I need to learn Shell ( power shell) 

    Was looking to the below site.  I dont even know the basic of % usage etc.. but I can understand the logic by reading the code, just not aware of syntax etc.

    http://www.computerhope.com/batch.htm


    Neil

    Friday, March 06, 2015 12:01 AM
  • No problem!

    Powershell is the way to go alright. Been a while since I've used DOS batch...had to refresh my own memory ;-)

    Friday, March 06, 2015 12:03 AM