Bagaimana cara mengekspor data dari server sql ke excel menggunakan prosedur tersimpan?

Dimasukkannya CLR di SQL Server telah menjadi kontroversi, tetapi beberapa orang telah menemukan cara yang bagus untuk memanfaatkan fitur ini. Penulis baru Anders Pedersen memberi kita teknik untuk mengekspor data dari SQL Server ke Excel menggunakan CLR

Hal pertama adalah, apakah Anda ingin menghindari SSIS sama sekali? . Langkah-langkahnya adalah

0. ) Siapkan spreadsheet Excel kosong dengan nama kolom yang akan Anda dapatkan dari prosedur tersimpan Anda. Anda dapat melakukan ini dengan cukup mudah dengan menyalin dan menempelkan hanya satu baris data dari SSMS dari tempat Anda menjalankan prosedur tersimpan secara manual, dan kemudian hanya menghapus baris data, meninggalkan header di tempatnya. Pastikan untuk tidak memiliki banyak kolom dengan nama yang sama di hasil prosedur tersimpan Anda

1. ) Tambahkan Connection Manager untuk menunjuk ke SQL Server dan database tertentu, dan juga menambahkan satu untuk menunjuk ke spreadsheet Excel yang kosong, Yang pertama dapat berupa koneksi SQL Native Client, dan yang terakhir adalah koneksi Flat File

2. ) Tambahkan Tugas Aliran Data ke Aliran Kontrol

3. ) Buat beberapa variabel SSIS untuk menyimpan nilai parameter apa pun untuk prosedur tersimpan Anda

4. ) Di dalam Tugas Aliran Data, tambahkan Sumber OLE DB dan konfigurasikan untuk menggunakan prosedur tersimpan Anda. Pernyataan EXEC untuk menjalankan prosedur tersimpan Anda adalah kueri Anda, dan Anda menggunakan ? . Di tab Parameter, Anda mengidentifikasi nama parameter aktual dalam prosedur Anda dan mengidentifikasinya sesuai urutan kemunculannya di prosedur tersimpan Anda, memberikan skema penomoran berbasis nol ke kolom Nama Parameter di sisi kanan, dan menentukan variabel

5. ) Tambahkan Excel Destination yang mengarah ke Flat File Connection Manager yang Anda buat di langkah nol

6. ) Seret baris keluaran Sumber OLE DB ke Tujuan Excel, lalu edit tujuan dan lakukan tab Pemetaan untuk memetakan kolom masukan yang keluar dari prosedur tersimpan Anda (dan dengan demikian Sumber OLE DB), ke kolom di

Ini akan membuat proses bekerja sekali. Eksekusi berulang akan membutuhkan spreadsheet template baru dengan hanya nama kolom setiap saat. Itu dapat dilakukan dengan menggunakan Tugas Sistem File dalam Aliran Kontrol Anda, sebelum Tugas Aliran Data, dengan menyalin file spreadsheet kosong (templat) di atas file yang ada dari eksekusi terakhir. Namun, jika Anda ingin menghindari SSIS sama sekali, Anda harus mengonfigurasi SQL Server untuk mengizinkan sesuatu yang tidak boleh dilakukan oleh banyak orang pada server mereka karena potensi risiko keamanan, dan saya malu untuk mengakui bahwa saya' . Itu akan memberi Anda kemampuan untuk menentukan nama file untuk keluaran langsung di dalam T-SQL. Saya percaya itu akan menjadi pernyataan BULK OUTPUT, dan Anda mungkin akan mengubah prosedur tersimpan Anda untuk MEMILIH data ke dalam tabel temp dan kemudian menambahkan parameter nama file dan kemudian menggunakan SQL dinamis untuk menjalankan pernyataan BULK yang akan menempatkan data ke dalam . Anda juga sebaiknya memvalidasi parameter nama file dan memiliki konvensi penamaan ketat yang Anda validasi, seperti tidak mengizinkan spasi

Dalam artikel sebelumnya, kami telah meninjau algoritme umum untuk menemukan dan menghapus transaksi terbuka yang tidak lengkap di database SQL Server, membuat tabel untuk memperbaiki transaksi yang tidak lengkap dan prosedur yang disimpan CRUD, dan menerapkan banyak pengaturan yang akan membuat alur kerja dokumen kami produktif dan berguna

Sekarang mari ekspor dan uji stored procedure, buat kodenya, dan ubah menjadi skrip – ini akan dijalankan di host mana pun atau sekelompok host dengan menentukan parameter entri yang tepat

Ekspor Stored Procedure di SQL Server

Ini juga bisa sangat berguna untuk membuat skrip untuk membuat objek database yang diperlukan, misalnya, membuat skrip untuk mengekspor prosedur tersimpan ke file atau menyalin prosedur tersimpan ke solusi lain. Untuk melakukannya, ikuti langkah-langkah berikut

• Di Object Explorer, klik kanan pada database Anda
• Pilih Tugas dari menu konteks yang muncul
• Pilih perintah Generate Scripts

• Pilih objek untuk skrip

• Di jendela Set Scripting Options, pilih Script to File

• Jalankan skrip yang dihasilkan terhadap database target

