Dynamic execute database script from DOS

Question

Hello Friends, 

I am struggling to solve  an issue for executing sql script from dos command,

 

CREATE DATABASE Security
 ON
 (
 NAME = N'Security',
 FILENAME =@testvarMdf,
 SIZE = 3,
 FILEGROWTH = 50MB
 )
 LOG ON
 (
 NAME = N'Security_log',
 FILENAME =@testvarLog ,
 SIZE = 1,
 FILEGROWTH = 50MB
 ) 
GO

 

Is there any way to pass dynamic FILENAME parameter from dos command, I am getting error in script if I use variable or ${variable}.

FILENAME should be pass dynamic at both places (testvarMdf and @testvarLog)

 

can you please help me out? Thanks.

---

Tej http://tejzatms.blogspot.com/

Answer 1

Why do you want to do this from DOS? Are you using sqlcmd?

In any case, you need to use dynamic SQL for this.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Yes, I am using sqlcmd,  I am creating one click solution for installing Schema, Proc, View,Jobs etc.  in one click. 

but in my schema installation script have to make MS Sql location path dynamic base on server installed path.

I prefer not to use dynamic SQL, Is there any other way ?

---

Tej http://tejzatms.blogspot.com/

Answer 3

create one batch file below mentioned db_security.bat

ECHO Script will execute then pause prior to completion to identify run-recording requirements
ECHO Hit ENTER to Continue
PAUSE
ECHO .
ECHO .

sqlcmd -e -i DB_security.sql -o DB_security.txt -d databasename -E

ECHO .
ECHO .
ECHO Please copy output text files

ECHO Hit 'ENTER' to finish script
PAUSE

after create the text file create sql with the name DB_security.sql

CREATE DATABASE Security
ON
(
NAME = N'Security',
FILENAME =@testvarMdf,
SIZE = 3,
FILEGROWTH = 50MB
)
LOG ON
(
NAME = N'Security_log',
FILENAME =@testvarLog ,
SIZE = 1,
FILEGROWTH = 50MB
)
GO

go to command prompt

go to the directory where do you kept the files

execute the batch file

c:\sekhara> db_security.bat

press enter it will execute

Answer 4

Thanks, but getting same error at @testvarMdf in text file.  how can I pass dynamic path?

---

Tej http://tejzatms.blogspot.com/

Answer 5

I don't know any other way except for dynamic.

Also you can do this from SSIS, I think.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 6

How can I use search and replace command using DOS ? pure dos command not third party

 

---

Tej http://tejzatms.blogspot.com/

Answer 7

for passing dynamic path you need to specify the dos parameters in the batch file like below

%1 %2 %3 etc.. see the below example dynamic parameters

ECHO Script will execute then pause prior to completion to identify run-recording requirements
ECHO Hit ENTER to Continue
PAUSE
ECHO .
ECHO .

sqlcmd -e -i %1 -o %2 -d databasename -E

ECHO .
ECHO .
ECHO Please copy output text files

ECHO Hit 'ENTER' to finish script
PAUSE

execute the command like below

c:\>c:\sekhara\db_security.bat c:\sekhara\sql\db_security.sql c:\sekhara\output\db_security.txt

 

entire path you can provide for the .sql file and output text file aswell it will execute that means

sqlcmd -e -i c:\sekhara\DB_security.sql -o c:\sekhara\DB_security.txt -d databasename -E

Answer 8

I don't know from the top of my head. Quick google search brings this thread http://www.computing.net/answers/programming/search-and-replace-in-dos/14876.html

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 9

CREATE DATABASE Security
ON
(
NAME = N'Security',
FILENAME =$(MDF) ,
SIZE = 3,
FILEGROWTH = 50MB
)
LOG ON
(
NAME = N'Security_log',
FILENAME =$(LDF) ,
SIZE = 1,
FILEGROWTH = 50MB
) 
GO

SET ECHO OFF
ECHO .

SET MDF="E:\TEMP\X.MDF"
SET LDF="E:\TEMP\X.LDF"

sqlcmd -e -i DB_security.sql -o DB_security.txt -s[sORNA-PC] -E

Save the SQL as db_security.sql

Save the Batch as anyname.bat.

Run it from command prompt and it will install the database.

 

Answer 10

You should change the server name From SORNA-PC to your servername

Also if still you want to pass the paths to the batch you can do as below.Save the follow code as batch file SET ECHO OFF
ECHO .

SET MDF=%1
SET LDF=%2

sqlcmd -e -i DB_security.sql -o DB_security.txt -s[sORNA-PC] -E

call the batch as shown below with 2 parameters

DBINSTALL "e:\temp\x.mdf" "e:\temp\x.ldf"

On a side note you can parametarize the server also by adding one more variable and parameter

Answer 11

In msdos you have editor, open your text file in editor you can modify the content using search and replace commands in that editor

c:\sekhara>edit file_name

it will open editor

In search tab you will find the replace option

Answer 12

Thanks Sorna and Noam,

Finally I make it works like, Test.sql

:SETVAR MDFPath "'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TESTDB.MDF'" :: Default Path
:SETVAR LDFPath "'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TESTDB_log.LDF'" :: Default Path

 
CREATE DATABASE TESTDB
ON
(
	NAME = N'TESTDB',
	FILENAME =$(MDFPath) ,
	SIZE = 3,
	FILEGROWTH = 50MB
)
LOG ON
(
	NAME = N'TESTDB_log',
	FILENAME =$(LDFPath) ,
	SIZE = 1,
	FILEGROWTH = 50MB
) 

and then command line  install.cmd

sqlcmd -i C:\Test\T\test.sql -v MDFPath="'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL\DATA\TESTDB.MDF'" -v LDFPath="'C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL\DATA\TESTDB_log.LDF'"

let me know if anything wrong here or any suggestion. 

 

Thanks for prompt reply.

---

Tej http://tejzatms.blogspot.com/