SqlCmd trong Command Prompt dùng để thực thi câu lệnh T-SQL
Kết nối với Server trong chế độ Windows Authentication
SqlCmd -S "ManhNguyenV"
Kết nối với Server trong chế độ SQL Server Authentication
SqlCmd -U sa -S "ManhNguyenV"
Password: enter password
Kết nối với Database
use [TestDB]
go
Thực thi một số câu lệnh SQL đơn giản:
select * from dbo.Users
go
Những câu lệnh hay dùng:
go: thực thi câu lệnh T-SQL để thấy kết quả
exit: thoát ra ngoài Command Prompt
Để chạy một đoạn script có sẵn?
set runscript=C:\SQL\SqlScript.sql
sqlcmd
1) Running a script by using sqlcmd
Start > Run > Cmd
C:\>md SQL
C:\>cd C:\SQL
C:\SQL>notepad MyScript.sql
C:\SQL>notepad MyOutput.txt
C:\SQL>sqlcmd -i MyScript.sql -o MyOutput.txt
C:\SQL>type MyOutput.txt
In dữ liệu ra text file
C:\SQL>sqlcmd -d TestDB -q "select * from dbo.Users" -o C:\SQL\SqlOutput.txt
In dữ liệu Unicode ra text file
C:\SQL>sqlcmd -d TestDB -q "select * from dbo.Users" -u -o C:\SQL\SqlOutput.txt
Không in Header
C:\SQL>sqlcmd -h-1 -S manhnguyenv -U sa -P 123456 -d TestDB -q "select * from dbo.Users" -u -o C:\SQL\SqlOutput.txt
2) Using SqlCmd to execute a stored procedure
if object_id('testsp','P') is not null
drop proc testsp
go
create proc testsp(
@name nvarchar(50)
)
as
set nocount off
select * from dbo.Users where name like '%' + @name + '%'
set nocount on
go
:setvar name 'manh'
exec dbo.testsp $(name)
go
Id Name
-------------------------------------------------------------
1 Manh
6 Nguyen Viet Manh
(5 rows affected)
3) Database Administration
Start > Run > Cmd
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation. All rights reserved.
C:\Users\Manh>notepad C:\SQL\BackupScript.sql
USE master;
BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';
C:\Users\Manh>sqlcmd
1> :connect manhnguyenv
Sqlcmd: Successfully connected to server 'manhnguyenv'.
1> :setvar db testdb
1> :setvar bakfile "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\testdb.bak"
1> :r C:\SQL\BackupScript.sql
2> go
Changed database context to 'master'.
Processed 376 pages for database 'testdb', file 'TestDB' on file 1.
Processed 6 pages for database 'testdb', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 382 pages in 0.033 seconds (90.258 MB/sec).
4) Returning XML output
C:\> sqlcmd -d manhnguyenv
:XML ON
select * from dbo.Users
go
5) Using sqlcmd to execute code on multiple instances
BadScript.sql
SELECT batch_1_this_is_an_error
GO
SELECT 'batch #2'
GO
GoodScript.sql
SELECT 'batch #1'
GO
SELECT 'batch #2'
GO
ReturnValue.sql
:exit(select 100)
@echo off
C:\windowsscript.bat
@echo off
echo Running badscript.sql
sqlcmd -i badscript.sql -b -o out.log
if not errorlevel 1 goto next1
echo == An error occurred
:next1
echo Running goodscript.sql
sqlcmd -i goodscript.sql -b -o out.log
if not errorlevel 1 goto next2
echo == An error occurred
:next2
echo Running returnvalue.sql
sqlcmd -i returnvalue.sql -o out.log
echo SQLCMD returned %errorlevel% to the command shell
:exit
WindowsScript.bat
@echo off
echo Running badscript.sql
sqlcmd -i badscript.sql -b -o out.log
if not errorlevel 1 goto next1
echo == An error occurred
:next1
echo Running goodscript.sql
sqlcmd -i goodscript.sql -b -o out.log
if not errorlevel 1 goto next2
echo == An error occurred
:next2
echo Running returnvalue.sql
sqlcmd -i returnvalue.sql -o out.log
echo SQLCMD returned %errorlevel% to the command shell
:exit
Refer: https://docs.microsoft.com/en-us/sql/relational-databases/scripting/sqlcmd-use-the-utility