Ketika semua langkah ini dilakukan, kami akan mendapatkan skrip yang dihasilkan dari prosedur tersimpan yang diekspor

Menguji prosedur tersimpan

Mari kembali ke stored procedure yang telah kita buat di bagian 2. Jika kita menyeret kursor ke stored procedure, menu drop-down akan muncul

Agar berfungsi dengan baik, prosedur tersimpan Anda harus memiliki deskripsi. Jika belum ditambahkan, silakan jalankan skrip berikut

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identification of frozen transactions (forgotten ones that do not have active requests) with their subsequent removal' , @level0type=N'SCHEMA',@level0name=N'srv', @level1type=N'PROCEDURE',@level1name=N'AutoKillSessionTranBegin'
GO

Atau gunakan properti objek yang diperluas

Anda juga dapat menambahkan deskripsi menggunakan metode ini

Pembuatan kode prosedur tersimpan

Anda dapat memanggilnya dengan klik kanan pada menu drop-down stored procedure, lalu pilih "Script Object as CREATE/ALTER"

Setelah Anda melakukan ini, skrip untuk perubahan objek akan muncul – dalam kasus kami, ini akan menjadi perubahan prosedur tersimpan. Jika tidak ada objek, akan ada skrip untuk membuatnya. Skrip ini dapat dengan mudah dipindahkan ke instance MS SQL Server yang tepat

Mengubah kode prosedur tersimpan menjadi skrip

Anda dapat memilih "Convert EXEC to Script" di menu drop-down

Alih-alih memanggil prosedur tersimpan, skrip akan dibuat, parameter entri akan menjadi variabel, dan konten prosedur tersimpan akan menjadi skrip. Ini adalah fitur yang berguna saat menguji kode Anda

Sekarang Anda dapat menjalankan skrip yang diturunkan pada host mana pun atau sekelompok host dengan mengatur nilai parameter entri yang tepat

Riwayat pembuatan kueri

Sekarang mari kita tinjau fungsi penting lainnya – SQL Complete. Riwayat Eksekusi yang menampilkan kueri sebelumnya. Untuk melakukan ini, klik pada SQL Complete. Tombol Riwayat Eksekusi

Anda akan melihat jendela berikut

Pojok kiri atas jendela berisi filter untuk kata dan rentang waktu, sedangkan pojok kanan menampilkan jumlah kueri yang ditemukan berdasarkan data yang dimasukkan dalam filter. Sisi bawah jendela menampilkan kueri yang dipilih dari tabel

Tabel tersebut terdiri dari kolom-kolom berikut

1. Status – menunjukkan apakah kueri berhasil diselesaikan (tanda centang putih di lingkaran hijau) atau tidak (tanda silang putih di lingkaran merah)
2. QueryText – menampilkan teks kueri
3. Ukuran (Bytes) – menunjukkan ukuran yang diukur dalam byte
4. Eksekusi Aktif – menampilkan tanggal dan waktu saat kueri dieksekusi
5. Durasi – menunjukkan waktu yang diperlukan kueri untuk dieksekusi
6. File – menunjukkan nama file dan path lengkap ke sana
7. Server – menampilkan nama server
8. Pengguna – menampilkan pengguna yang menjalankan skrip
9. Database – menampilkan database tempat skrip dijalankan

Pencarian untuk kolom "QueryText" dan "Execution On" dilakukan di filter "Search" dan "From/To"

Anda juga dapat mengurutkan kolom data dalam urutan menaik atau menurun dengan mengklik tajuk kolom yang tepat. Secara default, pengurutan kolom "Eksekusi Aktif" diaktifkan dalam urutan menurun

Anda dapat mengurutkan banyak kolom sekaligus dengan menahan tombol SHIFT. Anda juga dapat mengatur filter yang lebih kompleks dengan mengklik tanda filter

Mari sekarang masukkan nama prosedur tersimpan kami "AutoKillSessionTranBegin" di filter "Cari".

Hasilnya, kami mendapatkan riwayat pembuatan srv yang lengkap. Prosedur AutoKillSessionTranBegin yang telah dijelaskan sebelumnya

Jika Anda mengklik kanan pada baris tabel yang tepat, Anda dapat membuka skrip di jendela baru atau menyalinnya ke papan klip untuk menempelkannya di mana pun diperlukan

Anda dapat membuka skrip di jendela baru dengan mengklik dua kali skrip yang diinginkan di baris tabel

Anda juga dapat menentukan berapa lama riwayat eksekusi disimpan, ukuran kueri maksimum, dan jalur penyimpanan riwayat

Anda dapat membaca detail selengkapnya tentang fungsionalitas Riwayat Eksekusi di sini

Itu saja, teman-teman. Fungsionalitas baru telah dikembangkan, diuji, dan dipindahkan ke instans MS SQL Server yang tepat

Bagaimana cara mengekspor data secara otomatis dari SQL Server ke Excel?

Bagaimana cara mengekspor hasil SQL Server ke Excel?

Bagaimana cara membuat file Excel menggunakan prosedur tersimpan di SQL Server?

Bagaimana cara mengekspor data ke Excel dari SQL Server Management Studio?