Menggunakan sqlcmd

Artikel
09/19/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

sqlcmd adalah utilitas baris perintah untuk ad hoc, eksekusi interaktif dari pernyataan dan skrip Transact-SQL (T-SQL) dan untuk mengotomatiskan tugas pembuatan skrip T-SQL. Untuk menggunakan sqlcmd secara interaktif, atau untuk membuat file skrip untuk sqlcmd, Anda harus memahami T-SQL. Anda dapat menggunakan sqlcmd dengan berbagai cara. Contohnya:

Masukkan pernyataan T-SQL dari prompt perintah. Konsol mengembalikan hasilnya. Untuk membuka jendela Prompt Perintah, masukkan cmd di kotak pencarian Windows dan pilih Prompt Perintah untuk membuka. Pada prompt perintah, ketik sqlcmd diikuti dengan daftar opsi yang Anda inginkan. Untuk daftar lengkap opsi yang didukung oleh sqlcmd, lihat utilitas sqlcmd.
Kirimkan pekerjaan sqlcmd baik dengan menentukan satu pernyataan T-SQL untuk dijalankan, atau dengan mengarahkan utilitas ke file teks yang berisi pernyataan T-SQL untuk dijalankan. Output diarahkan ke file teks, tetapi juga dapat ditampilkan di prompt perintah.
Mode SQLCMD di Editor Kueri SQL Server Management Studio (SSMS).
Objek Manajemen SQL Server (SMO).
Pekerjaan SQL Server Agent CmdExec.

Opsi sqlcmd umum

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

Catatan

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

Menyambungkan ke utilitas sqlcmd

Sambungkan ke instans default dengan menggunakan Autentikasi Windows untuk menjalankan pernyataan T-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.
Sambungkan ke instans bernama dengan menggunakan Autentikasi Windows untuk menjalankan pernyataan T-SQL secara interaktif:
```
sqlcmd -S <ComputerName>\<InstanceName>
```
or
```
sqlcmd -S .\<InstanceName>
```
Sambungkan ke instans bernama dengan menggunakan Autentikasi Windows dan tentukan file input dan output:
```
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>
```
Sambungkan ke instans default di komputer lokal dengan Autentikasi Windows, jalankan kueri, dan tetap jalankan sqlcmd setelah kueri selesai:
```
sqlcmd -q "SELECT * FROM AdventureWorks2022.Person.Person"
```
Sambungkan ke instans default di komputer lokal dengan Autentikasi Windows, jalankan kueri, arahkan output ke file, dan keluar dari sqlcmd setelah kueri selesai:
```
sqlcmd -Q "SELECT * FROM AdventureWorks2022.Person.Person" -o MyOutput.txt
```
Sambungkan ke instans bernama menggunakan Autentikasi SQL Server untuk menjalankan pernyataan T-SQL secara interaktif, dengan sqlcmd meminta kata sandi:
```
sqlcmd -U MyLogin -S <ComputerName>\<InstanceName>
```
Tip

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

Jalankan pernyataan Transact-SQL secara interaktif dengan menggunakan sqlcmd

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

sqlcmd -S <ComputerName>\<InstanceName>

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

Pada perintah sqlcmd, Anda dapat mengetik pernyataan T-SQL dan perintah sqlcmd, seperti GO dan EXIT. Setiap pernyataan T-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 dari sqlcmd, ketik EXIT atau QUIT di awal baris baru.

Untuk menghapus cache pernyataan, ketik :RESET. Mengetik Ctrl+C menyebabkan sqlcmd keluar. Ctrl+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 :ED perintah dan prompt sqlcmd . Editor akan terbuka dan, setelah mengedit pernyataan T-SQL dan menutup editor, pernyataan T-SQL yang direvisi akan muncul di jendela perintah. Masukkan GO untuk menjalankan pernyataan T-SQL yang direvisi.

String yang dikutip

Karakter yang diapit dalam tanda kutip digunakan tanpa pra-pemrosesan tambahan, kecuali bahwa 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 diperluas saat muncul dalam string.

Contohnya:

sqlcmd
PRINT "Length: 5"" 7'";
GO

Berikut set hasilnya.

Length: 5" 7'

String yang mencakup beberapa baris

sqlcmd mendukung string yang mencakup beberapa baris. Misalnya, pernyataan berikut mencakup SELECT beberapa baris tetapi dijalankan sebagai string tunggal setelah Anda mengetik GO lalu tekan Enter.

SELECT <First line>
FROM <Second line>
WHERE <Third line>;
GO

Contoh sqlcmd interaktif

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

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

C:\Temp\>

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

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

C:\Temp>sqlcmd
1>

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

sqlcmd
USE AdventureWorks2022;
GO

Berikut set hasilnya.

Changed database context to 'AdventureWorks2022'.
1>

Saat Anda menekan Enter, itu memberi sinyal sqlcmd untuk memulai baris baru. Menekan Enter setelah Anda mengetik GO, memberi sinyal sqlcmd untuk mengirim USE AdventureWorks2022 pernyataan ke instans SQL Server. sqlcmd kemudian mengembalikan pesan untuk menunjukkan bahwa USE pernyataan berhasil diselesaikan, dan menampilkan prompt 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 dari sqlcmd:

