L'utilitaire bcp dans le client Microsoft SQL Server permet d'exporter et importer des données en mode natif ou en mode caractères. Ce guide pratique s'adresse à tous les administrateurs et concepteurs qui abordent pour la première fois cet outil natif d'export/import de données, outil natif qui est de loin le plus efficace.
Dans le jargon classique :
Classiquement les outils clients MS SQL Server sont installés dans le répertoire C:\Program Files\Microsoft SQL Server\<version>\tools\binn. <version> dépend de la version du client Microsoft SQL Server installé (2000, 2005, 2008...).
L'utilitaire bcp est disponible dans le répertoire C:\Program Files\Microsoft SQL Server\<version>\tools\binn, répertoire inscrit dans la variable %PATH% de l'environnement Windows.
Attention : bcp est un binaire qui existe également dans le client Sybase Adaptive Server Enterprise et en fonction de la localisation des répertoires Sybase et Microsoft SQL Server dans la variable d'environnement %PATH%, le binaire bcp de Sybase peut prendre la main au lieu du binaire bcp de Microsoft SQL Server. Pour adresser ce point, appeler le binaire bcp de Microsoft SQL Server dans une invite de commandes DOS en positionnant C:\Program Files\Microsoft SQL Server\<version>\tools\binn au début de la variable %PATH%.
Voici des exemples pratiques avec le client SQL Server 2005 (version=90).
DOS> C:\Program Files\Microsoft SQL Server\tools\90\binn\bcp -?
ou se positionner dans le répertoire C:\Program Files\Microsoft SQL Server\tools\90\binn :
DOS> cd C:\Program Files\Microsoft SQL Server\tools\90\binn
DOS> bcp -?
La syntaxe pour exporter une table ou une vue dans un fichier fichier.txt est la suivante :
DOS> bcp basededonnees.proprietaire.matable out fichier.txt -Sserveur
[-Uutilisateur] [-Pmotdepasse] [-T]
[-t separateur de champs] [-r separateur de ligne] [-c] [-n]
Le mot clé "out" indique le mode export de données.
Les options -c (mode caractères) et -n (mode natif) sont exclusives l'une de l'autre.
Dans les exemples qui suivent :
L'option -n exporte les données d'une table ou d'une vue en mode natif. Pour exporter la table AUM_PORTFOLIO (propriétaire dbo) en mode natif :
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpn -SPAR1DB1-02 -Usa -P****** -n
Starting copy... 37491 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 500 Average = (74982.00 rows per sec.)
Le fichier fichier.bcpn produit est dans un format propriétaire qui n'est pas humainement lisible. Le format en mode natif est adapté pour les transferts de données d'un serveur à un autre serveur ou si la table contient des colonnes de type text (données non structurées) contenant des retours chariot etc...
L'option -c exporte les données en mode caractères, le fichier produit est lisible et exploitable (awk etc...). Avec l'option -c, les options -t (séparateur de champs) et -r (séparateur de lignes) sont utilisables. Pour exporter la table AUM_PORTFOLIO (propriétaire dbo) en mode caractères :
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -SPAR1DB1-02 -Usa -P****** -c
DOS> type fichier.bcpc
1000005074241368 2009-03-19 00:00:00.000 EUR 187413059.270000000 1000005081554198 2009-03-20 00:00:00.000 EUR 32552630.210000000
Par défaut, le séparateur de champs est la tabulation et le séparateur de lignes est \r\n.
Pour spécifier des séparateurs non standards, par exemple le point virgule comme séparateur de champs et #\r\n comme séparateur de lignes : utiliser les arguments -t et -r.
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -r"#\r\n" -c
DOS> type fichier.bcpc
1000005074241368;2009-03-19 00:00:00.000;EUR;187413059.270000000# 1000005081554198;2009-03-20 00:00:00.000;EUR;32552630.210000000#
Si le compte Windows utilisé (domaine\login) est autorisé à accéder au serveur et à la base de données, l'authentification SQL avec les options -U et -P ne sont plus nécessaires, la commande bcp out peut être utilisée en authentification intégrée avec l'option -T (Trusted Connection) :
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc -SPAR1DB1-02 -T -t";" -r"#\r\n" -c
Jusqu'ici l'instance SQL Server est une instance par défaut sur la machine PAR1DB1-02. Lorsqu'il s'agit d'une instance nommée, comme par exemple l'instance nommée MOSS_DATA01 sur la machine PARDMOS105, le paramètre -S dans la commande bcp out indiquant le serveur s'écrit -S<machine>\<instance nommée> :
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO out fichier.bcpc
-SPARDMOS105\MOSS_DATA01 -Usa -P****** -t";" -r"#\r\n" -c
Le binaire bcp de SQL Server permet d'exporter des requêtes ou des jeux de résultats de procédures stockées avec l'option queryout. Cette option queryout permet notamment de s'affranchir de la création de vues ou d'exporter très simplement les résultats d'une procédure stockée effectuant des dénormalisations.
DOS> bcp "requete" queryout fichier.txt -Sserveur
[-Uutilisateur] [-Pmotdepasse] [-T]
[-t separateur de champs] [-r separateur de ligne] [-c] [-n]
Quelques exemples très simples :
Export de la table AUM_PORTFOLIO avec des clauses WHERE
DOS> bcp "select * from FinanceKiosk.dbo.AUM_PORTFOLIO where PortfolioID > 50000"
queryout fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -c
Export du résultat d'une procédure stockée
DOS> bcp "exec FinanceKiosk.dbo.uspGetOrderbyPortfolioID @Portolio_id=16"
queryout fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -c
La syntaxe pour importer dans une table les données provenant d'un fichier fichier.txt est la suivante :
DOS> bcp basededonnees.proprietaire.matable in fichier.txt -Sserveur
[-Uutilisateur] [-Pmotdepasse] [-T]
[-t separateur de champs] [-r separateur de ligne] [-b] [-E] [-c] [-n]
Le mot clé "in" indique le mode import de données.
Les options -c (mode caractères) et -n (mode natif) sont exclusives l'une de l'autre.
Comme pour l'export de données avec bcp out :
Les options -b et -E sont importantes lors des imports et sont abordées dans les paragraphes qui suivent.
Pour importer dans la table AUM_PORTFOLIO les données provenant d'un fichier texte pour lequel le séparateur de champs est le point virgule et le séparateur de lignes #\r\n :
DOS> type fichier.bcpc
1;MQ;1# 9;IPS;1#
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -r"#\r\n" -c
L'import avec bcp dans une table comportant des indexes, des colonnes de type text ou des déclencheurs (triggers) est journalisé. En fonction de la volumétrie à importer, la taille du journal de transactions de la base de données peut être insuffisante.
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -c
1000 rows sent to SQL Server. Total sent: 120000 1000 rows sent to SQL Server. Total sent: 121000
La saturation du journal est notifiée par le binaire bcp dans la sortie standard avec l'erreur 9002.
SQLState = 42000, NativeError = 9002
Error = [Microsoft][SQL Native Client][SQL Server]The transaction log for database 'FinanceKiosk' is full.
To find out why space in the log cannot be reused,
see the log_reuse_wait_desc column in sys.databases
L'option -b (batch size) de la commande bcp in permet d'éviter ces écueils et ordonne de lancer la commande commit toutes les n lignes importées. Pour lancer un commit toutes les 10000 lignes : spécifier -b10000.
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -b10000 -c
Le dimensionnement du paramètre "batch size" -b<nb lignes> va dépendre de la taille du journal de la base de données et de la structure de la table (indexes, colonnes text etc...).
Lorsque la table contient une colonne de type identity, l'option -E doit être indiquée dans la commande bcp in si l'on souhaite que les identifiants dans le fichier source soient conservés lors de l'import. Sans l'option -E, de nouvelles valeurs pour la colonne identity sont attribuées.
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -b10000 -E -c
L'option -E équivaut à la commande Transact SQL set identity_insert <table> on | off.
L'option -E peut être indiquée dans la commande bcp in même si la table ne contient pas de colonne de type identity.
Depuis SQL Server 2005, si la commande bcp est exécutée avec un utilisateur disposant de droits plus restreints que le rôle dbo (database owner), le droit ALTER doit être donné à cet utilisateur sur la table contenant la colonne identity. Sans ce droit, la commande bcp avec l'option -E est en échec avec le message 1088.
DOS>bcp RPM..SPA in REPORT.csv -t";" -E -USPA -SPAR1DB3-24 -P********** -c
SQLState = 37000, NativeError = 1088 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot find the ject "RPM..SPA" because it does not exist or you do not have permissions.
Voici la commande pour attribuer le droit ALTER.
grant alter on [proprietaire].[table] to [user]
grant alter on dbo.SPA to RPM_write
Le droit ALTER sur la table est nécessaire si l'une des conditions ci-dessous est remplie :
Comme tout binaire qui se respecte, bcp retourne un code erreur en cas d'échec, code erreur récupéré par la variable DOS %ERRORLEVEL% à exploiter.
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -b10000
Starting copy... ... SQLState = 22018, NativeError = 0 Error = [Microsoft][SQL Native Client]Invalid character value for cast specification BCP copy in failed
DOS> echo %ERRORLEVEL%
1
Par défaut, le nombre maximal d'erreurs autorisé lors de l'import avec bcp in est fixé à 10.
L'option -m (maxerrors) permet d'étendre cette limite à des valeurs personnalisées et plus élevées : par exemple 1000 erreurs maximum rencontrées
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -b10000 -m1000 -c
Attention : lorsque ce nombre maximal d'erreurs n'est pas atteint, le binaire bcp retourne 0 (succès).
L'option -e indique le fichier d'erreurs dans lequel doivent être consignées les lignes rejetées lors de l'import : pour notifier les erreurs dans le fichier errfile.txt
DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -SPAR1DB1-02 -Usa -P****** -t";" -b10000 -eerrfile.txt -c
DOS> type errfile.txt
#@ Row 5, Column 1: Invalid character value for cast specification @# 43659;5;4911-403C-98;1;772;1;2039.9940;.0000
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 03/2011 | Version initiale |
| 1.1 | 04/2011 | Spécification du droit grant alter on dbo.table to user pour les imports avec bcp dans des colonnes de type identity |