sqlsql-servercontinuous-integrationnant

How to check for databases on a SQL Server using NANT and executing a file on each


I would like to create a task in nAnt that iterates though the names of databases in a server and as it finds them runs a sql script on each"

For example

Databases
    Test_0001
    Test_0002
    Test_0003

and I have a SQL script that adds a column to a table named Customers in any of those databases. The problem I have is that I don't seem to find an example where I can iterate through each database on the server and then execute the task.

I found how to iterate through the SQL files folder and execute it but not sure about reading db instances in the server.


Solution

  • Define a property with your server instance and databases names.

    <property name="SQLInstance" value="MySQLServer">
    <property name="databases" value="Test_0001,Test_0003,Test_0003" />
    
          <foreach item="String" in="${databases}" delim="," property="Database">
                    <echo message="Running my scripts on the databases..." />
                  <exec program="sqlcmd" commandline="-b -s ${SQLInstance}" -o ${Database}.log -d ${Database}
          </foreach>
    

    That would be the best approach to take.