Batch Execute SQL files

Abstract: in many cases, a large number of. SQL files need to be executed one by one during system upgrade, which is inconvenient. Considering the execution sequence and convenience of customer service, can we find a simple way to execute these SQL files in batches?

Main content:

1. Preparations
2. Execute SQL files in batches using osql/ocmd commands
3. UseMaster..Xp_mongoshellCall osql/ocmd commands for Stored Procedures
4. Summary

I. Preparations

To execute SQL files in batches, create two SQL files: 1. SQL and 2. SQL.

1. SQL content:

 

Insert IntoDBO. Products (productname)Values('1. SQL _'+Cast(Getdate()As Nvarchar(10)))
 

2. Content in SQL:

 

Insert IntoDBO. Products (productname)Values('2. SQL _'+Cast(Getdate()As Nvarchar(10)))
 

Ii. Batch Processing of SQL files in batches using osql/ocmd

If you need to execute SQL in the command line, you need to use the osql command. Of course, this tool is included with SQL Server. Its usage is as follows:

Note: osql does not support all functions of SQL Server 2008. If you need to use all functions of SQL Server 2008, you can use the ocmd command.

With the osql/ocmd command, to execute SQL files in batches, you must write a batch processing command. For example, if you execute the preceding SQL file, you can write the following statement in the TXT file:

 

 Osql  -  S"  127.0  .  0.1  "  -  U "sa" -  P "sa"  -  D "northwind"  -  I"  %  CD  %  \  1  . SQL"
Osql  -  S"  127.0  .  0.1 "  -  U "sa"  -  P "sa"  -  D "northwind"  -  I"  %  CD  %  \  2  . SQL" 

Save SQL. bat to the same file as above. 1. SQL and 2. SQL. Double-click SQL. bat during execution.

Is the result before and after running:

3. Use the stored procedure of master .. xp_cmdshell to call the osql/ocmd command.

We used batch processing for batch execution of SQL files.ProgramOf course, you can call the batch processing file, but we prefer to use SQL more often.Master..Xp_mongoshellThis stored procedure.Master..Xp_mongoshellThis stored procedure allows us to call command shell using SQL statements. Of course, what we can do in cmd is basically used.Master..Xp_mongoshellIt can also be done.

when using master .. xp_mongoshell before that, we must configure the server security, if SQL server is not configured, the following error is thrown:

SQL server blocks components 'xp _ javasshell' Process 'sys. xp_mongoshell' is disabled because this component has been disabled as part of the server security configuration. The system administrator can use sp_configure enable 'xp _ javasshell' . Enable 'xp _ javasshell' For more information, see SQL server " peripheral application configurator " .

There are many configuration methods. Here we use SQL to perform security configuration (note that we should disable it after calling master .. xp_mongoshell for security reasons). Below we modify the Security Configuration and useMaster..Xp_mongoshellSQL statement for executing the osql/ocmd command:

 

 --  Enable Configuration  
  Exec  SYS. sp_configure  '  Show advanced options  '  ,  1  
 Go  
  --  Install  
  Reconfigure  
  Go  
  --  Configure xp_cmdshell to be available  
  Exec  SYS. sp_configure  '  Xp_mongoshell  ' ,  1  
  Go  
  --  Install  
  Reconfigure  
  Go  
  --  Run the xp_cmdshell command  
  Exec  Master .. xp_mongoshell '  Osql-U sa-P Sa-D northwind-I f: \ CSHARP \ timingjobs \ 1. SQL  '  
  Exec  Master .. xp_mongoshell  '  Osql-U sa-P Sa-D northwind-I f: \ CSHARP \ timingjobs \ 2. SQL  '  
  Go  

  --  Enable Configuration  
  Exec SYS. sp_configure  '  Show advanced options  '  ,  1  
  Go  
  --  Install  
  Reconfigure  
  Go  
  -- Configure xp_cmdshell as unavailable  
  Exec  SYS. sp_configure  '  Xp_mongoshell  '  ,  0  
  Go  
  --  Install  
  Reconfigure  
 Go 

Iv. Summary

Today we mainly talk about how to use osql/ocmd in command line and SQL (using Master .. xp_mongoshell) Execute SQL files in batches. In fact, osql/ocmd is much more useful than that. If you are interested, you can study it yourself.

BenWorksUse the Knowledge Sharing signature 2.5 mainland China License Agreement for licensing. You are welcome to reprint, interpret, or use it for commercial purposes. However, please indicate that the record is from Cui jiangtao (kenshincui) and contains related links.