Menggunakan Utilitas sqlcmd

Artikel
06/13/2017

Utilitas sqlcmd ini adalah utilitas baris perintah untuk ad hoc, eksekusi interaktif pernyataan dan skrip Transact-SQL dan untuk mengotomatiskan tugas pembuatan skrip Transact-SQL. Untuk menggunakan sqlcmd secara interaktif, atau untuk membuat file skrip yang akan dijalankan menggunakan sqlcmd, pengguna harus memahami Transact-SQL. sqlcmd Utilitas biasanya digunakan dengan cara berikut:

Pengguna secara interaktif memasukkan pernyataan Transact-SQL dengan cara yang mirip dengan bekerja di prompt perintah. Hasilnya ditampilkan di prompt perintah. Untuk membuka jendela Prompt Perintah, klik Mulai, klik Semua Program, arahkan ke Aksesori, lalu klik Prompt Perintah. Pada prompt perintah, ketik sqlcmd diikuti dengan daftar opsi yang Anda inginkan. Untuk daftar lengkap opsi yang didukung oleh sqlcmd, lihat Utilitas sqlcmd.
Pengguna mengirimkan sqlcmd pekerjaan baik dengan menentukan satu pernyataan Transact-SQL untuk dijalankan, atau dengan mengarahkan utilitas ke file teks yang berisi pernyataan Transact-SQL untuk dijalankan. Output biasanya diarahkan ke file teks, tetapi juga dapat ditampilkan di prompt perintah.
Mode SQLCMD dalam SQL Server Management Studio Editor Kueri.
SQL Server Management Objects (SMO)
SQL Server Agent pekerjaan CmdExec.

Opsi sqlcmd yang Biasanya Digunakan

Opsi berikut paling sering digunakan:

Opsi server (-S) yang mengidentifikasi instans Microsoft SQL Server yang sqlcmd tersambung.
Opsi autentikasi (-E, -U, dan -P) yang menentukan kredensial yang sqlcmd menggunakan untuk menyambungkan ke instans SQL Server.

Catatan

Opsi -E adalah default dan tidak harus ditentukan.
Opsi input (-Q, -q, dan -i) yang mengidentifikasi lokasi input ke sqlcmd.
Opsi output (-o) yang menentukan file untuk sqlcmd menempatkan outputnya.

Menyambungkan ke Utilitas sqlcmd

Berikut ini adalah penggunaan sqlcmd umum utilitas:

Menyambungkan ke instans default dengan menggunakan Autentikasi Windows untuk menjalankan pernyataan Transact-SQL secara interaktif:
```
sqlcmd -S <ComputerName>  
```
Catatan

Dalam contoh sebelumnya, -E tidak ditentukan karena merupakan default dan sqlcmd tersambung ke instans default dengan menggunakan Autentikasi Windows.
Menyambungkan ke instans bernama dengan menggunakan Autentikasi Windows untuk menjalankan pernyataan Transact-SQL secara interaktif:
```
sqlcmd -S <ComputerName>\<InstanceName>  
```
atau
```
sqlcmd -S .\<InstanceName>  
```
Menyambungkan ke instans bernama dengan menggunakan Autentikasi Windows dan menentukan file input dan output:
```
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>  
```
Menyambungkan ke instans default di komputer lokal dengan menggunakan Autentikasi Windows, menjalankan kueri, dan tetap sqlcmd berjalan setelah kueri selesai berjalan:
```
sqlcmd -q "SELECT * FROM AdventureWorks2012.Person.Person"  
```
Menyambungkan ke instans default di komputer lokal dengan menggunakan Autentikasi Windows, menjalankan kueri, mengarahkan output ke file, dan keluar sqlcmd setelah kueri selesai berjalan:
```
sqlcmd -Q "SELECT * FROM AdventureWorks2012.Person.Person" -o MyOutput.txt  
```
Menyambungkan ke instans bernama menggunakan Autentikasi SQL Server untuk menjalankan pernyataan Transact-SQL secara interaktif, dengan sqlcmd meminta kata sandi:
```
sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>  
```
Catatan

Untuk melihat daftar opsi yang didukung oleh sqlcmd eksekusi utilitas: sqlcmd -?.

Menjalankan Pernyataan Transact-SQL Secara Interaktif dengan Menggunakan sqlcmd

