Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email

Στις καθημερινές εργασίες των εταιριών είναι συχνή ανάγκη να πρέπει να εξάγονται καθημερινά δεδομένα από τη βάση και να δίνονται σε κάποιο Excel.

Σε αυτά τα Excel συνήθως θέλουμε να έχουμε κάποιο συγκεκριμένο όνομα μαζί με ημερομηνία και να αποθηκεύονται σε συγκεκριμένο path. Πολύ χρήσιμο είναι να στέλνονται αυτά τα Excel απευθείας με email από τον SQL Server τον ίδιο.

Στο άρθρο θα αναλύσουμε βήμα βήμα τον τρόπο και το τι απαιτείται για να γίνουν όλα τα παραπάνω χωρίς την χρήση πακέτων SSIS.

Τα προαπαιτούμενα

Θα πρέπει να έχουμε προεγκαστήσει έναν provider από την Microsoft που θα μας επιτρέπει να γράφουμε τα δεδομένα μας από τη βάση στο Excel. Μπορούμε να κατευάσουμε την 12 έκδοση από εδώ.

Πρέπει το account που σηκώνει το service account του SQL Server να έχει δικαιώμα να γράψει στο path αυτών τον αρχείων στο λειτουργικό όπως επίσης να είναι sysadmin στο instance.

Τέλος θα πρέπει να έχουμε τρέξει τα παρακάτω στο instance που θα μας επιτρέψουν να τρέξουμε το T-SQL script να γράφει στο Excel:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'xp_cmdshell', 1;  
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Για να δούμε τι providers έχουν εγκατασταθεί στο instance:

EXEC master.dbo.sp_MSset_oledb_prop

Το παράδειγμα

Έχουμε έναν πίνακα customers στο οποίο έχουμε εγγραφές. Θέλουμε να τον εξάγουμε σε ένα Excel με χρώματα στους τίτλους και μορφοποιήσεις με όνομα customers_yyyymmdd.xlsx. Σαν επόμενο βήμα θέλουμε να στέλνουμε αυτό το Excel με ένα email σε κάποιον παραλήπτη.

Οπότε για το παράδειγμα έχουμε περάσει μόνο 3 εγγραφές στον πίνακα.

insert into mig_db.dbo.customers
values
('Stratos'),
('Nikos'),
('Giorgos');

select * from mig_db.dbo.customers;
Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
01

Για να έχουμε τις μορφοποιήσεις του Excel που θέλουμε, δηλαδή Bold, περιθώρια, κίτρινα κελιά φτιάχνουμε ένα template.xlsx στο οποίο θα έχουμε κάνει όλες τις μορφοποιήσεις αυτές και το έχουμε αποθηκεύσει στο path που θα εξάγονται τα αρχεία.

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
02

Το επόμενο βήμα είναι να έχουμε βάλει στο script μας να κάνει copy το template σε καινούργιο αρχείο με το όνομα που θα θέλουμε τελικός να έχει π.χ. customers_20210101.xlsx.

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO
Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
03

Οπότε θα δούμε αμέσος ότι δημιουργήθηκε το αρχείο στον φάκελο μας το οποίο ακόμα δεν περιέχει εγγραφές.

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
04

Στο επόμενο βήμα θα γίνει η μεταφορά τον εγγραφών στο Excel. Εκεί θα πρέπει:

  • στην μεταβλητή @importq να βάλουμε το query που θα διαβάζει από τον πίνακα
  • στην μεταβλητή @dbfile να βάλουμε το path του αρχείου με το όνομα που θα έχει όπως κάναμε στο προηγούμενο βήμα με την εντολή copy (C:\Share\customers_’+convert(varchar(15),getdate(),112)+’.xlsx’)
  • Θα πρέπει να αλλάξουμε το όνομα στο template του φύλλου του Excel σε Sheet1 (αν δεν είναι ήδη) αλλιώς θα πρέπει να το αλλάξουμε στη μεταβλητή @dbfile στο select * from [Sheet1$]

USE mig_db
GO
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go
Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
05

Αυτό ήταν, αν ανοίξουμε τώρα το Excel θα δούμε τις εγγραφές με την μορφοποίηση που θέλουμε.

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
06

Σε προηγούμενο άρθρο είχαμε δει πως ενεργοποιούμε το Database Mail στον SQL Server. Αφού κάνουμε αυτά τα βήματα με το παρακάτω script μπορούμε να στείλουμε το Excel και με Email.

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

Οπότε αν πάνε όλα καλά θα έρθει το Email όπως φαίνεται στην από κάτω εικόνα.

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
07

Συγκεντρωτικά ο κώδικας:

declare @Sql varchar(200)
set @Sql='COPY C:\Share\template.xlsx C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
print @Sql
exec xp_cmdshell @Sql
GO

set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000), @importq varchar(2000)
set @importq = 'select * from mig_db.dbo.customers'
set @dbfile = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'+@importq
print @dbfile
exec(@dbfile)
go

declare @dbfile2 varchar(1000)
set @dbfile2 = 'C:\Share\customers_'+convert(varchar(15),getdate(),112)+'.xlsx'
EXEC msdb.dbo.sp_send_dbmail  
@profile_name = 'gmail',  
@recipients = 'account@dataplatform.gr',  
@subject = 'Customers Report',  
@body = 'Here is the daily customer report.',
@file_attachments = @dbfile2;
print @dbfile2

Και αν θέλω όλη αυτή η διαδικασία να γίνεται αυτόματα κάθε μέρα μια συγκεκριμένη ώρα;

Μπορούμε απλά να φτιάξουμε ένα Job στον SQL Server Agent.

Η διαδικασία είναι να κάνουμε δεξί κλικ New, Job… , Steps, New…, να δώσουμε ένα όνομα στο step, να επιλέξουμε τη βάση που έχει τα δεδομένα και να προσθέσουμε τα sql scripts που φτιάξαμε πριν, πατάμε ΟΚ και τέλος στην καρτέλα Schedules να ορίσουμε το πότε θα τρέχει.

Πώς μπορούμε να εξάγουμε καθημερινά δεδομένα από τον SQL Server σε Excel και να τα στέλνουμε με email
08

Υπάρχει εναλλακτικός πιο άμεσος τρόπος;

Σε περίπτωση που δεν μας νοίαζει η εμφάνιση του Excel με bold τίτλους, χρώματα κτλ, δεν μας νοιάζει να κρατάμε αυτόματα τα reports και να τους δίνουμε όνομα με την ημερομηνία δυναμικά, τότε μπορούμε κατά την εκτέλεση της procedure που στέλνει το email msdb.dbo.sp_send_dbmail να ορίσουμε απευθείας το query που θέλουμε να εκτελέσουμε και να το επισυνάψει ως attachment όπως με τον προηγούμενο τρόπο:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'gmail',
@recipients = 'account@dataplatform.gr',
@query = '
set nocount on;
USE mig_db;
go
select * from dbo.customers
go',
@subject = 'Customers Report',
@body = 'Here is the daily customer report.',
@query_attachment_filename='customers.xls',
@query_result_header = 1,
@query_result_width = 256,
@query_result_separator = ',',
@exclude_query_output = 1,
@append_query_error = 1,
@query_no_truncate = 0,
@query_result_no_padding = 1,
@attach_query_result_as_file = 1;
Μοιράσου το

Αφήστε μία απάντηση