Batch Execute SQL files

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. Use the master. xp_cmdshell stored procedure to call the osql/ocmd command
4. Summary

I. Preparations

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

1. SQL content:

 

INSERTINTO dbo.Products ( ProductName ) VALUES  ( '1.sql_'+CAST(GETDATE() ASNVARCHAR(10)) )

2. Content in SQL:

 

INSERTINTO dbo.Products ( ProductName ) VALUES  ( '2.sql_'+CAST(GETDATE() ASNVARCHAR(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 to execute SQL files in batches. How can we do this in the program? Of course, we can call the batch processing file, however, we prefer to use SQL more often, so we have to mention the master .. the stored procedure xp_mongoshell. The stored procedure of master.. xp_cmdshell allows us to call command shell in the form of SQL statements. Of course, we can basically use master. xp_cmdshell in cmd.

Before using the master .. xp_mongoshell, we must perform security configuration on the server. If we do not configure SQL server, the following error will be thrown:

SQL Server blocks access to 'sys. xp_mongoshell' during the 'xp _ mongoshell' process, because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'xp _ javasshell' by using sp_configure '. For more information about enabling 'xp _ external shell', see "peripheral application configurator" in SQL Server books online ".

There are multiple configuration methods. Here we use SQL to perform security configuration (note that the master is called for security reasons .. xp_mongoshell), the following is how to modify the Security Configuration and use the master .. xp_cmdshell:

 

-- 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 _ Your shell', 1
GO
-- Install
RECONFIGURE
GO
-- Execute 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 _ Your shell', 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.

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. Use the master. xp_cmdshell stored procedure to call the osql/ocmd command
4. Summary

I. Preparations

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

1. SQL content:

 

INSERTINTO dbo.Products ( ProductName ) VALUES  ( '1.sql_'+CAST(GETDATE() ASNVARCHAR(10)) )

2. Content in SQL:

 

INSERTINTO dbo.Products ( ProductName ) VALUES  ( '2.sql_'+CAST(GETDATE() ASNVARCHAR(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 to execute SQL files in batches. How can we do this in the program? Of course, we can call the batch processing file, however, we prefer to use SQL more often, so we have to mention the master .. the stored procedure xp_mongoshell. The stored procedure of master.. xp_cmdshell allows us to call command shell in the form of SQL statements. Of course, we can basically use master. xp_cmdshell in cmd.

Before using the master .. xp_mongoshell, we must perform security configuration on the server. If we do not configure SQL server, the following error will be thrown:

SQL Server blocks access to 'sys. xp_mongoshell' during the 'xp _ mongoshell' process, because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'xp _ javasshell' by using sp_configure '. For more information about enabling 'xp _ external shell', see "peripheral application configurator" in SQL Server books online ".

There are multiple configuration methods. Here we use SQL to perform security configuration (note that the master is called for security reasons .. xp_mongoshell), the following is how to modify the Security Configuration and use the master .. xp_cmdshell:

 

-- 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 _ Your shell', 1
GO
-- Install
RECONFIGURE
GO
-- Execute 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 _ Your shell', 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.