Anda dapat menggunakan sqlcmd utilitas secara interaktif untuk menjalankan pernyataan Transact-SQL di jendela Prompt Perintah. Untuk menjalankan pernyataan Transact-SQL secara interaktif dengan menggunakan sqlcmd, jalankan utilitas tanpa menggunakan opsi -Q, -q, -Z, atau -i untuk menentukan file input atau kueri apa pun. Contohnya:

sqlcmd -S <ComputerName>\<InstanceName>

Ketika perintah dijalankan tanpa file input atau kueri, sqlcmd menyambungkan ke instans SQL Server yang ditentukan lalu menampilkan baris baru dengan 1> diikuti dengan garis bawah berkedip yang diberi nama sqlcmd prompt. Menandakan 1 bahwa ini adalah baris pertama dari pernyataan Transact-SQL, dan sqlcmd prompt adalah titik di mana pernyataan Transact-SQL akan dimulai ketika Anda mengetiknya.

sqlcmd Pada perintah, Anda dapat mengetik pernyataan dan sqlcmd perintah Transact-SQL, seperti GO dan EXIT. Setiap pernyataan Transact-SQL dimasukkan ke dalam buffer yang disebut cache pernyataan. Pernyataan ini dikirim ke SQL Server setelah Anda mengetik GO perintah dan menekan ENTER. Untuk keluar sqlcmd, ketik EXIT atau QUIT di awal baris baru.

Untuk menghapus cache pernyataan, ketik :RESET. Mengetik ^C menyebabkan sqlcmd keluar. ^C juga dapat digunakan untuk menghentikan eksekusi cache pernyataan setelah GO perintah dikeluarkan.

Pernyataan T-SQL yang dimasukkan dalam sesi interaktif dapat diedit dengan memasukkan perintah :ED dan sqlcmd prompt. Editor akan terbuka dan, setelah mengedit pernyataan Transact-SQL dan menutup editor, pernyataan Transact-SQL yang direvisi akan muncul di jendela perintah. Masukkan GO untuk menjalankan pernyataan Transact-SQL yang tersedia.

String yang Dikutip

Karakter yang diapit dalam tanda kutip digunakan tanpa pra-pemrosesan tambahan, kecuali tanda kutip dapat dimasukkan ke dalam string dengan memasukkan dua tanda kutip berturut-turut. SQL Server memperlakukan urutan karakter ini sebagai satu tanda kutip. (Namun, terjemahan terjadi di server.) Variabel pembuatan skrip tidak akan diperluas saat muncul dalam string.

Contohnya:

sqlcmd

PRINT "Length: 5"" 7'";

GO

Berikut adalah hasil yang ditetapkan.

Length: 5" 7'

String yang Mencakup Beberapa Baris

sqlcmd mendukung skrip yang memiliki string yang mencakup beberapa baris. Misalnya, pernyataan berikut mencakup SELECT beberapa baris tetapi merupakan string tunggal yang dijalankan saat Anda menekan tombol ENTER setelah mengetik GO.

SELECT First line

FROM Second line

WHERE Third line;

GO

Contoh sqlcmd interaktif

Ini adalah contoh apa yang Anda lihat saat Anda menjalankan sqlcmd secara interaktif.

Saat Anda membuka jendela Prompt Perintah, ada satu baris yang mirip dengan:

C:\> _

Ini berarti folder C:\ adalah folder saat ini, dan jika Anda menentukan nama file, Windows akan mencari file di folder tersebut.

Ketik sqlcmd untuk menyambungkan ke instans default SQL Server di komputer lokal, dan konten jendela Prompt Perintah adalah:

C:\>sqlcmd

1> _

Ini berarti Anda telah terhubung ke instans SQL Server dan sqlcmd sekarang siap untuk menerima pernyataan dan sqlcmd perintah Transact-SQL. Garis bawah berkedip setelah 1> adalah sqlcmd perintah yang menandai lokasi di mana pernyataan dan perintah yang Anda ketik akan ditampilkan. Sekarang, ketik USE AdventureWorks2012 dan tekan ENTER, lalu ketik GO dan tekan ENTER. Konten jendela Prompt Perintah adalah:

sqlcmd

USE AdventureWorks2012;

GO

Berikut adalah hasil yang ditetapkan.

Changed database context to 'AdventureWorks2012'.

1> _

Menekan ENTER setelah memasukkan sinyal USE AdventureWorks2012sqlcmd untuk memulai baris baru. Menekan ENTER, setelah Anda mengetik GO, sinyal sqlcmd untuk mengirim USE AdventureWorks2012 pernyataan ke instans SQL Server. sqlcmd kemudian mengembalikan pesan untuk menunjukkan bahwa USE pernyataan berhasil diselesaikan dan menampilkan perintah baru 1> sebagai sinyal untuk memasukkan pernyataan atau perintah baru.

