Execute SQL scripts using external commands in SQL Server

server| Script | execute
Author:david Euler
Date:2004/09/28
email:de_euler-david@yahoo.com.cn

Have any questions, please contact me:


Sometimes we need to run all the SQL scripts (possibly txt or SQL text files) in a folder to update the schema more than once, so you can use multiple statements to execute the SQL script. --The following query runs in SQL query, and executes the specified SQL script under D:\vss, which is the suffix of txtexec master. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_030311.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_030416.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_030417.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_030513.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_030613.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_031010.txt '
EXEC master.. xp_cmdshell ' isql-u sa-p 123-i d:\vss\MSSchema_050810.txt ' exec master. xp_cmdshell ' isql-u sa-p 123-i d:\vss\TaxInvoice.sql ' Of course, we can simply execute these commands in a batch file Updateschema.cmd without using the stored procedure, which is the following UPDA Teschema.cmd:; The following command executes the specified SQL script under D:\vss: Isql-u sa-p 123-i d:\vss\MSSchema.txt
Isql-u sa-p 123-i D:\vss\MSSchema_030311.txt
Isql-u sa-p 123-i D:\vss\MSSchema_030416.txt
Isql-u sa-p 123-i D:\vss\MSSchema_030417.txt
Isql-u sa-p 123-i D:\vss\MSSchema_030513.txt
Isql-u sa-p 123-i D:\vss\MSSchema_030613.txt
Isql-u sa-p 123-i D:\vss\MSSchema_031010.txt
Isql-u sa-p 123-i d:\vss\MSSchema_050810.txt Note: 1.exec statements are used to execute stored procedures, xp_cmdshell is an extended stored procedure in the master database that can be used to execute strings of system commands, For example, exec master.   xp_cmdshell ' dir *.exe ' view all exe files in the current directory. 2.isql is a utility for SQL Server that understands the process of an external program that, if SQL uses the default installation, can be C:\Program Files\Microsoft SQL Server\80\tools\ Binn directory to find this Isql.exe file, isql can be used to execute Transact-SQL statements, stored procedures, and script files, parameter-u to specify the login server username,-p to specify the password, parameter-i is used to specify the script file to read execution, parameter-o to specify the file to save the output Name. 3. Several common databases, such as other oracle,db2,mysql,postgresql, also have corresponding tools to achieve similar functions.