Discussion :

[Piolet]

Bonjour,

voila comme dit dans le titre, j'aimerais exporter le résultat d'une requête dans un fichier texte, si possible avec un format CSV, mais ca on verra après...

J'ai bien trouvé l'utilitaire "bcp", mais j'aimerais le faire directement dans ma procédure stockée, si c'est possible, à l'image de BULK mais en export...

et ca j'ai pas trouvé.

Qq1 a-t-il une idée ?

[ylarvor]

bonjour,

avec xp_cmdshell, vous pouvez peut etre executer bcp depuis une procedure stockée...

[cavo789]

Il faut toutefois que l'administrateur SQL Server ait autorisé xp_cmdshell ce qui, logiquement, ne devrait pas être le cas (trop risqué).

Ton export, faut-il le faire une seule fois ou régulièrement ? Désolé pour cette bête question mais cela va impacter la procédure :

Une seule fois : dans SSMS, quand tu fais un execute de ton query, tu peux afficher les données sous forme de texte.

Régulièrement : il me semble que la meilleure façon serait de passer via un package SSIS. Il existe plusieurs tutoriels sur developpez.net.

[Piolet]

Il faut toutefois que l'administrateur SQL Server ait autorisé xp_cmdshell ce qui, logiquement, ne devrait pas être le cas (trop risqué).

Ton export, faut-il le faire une seule fois ou régulièrement ? Désolé pour cette bête question mais cela va impacter la procédure :

Une seule fois : dans SSMS, quand tu fais un execute de ton query, tu peux afficher les données sous forme de texte.

Régulièrement : il me semble que la meilleure façon serait de passer via un package SSIS. Il existe plusieurs tutoriels sur developpez.net.

je connais SSIS, mais la visiblement on cherchait une autre solution

j'ai essayé avec bcp, comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

bcp "exec sp_select_for_export" queryout "c:\test.txt" -S MONSERVER -U MONLOGIN -P MONPASSWORD -f "c:\format2.fmt"

mais ca me sort l'erreur suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Erreur de séquence de fonction

alors que grosso modo la meme chose mais avec une requete simple à la place de l'appel de proc stock, ca marchait.

(Je précise que le fichier de format, variait selon les cas)

donc la, bcp ne marche pas avec les appels de proc stock si je comprends bien ?

[ylarvor]

tu peux exporter ton résultat dans une table de tempdb depuis ta procédure stockée et récupérer ton résultat par une simple requête et bcp si tu n'as pas problème de concurrence.
Bon XP_CMPSHELL n'est pas recommandé, c'est vrai!

[Piolet]

tu peux exporter ton résultat dans une table de tempdb depuis ta procédure stockée et récupérer ton résultat par une simple requête et bcp si tu n'as pas problème de concurrence.
Bon XP_CMPSHELL n'est pas recommandé, c'est vrai!

ok..mais comment j'appelle l'exportation dans la table temporaire ?
et surtout, ma table temporaire sera-t-elle accessible de l'exterieur via l'appelle de bcp ?

[ylarvor]

Ben à vrai dire, je ne pensais pas à une table temporaire, accessible seulement pendant la session mais à une table EXPORT que tu crée dans TEMPDB puisqu'elle n'a pas pour objet d'être pérenisée, sauvegardée...

Tu peux y acceder comme pour n'importe quel SELECT à une table EXPORT dans une base TEMPDB.

[Piolet]

Ben à vrai dire, je ne pensais pas à une table temporaire, accessible seulement pendant la session mais à une table EXPORT que tu crée dans TEMPDB puisqu'elle n'a pas pour objet d'être pérenisée, sauvegardée...

Tu peux y acceder comme pour n'importe quel SELECT à une table EXPORT dans une base TEMPDB.

ok....mais le problème est le même,

en fait, c'est une tache de fond, lancée chaque soir, donc idéalement il faut remplir la temporaire (ou d'export, comme tu veux ) avant de lancer bcp, et ca, comment je fais

l'idéal avec ma proc stock, c'est qu'elle jouait ce role, elle ramenait les bonnes données au bon moment....

[ylarvor]

c'est le travail de l'agent sql server, voici un script, pour te demontrer que c'est possible, pour l'agent sql server qui export des données avec bcp et crée un cd de données.

il te suffit juste de rajouter une etape supplementaire, en amont dans laquelle tu appelle un clone de ta procédure stockee avec à la fin de la procédure un INSERT INTO TEMPDB.DBO.EXPORT SELECT col1,col2,col3 FROM Matable INNER JOIN ....

si tu ne peux pas utiliser un INSERT INTO SELECT, il te reste la solution d'un curseur ligne à ligne.

