0

I am creating a batch file where I am restoring a database from an IP address and then executing a couple .sql files onto the database. In a couple of the .sql files there are variables declared and set. But this process has to be done on many machines with different values for each variable in each machine.

So I'm able to restore the database through user input of the IP, but I'm not sure how to use the batch script command to change the variable values.

For example, in one of the .sql files, a variable @store was declared and set to some random number. I want to change that number through the batch file.

I am using windows and sql server express 2008 r2

DigCamara
  • 5,540
  • 4
  • 36
  • 47
Alkey29
  • 189
  • 1
  • 5
  • 20

2 Answers2

4

You can use "scripting variables" with SQLCMD.

Here's an example from that MSDN page:

You can also use the -v option to set a scripting variable that exists in a script. In the following script (the file name is testscript.sql), ColumnName is a scripting variable.

USE AdventureWorks2012;
SELECT x.$(ColumnName)
FROM Person.Person x
WHERE c.BusinessEntityID < 5;

You can then specify the name of the column that you want returned by using the -v option:

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

To return a different column by using the same script, change the value of the ColumnName scripting variable.

sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
1

If you are working on a Unix / Linux system, you can use sed to search a string.

Example: Assuming you need to replace 127.0.0.1 to 192.168.1.1, you can use the following instruction:

$ sed 's/127.0.0.1/192.168.1.1/g' script.sql > newScript.sql

This will replace the old ip in script.sql and will save a copy of this script in newScript.sql.

On windows, I don't know how to do it, but you can always download and install Cygwin to do exactly as above.

Hope this helps you.

Barranka
  • 20,547
  • 13
  • 65
  • 83
  • Cygwin is absolutely unnessary to get the GNU/Linux tools in Windows. There are several native ports of them that don't require Cygwin –  Feb 06 '13 at 22:36
  • @a_horse_with_no_name you know of another way to use sed in windows?\ – Alex Gordon Feb 06 '13 at 22:44
  • 1
    @a_horse_with_no_name can you please share them with us? I'm a Linux user (and fan), but it would be good yo know those tools – Barranka Feb 06 '13 at 23:00
  • @Barranka: http://gnuwin32.sourceforge.net/ and http://unxutils.sourceforge.net/ –  Feb 07 '13 at 07:29