@manhnguyenv

Welcome to my blog!

SqlCmd

May 24, 2017 21:29

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

 

Categories

Recent posts