Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Wednesday, March 4, 2015 10:55 PM
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
Thursday, March 5, 2015 11:57 PM ✅Answered | 1 vote
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
Wednesday, March 4, 2015 11:30 PM | 1 vote
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
Thursday, March 5, 2015 8:06 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 5, 2015 8:35 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 5, 2015 9:01 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 5, 2015 9:15 PM | 1 vote
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
Thursday, March 5, 2015 10:09 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 5, 2015 10:17 PM | 1 vote
it's working fine for me. can you post the exact contents of your batch and .sql file?
Thursday, March 5, 2015 10:26 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
Thursday, March 5, 2015 10:49 PM | 1 vote
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?
Thursday, March 5, 2015 10:56 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
Thursday, March 5, 2015 11:02 PM
ok the sqlcmd switches are the same for 2008R2 and 2012 so it *should* work for you :)
Thursday, March 5, 2015 11:08 PM | 1 vote
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 5, 2015 11:09 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
Thursday, March 5, 2015 11:34 PM | 1 vote
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 5, 2015 11:52 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
Friday, March 6, 2015 12:01 AM
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 6, 2015 12:03 AM | 1 vote
No problem!
Powershell is the way to go alright. Been a while since I've used DOS batch...had to refresh my own memory ;-)