Contoh berikut menunjukkan apa yang dimuat jendela Prompt Perintah jika Anda mengetik SELECT pernyataan, GO untuk menjalankan SELECT, dan EXIT untuk keluar sqlcmd:

sqlcmd

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Berikut adalah hasil yang ditetapkan.

BusinessEntityID FirstName LastName

----------- -------------------------------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

1> EXIT

C:\>

Baris demi baris 3> GO adalah output dari SELECT pernyataan. Setelah Anda menghasilkan output, sqlcmd reset sqlcmd perintah dan tampilkan 1>. Setelah memasukkan EXIT di baris 1>, jendela Prompt Perintah menampilkan baris yang sama dengan yang dilakukannya saat Anda pertama kali membukanya. Ini menunjukkan bahwa sqlcmd telah keluar dari sesinya. Sekarang Anda dapat menutup jendela Prompt Perintah dengan mengetik perintah lain EXIT .

Menjalankan File Skrip Transact-SQL dengan Menggunakan sqlcmd

Anda dapat menggunakan sqlcmd untuk menjalankan file skrip database. File skrip adalah file teks yang berisi campuran pernyataan, perintah, sqlcmd dan variabel skrip Transact-SQL. Untuk informasi selengkapnya tentang cara membuat variabel skrip, lihat Menggunakan sqlcmd dengan Variabel Pembuatan Skrip. sqlcmd bekerja dengan pernyataan, perintah, dan variabel skrip dalam file skrip dengan cara yang mirip dengan cara kerjanya dengan pernyataan dan perintah yang dimasukkan secara interaktif. Perbedaan utamanya adalah membaca sqlcmd file input tanpa jeda alih-alih menunggu pengguna memasukkan pernyataan, perintah, dan variabel pembuatan skrip.

Ada berbagai cara untuk membuat file skrip database:

Anda dapat membangun dan men-debug sekumpulan pernyataan Transact-SQL secara interaktif di SQL Server Management Studio, lalu menyimpan konten jendela Kueri sebagai file skrip.
Anda dapat membuat file teks yang berisi pernyataan Transact-SQL dengan menggunakan editor teks, seperti Notepad.

Contoh

A. Menjalankan skrip dengan menggunakan sqlcmd

Mulai Notepad, dan ketik pernyataan Transact-SQL berikut:

USE AdventureWorks2012;

GO

SELECT TOP (3) BusinessEntityID, FirstName, LastName

FROM Person.Person;

GO

Buat folder bernama MyFolder lalu simpan skrip sebagai file MyScript.sql di folder C:\MyFolder. Masukkan yang berikut ini di prompt perintah untuk menjalankan skrip dan masukkan output di MyOutput.txtMyFolder:

sqlcmd -i C:\MyFolder\MyScript.sql -o C:\MyFolder\MyOutput.txt

Saat Anda melihat konten MyOutput.txt di Notepad, Anda akan melihat hal berikut:

Changed database context to 'AdventureWorks2012'.

BusinessEntityID FirstName LastName

---------------- ----------- -----------

1 Syed Abbas

2 Catherine Abel

3 Kim Abercrombie

(3 rows affected)

B. Menggunakan sqlcmd dengan koneksi administratif khusus

Dalam contoh berikut, sqlcmd digunakan untuk menyambungkan ke server yang memiliki masalah pemblokiran dengan menggunakan koneksi administrator khusus (DAC).

C:\>sqlcmd -S ServerName -A

1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;

2> GO

Berikut adalah hasil yang ditetapkan.

spid blocked

------ -------

62 64

(1 rows affected)

Gunakan sqlcmd untuk mengakhiri proses pemblokiran.

1> KILL 64;

2> GO

C. Menggunakan sqlcmd untuk menjalankan prosedur tersimpan

Contoh berikut menunjukkan cara menjalankan prosedur tersimpan dengan menggunakan sqlcmd. Buat prosedur tersimpan berikut ini.

USE AdventureWorks2012;

IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL

DROP PROCEDURE dbo.ContactEmailAddress;

GO

CREATE PROCEDURE dbo.ContactEmailAddress

(

@FirstName nvarchar(50)

,@LastName nvarchar(50)

)

AS

SET NOCOUNT ON

SELECT EmailAddress

FROM Person.Person

WHERE FirstName = @FirstName

AND LastName = @LastName;