USE [msdb]
GO
/****** Objet : Job [export] Date de génération du script : 11/07/2008 10:38:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Objet : JobCategory [[Uncategorized (Local)]]] Date de génération du script : 11/07/2008 10:38:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'export',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Pas de description disponible.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'PLUTON\yann.larvor', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Objet : Step [export] Date de génération du script : 11/07/2008 10:38:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'export',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'bcp test.dbo.matable out "c:\save2.txt" -S localhost -U SA -P 4d1ee649 -c -T',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Objet : Step [gravure] Date de génération du script : 11/07/2008 10:38:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'gravure',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'CreateCD -r:u "C:\save2.txt"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'planifie',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20070728,
@active_end_date=99991231,
@active_start_time=120500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

[Piolet]

c'est le travail de l'agent sql server, voici un script, pour te demontrer que c'est possible, pour l'agent sql server qui export des données avec bcp et crée un cd de données.

il te suffit juste de rajouter une etape supplementaire, en amont dans laquelle tu appelle un clone de ta procédure stockee avec à la fin de la procédure un INSERT INTO TEMPDB.DBO.EXPORT SELECT col1,col2,col3 FROM Matable INNER JOIN ....

si tu ne peux pas utiliser un INSERT INTO SELECT, il te reste la solution d'un curseur ligne à ligne.

en fait, dans ton code, j'ai a prendre les 2 premières parties, la dernière, celle de la gravure, m'en s'coue, c'est ca ? tout le reste c'est bon au nom de la proc stoc et paramètre bcp près, non ?

[Piolet]

non en fait, seulement la 2eme partie me convient
vu que t'y appelles la proc stoc et qu'en suite tu appelles la commande bcp...
hum...je vais voir si ca marche

[Piolet]

Ah si, une question

ma proc stock prend des paramètres en entrée, genre 2 dates, comment c'est ti qu'on fait ?

[ylarvor]

là, je ne sais pas... si quelqu'un a une solution pour utiliser SSIS avec des paramètres. Peut être d'autres tables temporaires...autre solution, tu executes la procedure depuis ton programme client une fois par jour et tu execute la tache de l'agent de façon asynchrone.

Sinon, selon le contexte de production qui est le tien, il te reste la solution de XPCMDSHELL pour exécuter bcp depuis la procédure stockée... là, aucun problème de paramètres puisque le bcp s'execute depuis la procédure...

[Piolet]

là, je ne sais pas... si quelqu'un a une solution pour utiliser SSIS avec des paramètres. Peut être d'autres tables temporaires...autre solution, tu executes la procedure depuis ton programme client une fois par jour et tu execute la tache de l'agent de façon asynchrone.

Sinon, selon le contexte de production qui est le tien, il te reste la solution de XPCMDSHELL pour exécuter bcp depuis la procédure stockée... là, aucun problème de paramètres puisque le bcp s'execute depuis la procédure...

et ta solution de job ? avec des paramètres c'est pas possible ?

[ylarvor]

Dans un job, il n'y a pas de paramètre mais tu peux appeler des tables temporaires dans ta procédures stockées pour aller chercher les dates qui t'intéresse.

[Piolet]

Dans un job, il n'y a pas de paramètre mais tu peux appeler des tables temporaires dans ta procédures stockées pour aller chercher les dates qui t'intéresse.

ok, alors autre question, est-ce que je peux inclure le job que tu m'as filé, dans un curseur.

En gros je dois récupérer des id, et pour chacun de ces id, appeller ce job, ca se fait ca ou pas du tout ?

[ylarvor]

tu dois placer ton ID dans une table temporaire. Il est possible d'appeler un job par sp_start_job ensuite.

[Piolet]

tu dois placer ton ID dans une table temporaire. Il est possible d'appeler un job par sp_start_job ensuite.

tu aurais pas un exemple ?

parce que, deja, je viens de percuter que ton exemple de vendredi, servait à ajouter un job sur le server, facon ligne de commande, sans passer par le SQL Server Agent.

donc j'essaie de le remettre à ma sauce, tout en sachant que ma proc stock doit prendre un id en paramètre et que ce dernier n'est pas encore trouvé

[ylarvor]

Laisse tomber cet exemple, je te l'avais indiqué juste pour te prouver qu'avec l'agent sql server, il était possible d'utiliser bcp pour creer un cd.

ton problème tel que je le comprend, tu as une procédure stockée qui fait un traitement sur demande. Ce que je rajoute, tu stockes ton résultat à exporter dans la base tempdb avec INSERT INTO TEMPDB.DBO.MATABLE SELECT *... puis tu appelles un job grace à sp_start_job, qui est documenté avec exemple sur msdn, ton job, que tu as créé à la main avec l'agent sql server se contente de prendre la table TEMPDB.DBO.MATABLE résultat et d'exporter le résultat sous forme de fichier texte. On a mis du temps, mais c'est exactement ce que tu demandais au début de ton questionnement, comment exporter une table sous forme de fichier texte, non ? et sans XP_CMD_SHELL!

[Piolet]

Laisse tomber cet exemple, je te l'avais indiqué juste pour te prouver qu'avec l'agent sql server, il était possible d'utiliser bcp pour creer un cd.

ton problème tel que je le comprend, tu as une procédure stockée qui fait un traitement sur demande. Ce que je rajoute, tu stockes ton résultat à exporter dans la base tempdb avec INSERT INTO TEMPDB.DBO.MATABLE SELECT *... puis tu appelles un job grace à sp_start_job, qui est documenté avec exemple sur msdn, ton job, que tu as créé à la main avec l'agent sql server se contente de prendre la table TEMPDB.DBO.MATABLE résultat et d'exporter le résultat sous forme de fichier texte. On a mis du temps, mais c'est exactement ce que tu demandais au début de ton questionnement, comment exporter une table sous forme de fichier texte, non ? et sans XP_CMD_SHELL!

c'est ca, sauf qu'en plus, je dois passer un argument à ma proc stock, pour qu'elle me ramène les bonne données par rapport a un id...

en gros si je devais l'écrire en pseudo code, ca donnerait ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
for(int id : ids) {
   DROP tableTMP
   EXEC ma_proc_stoc id => tableTMP
   bcp tableTMP "c:\id_export.txt"
}