Parametre pour Query sp_send_dbmail

[olibara]

* Bonjour, *

Il me semble avoir lu quelque part que le Query assigné a sp_Send_Dbmail n'autorisait pas de paramètres

Avant de me casser les dents là dessus pouvez-vous me le confirmer ?

N.B. : Accessoirement peut-on faire un query multiple (plusieurs requêtes distincte séparées par ';' ) dans sp_Send_DbMail ?

Merci de vos réponses

[mikedavem]

Il me semble avoir lu qq part que le Query assigné a sp_Send_Dbmail n'autorisait pas de parametres

La requête est exécutée dans une session distincte de celle utilisée par la commande sp_send_dbmail. Il faudra donc jouer avec lorsque vous allez construire votre requête qui sera exécutée par sp_send_dbmail.

N.B. : Accessoirement peut-on faire un query multiple (plusieurs requetes distincte séparées par ';' ) dans sp_Send_DbMail ?

A priori il n'y a pas de souci à cela tant que la syntaxe générale soit correcte.

++

[elsuket]

Bonjour,

Je confirme pour l'exécution de plusieurs requêtes.

@++

[olibara]

Merci Elsuket

La requête est exécutée dans une session distincte de celle utilisée par la commande sp_send_dbmail. Il faudra donc jouer avec lorsque vous allez construire votre requête qui sera exécutée par sp_send_dbmail.

Pour etre plus concret puis-je faire qq chose comme ceci ?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare @Chauffeur int;
SET @Chauffeur=1;
 
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com',
    @query = 'SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
    WHERE (CAST(DATE AS DATE)=CAST(GETDATE() AS DATE) OR MailStatus=0)
    AND ChauffeurID=@Chauffeur' ,
    @subject = 'Chauffeur Gedaan !',
    @attach_query_result_as_file = 0 ;
 
   UPDATE [Clark].[dbo].[TotalChauffeur]
   SET MailStatus=1
   WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
   OR MailStatus=0;

(Note la presence du parametre @Chauffeur dans le query du mail)

Sinon je suppose que je dois construire la chaine de requete avant de l'assigner !

[elsuket]

Héhé non
Pour cela il faut du SQL dynamique :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @sql nvarchar(max)
 
