The stored procedure uses shell scripts to execute SQL files.

Source: Internet
Author: User

The requirement today is to write the creation of all tables to the storage process.

After receiving the script for creating a table, it is silly. There are 60-70 tables, including stored procedures and views.

How can this problem be solved.

The idea is to use shell scripts to execute SQL script files in the stored procedure.

Get the shell execution function through msdn: xp_cmdshell.

The following is a complete script:

 Create     Procedure  Creattable
(
@ Username Varchar ( 200 ),
@ Password Varchar ( 200 ),
@ Filepath Varchar ( 200 ),
@ Trusted Bit
)
As
Begin
Set Nocount On ;

Declare @ Shell Varchar ( Max );

Exec SYS. sp_configure ' Show advanced options ' , 1 ;

-- Open Shell
Exec SYS. sp_configure ' Xp_mongoshell ' , 1

If @ Trusted = 1
Set @ Shell = ' Osql-e northwind-I ' + @ Filepath ;
Else
-- Use user name connection
Set @ Shell = ' Osql-u ' + @ Username + ' -P ' + @ Password + ' -D northwind-I ' + @ Filepath ;

Exec Master .. xp_mongoshell @ Shell ;

-- Close Shell
Exec SYS. sp_configure ' Xp_mongoshell ' , 0
End
go
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.