Sql server 2005 - gestion de la largeur des colonnes d'une requete liee a un email (sp_send_dbmail)

0/5 (3 avis)

Snippet vu 14 080 fois - Téléchargée 17 fois

Commenter

Contenu du snippet

A partir de la version 2005, l'envoi d'un Email à partir de SQL Server a été grandement simplifié (compte SMTP) et permet d'associer au message un tableau de données (requête). Cette solution présente un inconvénient majeur de présentation car la largeur d'une colonne de type caractère correspond à la largeur de la structure au lieu de la largeur des données effectives. Inédite, l'astuce proposée ajoute la gestion de la largeur des colonnes de la requête liée à l'envoi pour les données de type caractère.

Source / Exemple : 


------------------------ Requête sans gestion de la largeur des colonnes --------------------------------
-- Variables locales
Declare @SQL varchar(max), 
                 @PROFIL nvarchar(128), 
                 @RCPT varchar(60), 
                 @TXT char(19);

-- Compte SMTP
Set @PROFIL= (SELECT TOP 1 Name FROM msdb.dbo.sysmail_profile);
-- Destinataire de l'envoi (à saisir pour la demo.)
Set @RCPT= '< Adresse à saisir >';
-- Corps du message
Set @TXT= 'Voir ci-dessous :' + char(13) + char(10);

-- Requête associée à l'envoi
USE  AdventureWorks ; 
Set @SQL= 'Select FirstName + '' '' + LastName [Nom complet], EmailAddress [Adr. courriel] From Person.Contact Where ContactID <11'

-- Poster l'envoi
EXEC msdb.dbo.sp_send_dbmail @profile_name=@PROFIL, @recipients=@RCPT, @subject ='Requête associée à l''envoi sans gestion des largeurs', @body= @TXT, @body_format ='TEXT', 
                                                                     @importance ='High', @sensitivity ='Normal', @query=@SQL, @execute_query_database = 'AdventureWorks', @attach_query_result_as_file =0, @query_result_header=1, @exclude_query_output=1;
GO
------------------------ Requête avec gestion de la largeur des colonnes --------------------------------
-- Variables locales
Declare @SQL varchar(max), 
                 @PROFIL nvarchar(128), 
                 @RCPT varchar(60), 
                 @TXT char(19),
                 @LEN1 int,
                 @LEN2 int;

-- Compte SMTP
Set @PROFIL= (SELECT TOP 1 Name FROM msdb.dbo.sysmail_profile);
-- Destinataire de l'envoi (à saisir pour la demo.)
Set @RCPT= '< Adresse à saisir >';
-- Corps du message
Set @TXT= 'Voir ci-dessous :' + char(13) + char(10);
-- Sélection de la base de données 
USE  AdventureWorks ;

-- Calculer les largeurs des colonnes 
Select @LEN1=Max(Len(FirstName + ' ' + LastName)), @LEN2=Max(Len(EmailAddress)) From Person.Contact Where ContactID <11
-- Tenir compte de la largeur des en-têtes
If (ISNULL(@LEN1, 0)<Len('Nom complet')) Set @LEN1=Len('Nom complet');
If (ISNULL(@LEN2, 0)<Len('Adr. courriel')) Set @LEN2=Len('Adr. courriel');

-- Requête associée à l'envoi
Set @SQL= 'Select Substring(FirstName + '' '' + LastName, 1, ' + Convert(varchar(3), @LEN1 + 1) + ') [Nom complet], Substring(EmailAddress, 1, ' + Convert(varchar(3), @LEN2 + 1) + ') [Adr. courriel] From Person.Contact Where ContactID <11'

-- Poster l'envoi
EXEC msdb.dbo.sp_send_dbmail @profile_name=@PROFIL, @recipients=@RCPT, @subject ='Requête associée à l''envoi avec gestion des largeurs', @body= @TXT, @body_format ='TEXT', 
                                                                     @importance ='High', @sensitivity ='Normal', @query=@SQL, @execute_query_database = 'AdventureWorks', @attach_query_result_as_file =0, @query_result_header=1, @exclude_query_output=1;
GO
-----------------------------------------------------------------------------------------------------------------------------------

Conclusion :


Il suffit d'ajouter votre adresse (@RCPT) dans les deux cas proposés en démonstration sur la base de données AdventureWorks pour tester le code source (cf. capture d'écran). Pour résumer l'astuce exposée, la fonction « SUBSTRING » permet de fixer les largeurs des colonnes.

A voir également

Ajouter un commentaire Commentaires
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
23 août 2007 à 09:50
Pour information, l'objet graphique calendrier (sélecteur de date) est utilisé par l'éditeur d'état. Le correctif cité dans le commentaire précédent concerne donc un bogue de SQL Server Reporting.
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
22 août 2007 à 13:37
A tous,

Ayant contourné l'anomalie de présentation avec cette astuce personnelle, je n'ai pas cherché à savoir si il existait un correctif à celle-ci. Si quelqu'un connait cette information, elle serait la bienvenue.
Par contre, je peux indiquer qu'il faut contacter le support technique pour télécharger le correctif n°936305 avec le SP2 pour obtenir l'objet calendrier au format date français.

Merci d'avance
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
20 août 2007 à 14:58
Pour les débutants ne sachant pas comment configurer la messagerie SQL Server 2005, je conseille la lecture de l'excellent document mis à disposition par Fabrice Romelard à l'adresse suivante :
http://fromelard.free.fr/Scripts/SQL_SQLServer2005Mail/SQL_SQLServer2005Mail.pdf
S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (FENETRES)