SET	@sql = 'EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients_IN'
		+ ', @query = ''SELECT *  FROM Clark.dbo.TotalChauffeur WHERE (CAST(DATE AS date) = CAST(GETDATE() AS date) OR MailStatus = 0) AND ChauffeurID = ' + CAST(@Chauffeur AS varchar(10))
		+ ''', @subject = @subject_IN, @attach_query_result_as_file = 0'
 
EXEC	sp_executesql
		@sql
		, @params = '@recipients_IN varchar(128), @subject_IN nvarchar(255)'
		, @recipients_IN = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com'
		, @subject_IN = 'Chauffeur Gedaan !'
 
UPDATE	Clark.dbo.TotalChauffeur
SET	MailStatus = 1
WHERE	CAST(DATE AS date) = CAST(GETDATE() AS date)
OR	MailStatus = 0

Regarde les remarques que j'ai posté dans ton sujet sur les procédures stockées dans le trigger

@++

[olibara]

Merci Elsuket

Oui je vois !
J'avais cru m'en tirer avec un truc du genre, mais je ne fais que deplacer le problème

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 declare @str varchar(max);
 
 @str='SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
    WHERE (CAST(DATE AS DATE)=CAST(GETDATE() AS DATE) OR MailStatus=0)
    AND ChauffeurID=CAST(@Chauffeur as varchar)';
 
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com',
    @query = @str ,
    @subject = 'Chauffeur Gedaan !',
    @attach_query_result_as_file = 0 ;
 
   UPDATE [Clark].[dbo].[TotalChauffeur]
   SET MailStatus=1
   WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
   OR MailStatus=0;

[elsuket]

Rien ne t'empêche d'écrire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE @str nvarchar(max)
DECLARE @ChauffeurID int = 1
 
 SET @str='SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
    WHERE (CAST(DATE AS DATE)=CAST(GETDATE() AS DATE) OR MailStatus=0)
    AND ChauffeurID=' + CAST(@ChauffeurID AS varchar(10));
 
EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'oxg@skynet.com;bartmissa@wareever.com;machin@recutex.com',
    @query = @str ,
    @subject = 'Chauffeur Gedaan !',
    @attach_query_result_as_file = 0 ;
 
   UPDATE [Clark].[dbo].[TotalChauffeur]
   SET MailStatus=1
   WHERE CAST(DATE AS DATE)=CAST(GETDATE() AS DATE)
   OR MailStatus=0;

C'est plus propre que ce que je t'ai donné au départ

@++

[olibara]

Merci Elsuket

Oui c'est vrai !

Je m'etais laissé embrouiller par le problème de session en négligeant le fait que dans l'assignation

Code :

   @query = @str,

le parametre @str est interprete dans l'appel

alors que dans le cas

Code :

   @query = 'select * from tbl where ID=@xid ';

le parametre @xid devrait etre interpretté dans la session sp_Send_DbMail

[olibara]

Bonsoir Elsuket

J'ai donc avancé : voici a quoi j'arrive

Cependant je pense que mes methode d'assignations de @Chauffeur et de Name dans le WHILE ne sont pas les meilleures et sans doute erroné por @Name car ca me donne 'C' dans le subject

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_NewMail1]    Script Date: 03/10/2011 20:59:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[sp_NewMail1]
AS
BEGIN
 
DECLARE @str nvarchar(max);
DECLARE @Chauffeur INT;
DECLARE @Name VARCHAR;
SET @Chauffeur = 0 
WHILE EXISTS (SELECT TOP 1 ChauffeurID FROM TotalChauffeur WHERE MailStatus=0)
   BEGIN 
     SET @Chauffeur = (SELECT TOP 1 ChauffeurID FROM TotalChauffeur WHERE MailStatus=0);
     SET @Name = 'Chauffeur Gedaan :' + (SELECT TOP 1 ChauffeurName FROM TotalChauffeur WHERE MailStatus=0);     
     SET @str='SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
     WHERE ChauffeurID=' + CAST(@Chauffeur AS varchar(10));
     EXEC msdb.dbo.sp_send_dbmail
     @recipients = 'ogb@skynet.be;carol@recutex.be;christophe@recutex.be;frederik@recutex.be',
     @query = @str ,
     @subject = @Name,
     @attach_query_result_as_file = 0 ;
     UPDATE [Clark].[dbo].[TotalChauffeur]
     SET MailStatus=1
     WHERE ChauffeurID=@Chauffeur;
    END
END

[elsuket]

Effectivement tu peux affecter tes variables dans le SELECT du WHILE :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
ALTER PROCEDURE dbo.sp_NewMail1
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @str nvarchar(max)
		, @Chauffeur int
		, @Name varchar(64);
 
	SET @Chauffeur = 0 
 
	WHILE EXISTS 
	(
		SELECT *
		FROM	dbo.TotalChauffeur 
		WHERE	MailStatus = 0
	)
	BEGIN
		SELECT	TOP 1 @Chauffeur = ChauffeurID
			, @Name = 'Chauffeur Gedaan :' + ChauffeurName
		FROM	dbo.TotalChauffeur 
		WHERE	MailStatus = 0
 
		SET @str = 'SELECT * FROM Clark.dbo.TotalChauffeur WHERE ChauffeurID = ' + CAST(@Chauffeur AS varchar(10));
 
		EXEC msdb.dbo.sp_send_dbmail
			@recipients = 'ogb@skynet.be;carol@recutex.be;christophe@recutex.be;frederik@recutex.be'
			, @query = @str
			, @subject = @Name
			, @attach_query_result_as_file = 0;
 
		UPDATE	dbo.TotalChauffeur
		SET	MailStatus = 1
		WHERE	ChauffeurID = @Chauffeur;
	END
END

EXISTS étant optimisé pour l'utilisation de étoile (comme COUNT(*), mais pas SELECT !), fais-toi plaisir

Encore une fois :

- Tu as oublié le SET NOCOUNT ON

- Supprime les crochets, il ne servent à rien puisque le nommage de tes tables et colonnes sont nommés proprement (pas d'espace ou de caractères de ponctuations et autres caractères cabalistiques)

- Ta procédure stockée étant dans la même base de données que la table que tu requêtes, tu n'as pas besoin de préfixer les tables par le nom de la base de données qui les supporte.
En revanche il est toujours bon de qualifier les objets par le nom du schéma (ici, dbo) auquel ils appartiennent

- Aère ton code !

Attention à tes types : la déclaration suivante est fausse :

Code :

DECLARE @Name VARCHAR;

Car je suppose que tes chauffeurs ont un nom de plus d'une lettre

En effet si on exécute :

Code :

1
2
DECLARE @toto varchar = 'toto'
SELECT	@toto

On obtient : t

Regarde le type de la colonne ChauffeurName, et mets le même à la variable @Name.
Si par exemple c'est varchar(32), comme tu ajoutes 'Chauffeur Gedaan :' devant, j'ai mis varchar(64).

Il est dommage de devoir lire deux fois la table au moins (une fois par le EXISTS, une fois par le SELECT TOP 1).
Tu ne peux pas envoyer un calcul pour tous les chauffeurs ?

@++

[olibara]

Merci Elsuket

Ton aide est précieuse et tres instructive

J'ai beaucoup plus d'expérience en C# C++ etc qu'en SQL
Mais tu m'aide beaucoup a avancer

Je vais me replonger la dedans ce soir

[elsuket]

J'ai beaucoup plus d'expérience en C# C++ etc qu'en SQL

Moi c'est exactement l'inverse

@++

[olibara]

Bonsoir a tous

Et bonsoir Elsuket

Voici mon code actuel (je n'oserais pas dire définitif)
- J'espere que maintenant j'ai utilisé correctement SET NOCOUNT ON
- A ce propos j'aimerais quand meme afficher le total des rows, :

• soit simplement en numerotant chaque row
• soit en ajoutant un Row contenant 'Total Bak=(Count(*) du select)

Comment puis-je faire l'un ou l'autre ?

- J'ai un peu ramé en devant ajouter la constante 'Total' dans mon query mais une vieille experience AWK et Linux m'ont aidé

Code :

1
2
3
4
  union
  SELECT 
      ChauffeurID
      ,'+'''Total'''+' AS ChauffeurName

Voici le resultat de ce périple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_ClarkMail]    Script Date: 03/11/2011 20:23:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[sp_ClarkMail]
AS
BEGIN
 
SET NOCOUNT ON
 
DECLARE @str nvarchar(max);
DECLARE @Chauffeur INT;
DECLARE @Name VARCHAR(80);
DECLARE @Result VARCHAR(MAX);
 
SET @Chauffeur = 0 
 
	WHILE EXISTS 
	(
		SELECT *
		FROM	dbo.TotalChauffeur 
		WHERE	MailStatus = 0
	)
	BEGIN
	 SELECT	TOP 1 @Chauffeur = ChauffeurID
	        	, @Name = 'Gedaan en leeg :' + ChauffeurName
		FROM	dbo.TotalChauffeur 
		WHERE	MailStatus = 0;
 
     SET @Result='SELECT 
       ChauffeurID
      ,ChauffeurName
      ,Date
      ,tarra
      ,bruto
      ,(bruto-tarra) as Neto
  FROM Clark.dbo.BakMove
  where 
  ChauffeurID='+CAST(@Chauffeur AS varchar(10))
  +' AND TotalStatus<>1
  union
  SELECT 
      ChauffeurID
      ,'+'''Total'''+' as ChauffeurName
      ,Max(Date)
      ,sum(tarra)
      ,sum(bruto)
      ,(sum(bruto)-sum(tarra)) as Neto
  FROM Clark.dbo.BakMove
  where 
  ChauffeurID='+CAST(@Chauffeur AS varchar(10))
  +' AND TotalStatus=2    
  group by ChauffeurID
  order by Date';
 
 
  --   SET @str='SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
  --   WHERE ChauffeurID=' + CAST(@Chauffeur AS varchar(10));
     EXEC msdb.dbo.sp_send_dbmail
     @recipients = 'ogb@skynet.be;carol@recutex.be;christophe@recutex.be;frederik@recutex.be',
     @query = @Result ,
     @attach_query_result_as_file = 0,    
     @subject = @Name;
--     @Body = @Result;
     UPDATE TotalChauffeur
     SET MailStatus=1
     WHERE ChauffeurID=@Chauffeur;
 
    UPDATE
    Clark.dbo.BakMove
    SET TotalStatus=1
    WHERE ChauffeurID = @Chauffeur AND TotalStatus=2;
 
    END
END

[olibara]

Envoyé par Elsuket

Il est dommage de devoir lire deux fois la table au moins (une fois par le EXISTS, une fois par le SELECT TOP 1).
Tu ne peux pas envoyer un calcul pour tous les chauffeurs ?

Malheureusement non, car le but in fine c'est d'envoyer un mail recap de l'activité du chauffeur et cela pour chaque chauffeur a chaque camion vidé !

(voir derniere mouture de mon code)

J'ai encore un Souci avec SET NOCOUNT ON
A mon avis c'est inoperant pour l'appel de sp_Send_DbMail

Par contre che cherche toujours comment faire disparaitre le (n rows affected) du resultat du Query sp_Send_DbMail

Et par ailleurs, ajouter un total formaté du genre
(5 Containers déplacés)

Je me demande donc s'il n'est pas plus simple, d'effectuer le query AVANT le sp_Send_DbMail, de l'assigner dans un VARCHAR(MAX) et mettre ce varchar dans le body du mail ?

Voici le détail d'un mail (c'est encore moins bien présenté ici qu'en vrai)

ChauffeurID ChauffeurName Date tarra bruto Neto
----------- -------------------------------------------------------------------------------- ----------------------- ----------- ----------- -----------
8 RONNY 2011-03-11 15:17:41.000 243 880 637
8 RONNY 2011-03-11 15:24:39.000 244 954 710
8 RONNY 2011-03-11 15:34:05.000 239 961 722
8 RONNY 2011-03-11 15:39:25.000 283 977 694
8 RONNY 2011-03-11 15:51:12.000 273 1012 739
8 RONNY 2011-03-11 15:51:14.000 0 0 0
8 Total 2011-03-11 15:51:14.000 1282 4784 3502

(7 rows affected)

[olibara]

J'ai donc essayé de construire le body avec le résultat d'un select casté en VARCHAR avant l'appel de sp_Send_Dbmail

Ca donne le code ci-après, mais j'ai une message d'erreur

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,

Désolé de paraitre sans doute tres stupide mais rassurez-vous, je crois que j'apprends beaucoup de chose et j'essayerai de ne pas les oublier (j'aurais pu aussi optimiser mes assignation de @Chauffeur et @Name mais ce n'est pas la premiere préocupation ici )!

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
USE [Clark]
GO
/****** Object:  StoredProcedure [dbo].[sp_NewMail2]    Script Date: 03/13/2011 17:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[sp_NewMailTst]
AS
BEGIN
 
DECLARE @str nvarchar(max);
DECLARE @Chauffeur INT;
DECLARE @Name VARCHAR(80);
DECLARE @Result VARCHAR(MAX);
 
SET @Chauffeur = 0 
WHILE EXISTS (SELECT TOP 1 ChauffeurID FROM TotalChauffeur WHERE MailStatus=0)
   BEGIN 
     SET @Chauffeur = (SELECT TOP 1 ChauffeurID FROM TotalChauffeur WHERE MailStatus=0);
     SET @Name = 'Gedaan en leeg : ' + (SELECT TOP 1 ChauffeurName FROM TotalChauffeur WHERE MailStatus=0);     
     SET @Result=CAST((SELECT 
       ChauffeurID
      ,ChauffeurName
      ,Date
      ,tarra
      ,bruto
      ,(bruto-tarra) AS Neto
  FROM Clark.dbo.BakMove
  WHERE 
  ChauffeurID=CAST(@Chauffeur AS varchar(10))
   AND TotalStatus<>1
  union
  SELECT 
      ChauffeurID
      ,'Total' AS ChauffeurName
      ,Max(Date)
      ,sum(tarra)
      ,sum(bruto)
      ,(sum(bruto)-sum(tarra)) AS Neto
  FROM Clark.dbo.BakMove
  WHERE 
  ChauffeurID=CAST(@Chauffeur AS varchar(10))
  AND TotalStatus<>1    
  GROUP BY ChauffeurID
  ORDER BY Date) AS VARCHAR(MAX)) ;
 
 
  --   SET @str='SELECT *  FROM [Clark].[dbo].[TotalChauffeur] 
  --   WHERE ChauffeurID=' + CAST(@Chauffeur AS varchar(10));
     EXEC msdb.dbo.sp_send_dbmail
     @recipients = 'ogb@skynet.be;carol@recutex.be;christophe@recutex.be;frederik@recutex.be',
--     @query = @Result ,
     @attach_query_result_as_file = 0,    
     @subject = @Name,
     @Body = @Result;
     UPDATE [Clark].[dbo].[TotalChauffeur]
     SET MailStatus=1
     WHERE ChauffeurID=@Chauffeur;
 
    UPDATE
    Clark.dbo.BakMove
    SET TotalStatus=1
    WHERE ChauffeurID = @Chauffeur;
 
    END
END

[elsuket]

Est-ce que ton idée est de montrer le résultat de la requête en HTML dans le mail ?
Est-ce qu'il ne serait pas mieux de faire un rapport SSRS pour cela ?

@++

[olibara]

Merci Elsuket

Est-ce que ton idée est de montrer le résultat de la requête en HTML dans le mail ?
Est-ce qu'il ne serait pas mieux de faire un rapport SSRS pour cela ?

J'y ai pensé mais avant tout j'aimerais maitriser ce que je peux faire avec des query "simples" : pense tu que je suis sur la bonne voie ?

C'est quoi un rapport SSRS ?
Je dois aussi essayer de ne pas prendre trop de ressource pour ce rapport afin de minimiser le risque de perte de connexion entre l'appel de la requete et le retour d'execution (cette gestion d'exception est asser lourde)

[olibara]

Bonjour

Ce qui me bloque le plus actuellement c'est comment ajouter le total des Rows utile dans le select que je construit pour sp_Send_Dbmail

A l'issue du Query ci-apres
Apres le resultat je trouve

(6 rows affected)

Je m'en contenterais, sauf que ce n'est pas 6 mais 5 (le row de l'union ne dois pas etre compté)
Je pourrais aussi truquer en l'ajoutant dand le libellé 'Total' mais là je ne sais pas comment faire ?

Quelqu'un a une idée pour afficher d'une manière ou d'une autre ce total de row ?

Merci de votre aide

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
    SET @Result='SELECT 
       ChauffeurID
      ,ChauffeurName
      ,Date
      ,tarra
      ,bruto
      ,(bruto-tarra) as Netto
  FROM Clark.dbo.BakMove
  where 
  ChauffeurID='+CAST(@Chauffeur AS varchar(10))
  +' AND TotalStatus<>1
  AND Bruto<>0
  union
  SELECT 
      ChauffeurID
      ,'+'''Total'''+' as ChauffeurName
      ,Max(Date)
      ,sum(tarra)
      ,sum(bruto)
      ,(sum(bruto)-sum(tarra)) as Netto
  FROM Clark.dbo.BakMove
  where 
  ChauffeurID='+CAST(@Chauffeur AS varchar(10))
  +' AND TotalStatus<>1    
  group by ChauffeurID
  order by Date';

[elsuket]

J'y ai pensé mais avant tout j'aimerais maitriser ce que je peux faire avec des query "simples" : pense tu que je suis sur la bonne voie ?

Oui

C'est quoi un rapport SSRS ?

SQL Server Reporting Services, qui est une fonctionnalité de SQL Server qui te permet de créer des rapports à partir de requêtes, et de les publier très simplement sur un site web interne.
L'utilisateur du rapport a la possibilité de passer des valeurs aux paramètres de celui-ci, sans connaître quoi que ce soit au développement.
On peut aussi souscrire des utilisateurs et leur envoyer le rapport par e-mail, ou le lien qui pointe vers celui-ci, éventuellement pré-paramétré.
A voir en évolution de ce que tu fais actuellement.

Je dois aussi essayer de ne pas prendre trop de ressource pour ce rapport afin de minimiser le risque de perte de connexion entre l'appel de la requete et le retour d'execution (cette gestion d'exception est asser lourde)

Dans ce cas la procédure stockée est une bonne solution, puisqu'on ne transmet pas le texte de la requête.

Je m'en contenterais, sauf que ce n'est pas 6 mais 5 (le row de l'union ne dois pas etre compté)
Je pourrais aussi truquer en l'ajoutant dand le libellé 'Total' mais là je ne sais pas comment faire ?

Quelqu'un a une idée pour afficher d'une manière ou d'une autre ce total de row ?

D'abord comme je te l'avais dit, met SET NOCOUNT à ON dans la chaîne de requête, comme dans la procédure.
Ensuite tu peux écrire :

Code :

1
2
3
DECLARE @nb_rows int
-- Ton SELECT
SELECT @nb_rows = @@ROWCOUNT - 1

@++

[olibara]

Merci Elsuket

Mais je cependant que l'idée que javais exposé qq post précédent n'est pas faisable mais je n'ai pas très bien compris pourquoi !

J'ai donc essayé de construire le body avec le résultat d'un select casté en VARCHAR avant l'appel de sp_Send_Dbmail

Ca donne le code ci-après, mais j'ai une message d'erreur