USE AdventureWorks2022;
GO
SELECT TOP (3) BusinessEntityID, FirstName, LastName
FROM Person.Person;
GO

Berikut set hasilnya.

BusinessEntityID  FirstName  LastName
----------------- ---------- ------------
1                 Syed       Abbas
2                 Catherine  Abel
3                 Kim        Abercrombie

Setelah Anda menghasilkan output, sqlcmd mengatur ulang perintah sqlcmd dan menampilkan 1>. Ketik EXIT pada 1> perintah, untuk keluar dari sesi. Sekarang Anda dapat menutup jendela Prompt Perintah dengan mengetik perintah lain EXIT .

Membuat dan mengkueri kontainer SQL Server

Anda dapat menggunakan sqlcmd (Go) untuk membuat instans baru SQL Server dalam kontainer. sqlcmd (Go) mengekspos create pernyataan yang memungkinkan Anda menentukan gambar kontainer dan cadangan SQL Server, untuk membuat instans SQL Server dengan cepat untuk tujuan pengembangan, penelusuran kesalahan, dan analisis.

Penting

Anda memerlukan runtime kontainer yang diinstal, seperti Docker, atau Podman.

Perintah berikut menunjukkan cara melihat semua opsi yang tersedia untuk membuat kontainer SQL Server baru:

sqlcmd create mssql --help

Perintah berikut membuat instans SQL Server baru menggunakan versi terbaru SQL Server 2022 (16.x), lalu memulihkan database sampel Wide World Importers:

sqlcmd create mssql --accept-eula --tag 2022-latest --using https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

Setelah instans SQL Server dibuat, Anda dapat menggunakan sqlcmd (Go) untuk mengelola dan mengkuerinya.

Perintah berikut mengonfirmasi versi instans yang dibuat:

sqlcmd query "SELECT @@version"

Perintah berikut memulai sesi interaktif dengan instans yang dibuat:

sqlcmd query

Perintah berikut membuka Azure Data Studio, dan tersambung secara otomatis ke database yang dipulihkan selama proses pembuatan:

sqlcmd open ads

Perintah berikut mencantumkan string koneksi yang akan digunakan untuk menyambungkan ke instans yang dibuat:

sqlcmd config connection-strings

Perintah berikut digunakan untuk menghapus kontainer saat tidak lagi diperlukan:

sqlcmd delete

Menjalankan file skrip Transact-SQL menggunakan sqlcmd

Anda dapat menggunakan sqlcmd untuk menjalankan file skrip database. File skrip adalah file teks yang berisi campuran pernyataan T-SQL, perintah sqlcmd , dan variabel pembuatan skrip. Untuk informasi selengkapnya tentang cara membuat variabel skrip, lihat Menggunakan sqlcmd dengan Variabel Pembuatan Skrip. sqlcmd bekerja dengan pernyataan, perintah, dan variabel pembuatan skrip dalam file skrip dengan cara yang mirip dengan cara kerjanya dengan pernyataan dan perintah yang dimasukkan secara interaktif. Perbedaan utamanya adalah bahwa sqlcmd membaca 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 T-SQL secara interaktif di SQL Server Management Studio, lalu menyimpan konten jendela Kueri sebagai file skrip.
Anda dapat membuat file teks yang berisi pernyataan T-SQL dengan menggunakan editor teks, seperti Notepad.

Contoh

J. Menjalankan skrip dengan menggunakan sqlcmd

Mulai Notepad, dan ketik pernyataan T-SQL berikut:

USE AdventureWorks2022;
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 perintah berikut pada prompt perintah untuk menjalankan skrip dan masukkan output di MyOutput.txt MyFolder:

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

Berikut set hasilnya.

Changed database context to 'AdventureWorks2022'.
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:\Temp\>sqlcmd -S ServerName -A
1> SELECT session_id, blocking_session_id FROM `sys.dm_exec_requests` WHERE blocking_session_id <> 0;
2> GO

Berikut set hasilnya.

session_id   blocking_session_id
-----------  --------------------`
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 AdventureWorks2022;
GO

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;
GO

Pada perintah sqlcmd, masukkan yang berikut ini:

C:\Temp\sqlcmd
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
1> EXEC dbo.ContactEmailAddress $(FirstName),$(LastName)
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:\Temp\BackupTemplate.sql dengan kode berikut.

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

Pada perintah sqlcmd, masukkan kode berikut:

C:\Temp\>sqlcmd
1> :connect <server>
Sqlcmd: Successfully connected to server <server>.
1> :setvar db msdb
1> :setvar bakfile C:\Temp\msdb.bak
1> :r C:\Temp\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:\Temp\>sqlcmd -d AdventureWorks2022
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

Perintah sqlcmd seperti sqlcmd -i C:\Temp\InputFile.txt -o C:\Temp\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 di C:\Temp:

C:\Temp\badscript.sql

SELECT batch_1_this_is_an_error
GO
SELECT 'batch #2'
GO

C:\Temp\goodscript.sql

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

C:\Temp\returnvalue.sql
```
:exit(select 100)
```

C:\Temp\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:\Temp\windowsscript.bat:

C:\Temp\>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 di Azure SQL Database

sqlcmd dapat dijalankan pada koneksi ke data SQL Database 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 Klien Asli SQL Server 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