SET NOCOUNT OFF

sqlcmd Pada perintah, masukkan yang berikut ini:

C:\sqlcmd

1> :Setvar FirstName Gustavo

1> :Setvar LastName Achong

1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)

2> GO

EmailAddress

-----------------------------

gustavo0@adventure-works.com

D. Menggunakan sqlcmd untuk pemeliharaan database

Contoh berikut menunjukkan cara menggunakan sqlcmd untuk tugas pemeliharaan database. Buat C:\BackupTemplate.sql dengan kode berikut.

USE master;

BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';

sqlcmd Pada perintah, masukkan yang berikut ini:

C:\ >sqlcmd

1> :connect <server>

Sqlcmd: Successfully connected to server <server>.

1> :setvar db msdb

1> :setvar bakfile c:\msdb.bak

1> :r c:\BackupTemplate.sql

2> GO

Changed database context to 'master'.

Processed 688 pages for database 'msdb', file 'MSDBData' on file 2.

Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.

BACKUP DATABASE successfully processed 693 pages in 0.725 seconds (7.830 MB/sec)

E. Menggunakan sqlcmd untuk menjalankan kode pada beberapa instans

Kode berikut dalam file menunjukkan skrip yang tersambung ke dua instans. GO Perhatikan sebelum koneksi ke instans kedua.

:CONNECT <server>\,<instance1>

EXEC dbo.SomeProcedure

GO

:CONNECT <server>\,<instance2>

EXEC dbo.SomeProcedure

GO

E. Mengembalikan output XML

Contoh berikut menunjukkan bagaimana output XML dikembalikan tidak diformat, dalam aliran berkelanjutan.

C:\>sqlcmd -d AdventureWorks2012

1> :XML ON

1> SELECT TOP 3 FirstName + ' ' + LastName + ', '

2> FROM Person.Person

3> GO

Syed Abbas, Catherine Abel, Kim Abercrombie,

F. Menggunakan sqlcmd dalam file skrip Windows

sqlcmdPerintah seperti sqlcmd -i C:\InputFile.txt -o C:\OutputFile.txt, dapat dijalankan dalam file .bat bersama dengan VBScript. Dalam hal ini, jangan gunakan opsi interaktif. sqlcmd harus diinstal pada komputer yang menjalankan file .bat.

Pertama, buat empat file berikut:

C:\badscript.sql

SELECT batch_1_this_is_an_error  
GO  
SELECT 'batch #2'  
GO

C:\goodscript.sql

SELECT 'batch #1'  
GO  
SELECT 'batch #2'  
GO

C:\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

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

Kemudian, pada prompt perintah, jalankan C:\windowsscript.bat:

C:\>windowsscript.bat

Running badscript.sql

== An error occurred

Running goodscript.sql

Running returnvalue.sql

SQLCMD returned 100 to the command shell

G. Menggunakan sqlcmd untuk mengatur enkripsi pada Azure SQL Database

sqlcmddapat dijalankan pada koneksi untuk SQL Database data untuk menentukan enkripsi dan kepercayaan sertifikat. Tersedia dua opsi 'sqlcmd''':

Sakelar -N digunakan oleh klien untuk meminta koneksi terenkripsi. Opsi ini setara dengan opsi ENCRYPT = trueADO.net .
Sakelar -C digunakan oleh klien untuk mengonfigurasinya secara implisit sertifikat server kepercayaan dan tidak memvalidasinya. Opsi ini setara dengan opsi TRUSTSERVERCERTIFICATE = trueADO.net .

Layanan SQL Database tidak mendukung semua opsi yang SET tersedia pada instans SQL Server. Opsi berikut melemparkan kesalahan saat opsi yang SET sesuai diatur ke ON atau OFF:

SET ANSI_DEFAULTS
SET ANSI_NULLS
SET REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT

Opsi SET berikut ini tidak melemparkan pengecualian tetapi tidak dapat digunakan. Mereka tidak digunakan lagi:

SET CONCAT_NULL_YIELDS_NULL
SET ANSI_PADDING
SET QUERY_GOVERNOR_COST_LIMIT

Sintaks

Contoh berikut mengacu pada kasus di mana pengaturan Penyedia SQL Server Native Client meliputi: ForceProtocolEncryption = False,Trust Server Certificate = No

Sambungkan menggunakan kredensial Windows dan enkripsi komunikasi:

SQLCMD -E -N

Sambungkan menggunakan kredensial Windows dan sertifikat server kepercayaan:

SQLCMD -E -C