Using SQLCMD to execute SQL scripts

Do you have a set of SQL scripts that you run all the time? Have you thought about creating a batch file to call these scripts whenever you need them to run? Here is how you would do such a thing.

First, create a folder on your computer and call it something such as scripts and then drop your scripts into the folder (Figure A).

Figure A.

Next, create a batch file that points to the script or scripts that you created (Figure B).

Figure B.

Your script or scripts can be as easy or as complex as you want. Create a set of scripts to help you in your daily life.

Let’s now go ahead and run our batch file (Figure C).

Figure C.

Another script I tend to run a lot is a reset permissions script. Figure D. shows the output.

Note: You can run a sqlcmd /? to get a listing of all your switches (Figure E).

Figure E.


Sample Command:

C:\Documents and Settings\minds>sqlcmd -SMinds8\Minds8 -Usa -P123456 -dSamy -q"s

elect a.LastName,a.Firstname,b.OrderNo,Convert(varchar(10),b.orderdate,103)Order

date,b.Orderprice From dbo.persons as a inner join orders as b on a.p_id=b.p_id"

 -h10 -Y15

LastName        Firstname       OrderNo     Orderdate  Orderprice

--------------- --------------- ----------- ---------- ---------------------

Pettersen       Kari                  77895 02/12/2009             1000.0000

Pettersen       Kari                  44678 02/12/2009             1600.0000

Hansen          Ola                   22456 02/12/2009              700.0000

Hansen          Ola                   24562 02/12/2009              300.0000

Hansen          Ola                   34764 02/12/2009             2000.0000

Nilsen          Johan                 12345 02/12/2009              100.0000

Nilsen          Johan                 12345 02/12/2010              100.0000

Nilsen          Johan                 12345 02/12/2008              100.0000

Hansen          Ola                   12345 02/12/2010              100.0000

(9 rows affected)

1> quit

C:\Documents and Settings\minds>