Θα περιγράψω μια κλασική περίπτωση exporting, καθώς και τον τρόπο που αντιμετωπίστηκε. Παράλληλα θα αναφέρω κάποιους προβληματισμούς και θα ζητήσω όποιος θέλει να προσφέρει μια καλύτερη ή πιο κομψή λύση με βάση την εμπειρία του.

Η βάση: SQL Server 2008 Web Edition σε ένα dedicated server κάπου στο σύννεφο.

Η απαίτηση: Μια φορά τον μήνα γίνεται εξαγωγή ενός dataset στο οποίο συμμετέχει ένας πίνακας που κάποιοι κάνουν dataentry νύχτα-μέρα (ας πούμε οτι αυτό ο πίνακας λέγεται Dataentry_tbl). Το dataset αυτό περιέχει περίπου 800.000 εγγραφές και πρέπει να καταγράφεται σε ένα text file με την 1η γραμμή τα field-names, με κωδικοποίηση utf-8 και delimiters το double-pipe "||". Επίσης, πρέπει το όνομα του να είναι της μορφής Export_YYYYMMDD.txt όπου ΥΥΥΥ=year, MM=month, DD=day.

Η παραπάνω διαδικασία γίνεται με T-SQL με την χρήση μιας procedure που αναλαμβάνει να τα κάνει όλα.

Προβλήματα και λύσεις

1. Το πρώτο θέμα που πρέπει να αντιμετωπίσουμε είναι πως θα μπορέσουμε να απομονώσουμε τις εγγραφές του πίνακα Dataentry_tbl και να δουλέψουμε μόνο με αυτές χωρίς να επηρεάσουμε με κανένα τρόπο (page lock κλπ) τους χρήστες που κάνουν dataentry.

--> Καθαρίσω τον πίνακα που θα χρησιμοποιήσω για να βάλω τα δεδομένα ακριβώς που θέλω να εξάγω.
truncate table Data_Out_Tbl

--> Καταχωρώ τα IDs που θέλω να απομονώσω σε ένα προσωρινό πίνακα.
select ID
into #Temp_Ids
from Dataentry_tbl with(nolock)
where condition1 and condition2 and condition3

