.bat files generate sql scripts in command prompt. Is it possible? Can I transmit parameters to the sql files?

• Stefan Ardeleanu

  Mr or Mrs. 500

  Points: 501

  More actions

  May 26, 2004 at 3:35 am

  #108191

  I would like to make a .bat file. This .bat file will generate a database and all the database objects. I have some sql files for that. This .bat must opejn and execute in some way all these .sql files. First of all, I would like to name the database in command prompt at runtime.

  The .bat file is something like that (I tried with isqlw too):

  set db_name=%1

  osql -S STEFANA\STEFANA -d Northwind -U sa -P sivadoc -i Input_Test.sql %db_name% -o Output_Test.txt

  In the input file I would like to transmit the database name. Is it possible? If yes, please guve me a good advice. If no, please tell me. I am not very goog in .bat files so it may be a silly question. I also don't use too aften command prompt utilities like osql, isql and isqlw.

  Thanks.  

• schleep

  SSChampion

  Points: 12586

  More actions

  May 27, 2004 at 6:40 am

  #507993

  Hi Stefan,

  Of course you can pass the name of a new DB. You'll need a way to create it -- this would likely be a stored procedure which accepts the DB name as a parm.

  To follow your example, your batch file would look something like:

  set db_name=%1

  REM Run the script for stored procedure which creates DBs.

  osql -S STEFANA\STEFANA -d Northwind -U sa -P sivadoc -i CreateDB.sql -o Output_Test.txt

  REM Call create DB proc, passing new DB name.

  osql -S STEFANA\STEFANA -d Northwind -U sa -P sivadoc -Q "EXECUTE CreateMyDB '%db_name%'" -o Output_Test.txt

  REM DB created. Now, create objects in new DB.

  REM -d switch now points at new DB.

  osql -S STEFANA\STEFANA -d %db_name% -U sa -P sivadoc -i CreateDBObjects.sql -o Output_Test.txt

  etc...

  Hope this helps,

  P

• Stefan Ardeleanu

  Mr or Mrs. 500

  Points: 501

  More actions

  May 27, 2004 at 6:59 am

  #507995

  Thanks. Finally I choose to run a first batch in which I create a stored procedure and a table in master. IN that table I insert the database name, datafile, size and collate. HAving them in that table in master database I can take them and create in a string the database. All these will be in a setup. Thanks anyway for your ghelp.