--> Σιγουρεύομαι οτι έχω βρει τουλάχιστον μια εγγραφή
if 0 =(select count(*) from #Temp_Card_Ids)
begin
            drop table #Temp_Ids
            select 'No rows to export.' as [Message]
            return
end

--> Εισάγω σε ένα πίνακα αυτά που θέλω να κάνω export κάνοντας join μεταξύ του temporary πίνακα και του βασικού μου πίνακα (with(nolock)).
insert into Data_Out_Tbl      
select  col1 as Column_1,
          col2 as Column_2,
          case when len(col3)=0 then null else col3 end as Column_3  ---> θα το αναλύσουμε παρακάτω αυτό
from Dataentry_tbl with(nolock)
inner join #Temp_Card_Ids on (#Temp_Card_Ids.crd_ID = Dataentry_tbl.crd_ID)

2. Εξαγωγή του πίνακα στο ascii αρχείο με την χρήση bcp και το 1ο πρόβλημα είναι πως θα εξάγω τις επικεφαλίδες;

Ως γνωστόν το bcp utility δεν μπορεί να εξάγει metadata (τουλάχιστον όχι με αυτόματο τρόπο). Για να το κάνω αυτό χρησιμοποιώ ένα όχι και τόσο κομψό τρόπο, αλλά λίγο πονηρό (την δουλειά του την κάνει πάντως - ευπρόσδεκτη κι εδώ οποιαδήποτε παρέμβαση). Θα δημιουργήσω δύο ascii αρχεία: Ένα με τις επικεφαλίδες (τα metadata δλδ) και ένα με τα data και μετά θα τα ενώσω σε ένα...:

    --- Δημιουργώ το όνομα του 1ου αρχείου με τις επικεφαλίδες.
    set @path='C:\exported_data\ftp_directory\'
    set @filename1 = 'Export_' +replace(CONVERT(VARCHAR(10), getdate(), 126),'-','') + '_1.txt'

    ---- Κρατώ στην μεταβλητή @colnames τα ονόματα των στηλών του πίνακα που με ενδιαφέρει.
    declare @colnames varchar(2000)
    SELECT @colnames = COALESCE(@colnames + '||', '') + column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Data_Out_Tbl';

    ----- Καταχώρησε την μεταβλητή αυτή σε ένα global temporary πίνακα ώστε να την δει αμέσως μετά το bcp.
    select @colnames as cname into ##ColumnsTable        

    ------ Εξάγω με BCP τα περιεχόμενα του πίνακα ##ColumnsTable στο αρχείο 1 (αυτό με τις επικεφαλίδες). -C65001 = utf-8
    set @Cmd = 'bcp "SELECT * FROM ##ColumnsTable" queryout "'+@path+@filename1+'" -C65001 -T -S"MSSQLSERVER2008" -c'   
    select @cmd
    EXEC xp_cmdshell @Cmd
   
    drop table ##ColumnsTable
 

    -- Δημιουργώ το 2o ascii αρχείο με τα data
    set @filename2 = 'Export_' +replace(CONVERT(VARCHAR(10), getdate(), 126),'-','') + '_2.txt'
    set @Cmd = 'bcp "SELECT * FROM Data_Out_Tbl" queryout "'+@path+@filename2+'" -C65001 -T -S"MSSQLSERVER2008" -c -t"||"'
    EXEC xp_cmdshell @Cmd
 

    -- Ενώνω τα 2 αρχεία σε 1 και διαγράφω να ενδιάμεσα ----------------
    set @filename3 = 'Export_' +replace(CONVERT(VARCHAR(10), getdate(), 126),'-','') + '.txt'
    set @Cmd = 'copy /b "'+@path+@filename1+'"+"'+@path+@filename2+'" "'+@path+@filename3+'"'
    select @Cmd
    EXEC xp_cmdshell @Cmd
   
    set @Cmd = 'del "'+@path+@filename1+'"'
    select @Cmd
    EXEC xp_cmdshell @Cmd

    set @Cmd = 'del "'+@path+@filename2+'"'
    select @Cmd
    EXEC xp_cmdshell @Cmd

3. Ακόμα ένα πρόβλημα του bcp: τα empty strings καταγράφονται στο exported file ως '\0' δηλαδή ως NUL (όχι το NULL της βάσης), αυτό έχει σαν αποτέλεσμα να "αγχώνονται"  σχεδόν όλα τα προγράμματα που προσπαθούν να τα διαβάσουν.

Για να ξεπεράσω αυτό το πρόβλημα μετέτρεψα όλα τα empty strings σε NULL ώστε το BCP να τα γράψει κάτω ως empty strings. Ναι είναι αλήθεια: Το bcp μεταφράζει το NULL (αυτό της βάσης) ως empty string και το empty string (της βάσης) σε... NUL - δηλαδή σε '\0' ή αλλιώς σε Ascii Zero που δηλώνει (παγκοσμίως) το τέλος οποιασδήποτε συμβολοσειράς, με αποτέλεσμα να "κόβονται" οι γραμμές από τα περισσότερα προγράμματα θα προσπαθούσαν να κάνουν UPLOAD αυτό το αρχείο! Τι να πεις...

 εξ' ου και το...:
case when len(col3)=0 then null else col3 end as Column_3
στο τελευταίο SELECT της ερώτησης 1.

4. Και μια διευκρίνηση για τους... αραβόφωνους!
Έστω οτι η γραμμογράφηση του αρχείου ήταν "ID || En_Name || Arabic_name || Date_In" και βλέπω τα εξής δεδομένα στο ascii file μου (sample data) :

ID || En_Name || Arabic_name || Date_In
876627||The Name 1||17/12/2013||تب ص
879726||The Name 32||19/12/2013||تب ص

Να μην τρομάξουν όσοι δουν το παραπάνω με την χρήση κάποιου editor (πχ. Notepad  ή Notepad++), νομίζοντας οτι δεν συμβαδίζει ο header με τα data. Το τελευταίο πεδίο στο παραπάνω αρχείο (όσο κι αν αυτό δεν φαίνεται!!) είναι η ημερομηνία. Στο συγκεκριμένο ascii έχουμε δύο γλώσσες με λίγο διαφορετική "κατεύθυνση" εμφάνισης: Κάποια data που εμφανίζονται από δεξιά προς τα αριστερά (Αραβικά) και κάποια εμφανίζονται από αριστερά προς τα δεξιά (Αγγλικά). Νομίζω (δεν το ξέρω σίγουρα - αλλά κρίνω εκ του αποτελέσματος) οτι κάπου εκεί κρύβεται το πρόβλημα, σε συνδυασμό πάντα με τα current local settings. Πάντως αν προσπαθήσετε να κάνετε upload το ascii στον SQL Server γίνεται μια χαρά αναγνωρίζοντας την σειρά των πεδίων όπως πρέπει!

Any suggestion, correction  or even opinion is more than welcome!

Thnx 4 reading