Tag Archives: Procédure stockée

Abonnements pilotés par les données (ou Data Driven Subscriptions)

Bonjour

Voici un article sur les Abonnements pilotés par les données (ou Data Driven Subscriptions) dans la suite SQL Server 2008.

Utilisation du Report Server

Les abonnements pilotés par les données permettent le passage de paramètre dans la planification de diffusion de rapport Reporting Services.

Une fois que vous avez réalisé votre rapport sous Reporting Services, vous pouvez le déployer sur le portail Report Server. Le rapport est alors accessible sur votre navigateur à l’url de votre portail. Si le rapport est paramétré, lors de son exécution, vous pourrez choisir ou taper une valeur correspondant à votre paramètre. Le rapport s’affiche en tenant compte de votre choix. C’est l’utilisation classique du Report Server.

Les abonnements

Vous pouvez utiliser le Report Server d’une manière plus avancée pour répondre à la demande de votre client ou votre employeur : « Je voudrais recevoir automatiquement le rapport X par mail quotidiennement »

Sur le portail, lorsque vous exécutez un rapport, vous avez 4 onglets :

  • Affichage
  • Propriétés
  • Historique
  • Abonnements

C’est l’onglet Abonnements qui va nous intéresser. Il contient la liste des abonnements qui sont définit sur un rapport et offre la possibilité d’en ajouter.

Par défaut, en cliquant sur Nouvel abonnement, vous devriez pouvoir planifier une remise des rapports par Partage Windows. Pour planifier une remise de rapports par mail, vous devez configurer les paramètres d’envoi de mail dans le Gestionnaire de configuration de Reporting Services.

Attention, vous ne pourrez pas utiliser un serveur SMTP nécessitant l’authentification nativement. (voir l’article Envoi de mail avec un serveur SMTP non local)

Les abonnements pilotés par les données

Dans la version enterprise de SQL Server 2008 (et visiblement 2005), lors de la conception de votre abonnement, vous avez la possibilité de définir la valeur d’un parametre à partir d’une requête SQL. Ainsi vous pourrez automatiser l’envoi d’un rapport à une adresse email retournée par la requete.

Dans la version standard, la fonction n’est pas prévue :

Les abonnements pilotés par les données ne sont pas pris en charge dans l’ édition Standard ni dans SQL Server Express Edition with Advanced Services.” FAQ Microsoft (http://msdn.microsoft.com/fr-fr/library/ms159150.aspx)

Si vous ne pouvez pas migrer, il faut trouver un solution de contournement.

La solution consiste à modifier les tables internes de ReportServer pour ajouter manuellement les valeurs des paramètres.
La procédure stockée qui permet de le faire a été diffusée par Jason Selburg sur www.codeproject.com puis améliorée par Greg Low sur le sqlblog.com.

Pour utiliser la procédure, il faut :

– Sur le Report Server, compléter les champs dans l’abonnement comme ci-dessous.

To Address: |TO|
Carbon Copy Address: |CC|
Blind Copy Address: |BC|
Reply To Address: |RT|
Comment / Body: |BD|

Source : http://www.codeproject.com/KB/database/DataDrivenSubscriptions.aspx

– Avant de valider l’abonnement, ouvrir un SQL Server Management Studio >> Agent SQL Server >> Travaux

– Valider l’abonnement sur le Report Server (Cette manipulation permet d’identifier l’abonnement créé)

– Localiser l’identifiant de l’abonnement (code alphanumérique peu explicite) (c’est celui qui apparait dans Travaux)

– Ensuite, il faut placer la procédure stockée suivante dans la liste des procédures stockées de la base ReportServer.

Source : http://sqlblog.com/blogs/greg_low/archive/2008/08/13/data-driven-subscriptions-in-sql-server-2005-standard-edition.aspx

– Enfin, dans une nouvelle requête, compléter les paramètres pour un premier test :

EXEC dbo.ExecuteSubscribedReport

@ScheduleID = ‘4CE38C83-6A03-4780-895A-92FD6F8FD5B0’, (identifiant de l’abonnement récupéré dans Travaux)

@EmailTo = ‘to@mail.com‘, (mettez votre mail pour le recevoir)

@EmailCC = ‘cc@mail.com‘,

@EmailBCC = ‘bcc@mail.com‘,

@EmailReplyTo = ‘rt@mail.com‘,

@EmailBody = ‘Salut to’,

@ParameterList = ‘ ‘;

Le plus difficile est fait. Vous pouvez intégrer cet appel dans une procédure stockée pour automatiser un mailing pour vos clients :

Boucle avec un curseur
– On récupere les adresses email et le code du client avec un select
– On execute la procédure ExecuteSubscribedReport avec l’email pour destinataire et le code du client pour la ParameterList.

N’hésitez pas à m’indiquer en commentaire si certaines parties ne sont pas assez détaillées ou illustrées 😉

A bientôt,

Guillaume

[Procédure Stockée] Cursor / Curseur

Bonjour,

Lors du développement d’une procédure stockée, il est d’usage d’éviter l’utilisation des curseurs. Cependant ce n’est pas toujours possible. Il convient alors d’apprivoiser certaines subtilités.

Pour rappel, les curseurs permettent un parcourt ligne à ligne renvoyée par une requête de sélection (par exemple). Vous trouverez plus d’information sur leur usage sur Les procédures et les curseurs avec MS-SQL Server.

Après avoir chargé le curseur avec :

DECLARE curseur_1 CURSOR FOR (RQT)

puis avoir ouvert le curseur avec :

OPEN curseur_1

puis avoir récupéré le premier champ de la premiere ligne du curseur avec :

FETCH curseur_1 into @champ1

Un curseur se parcourt avec une boucle :

WHILE @@FETCH_STATUS = 0

BEGIN

Instructions

END

@@Fetch_Status retourne l’état de la dernière instruction FETCH effectuée sur un curseur actuellement ouvert par la connexion.

J’attire votre attention dans cet article sur l’usage de 2 curseurs imbriqués. En effet, la documentation Microsoft précise :

Supposons, par exemple, qu’un utilisateur exécute une instruction FETCH sur un curseur, puis appelle une procédure stockée qui ouvre et traite les résultats pour un autre curseur. Lorsque le contrôle revient de la procédure stockée, @@FETCH_STATUS prend en compte la dernière instruction FETCH exécutée dans la procédure stockée et non celle qui avait eu lieu avant l’appel de la procédure stockée. (http://msdn.microsoft.com/fr-fr/library/ms187308.aspx)

Il est donc impossible d’imbriquer 2 curseurs. La solution est pour y remédier est d’attaquer la table sys.dm_exec_cursors qui contient l’état des curseurs actifs distinctement.

Il faut donc remplacer le :

while fetch_status =0
par :
while (SELECT fetch_status FROM sys.dm_exec_cursors(@@spid) where name = ‘curseur_1’ ) =0

Dans le cas d’une utilisation importante de procédures stockées potentiellement imbriquées, cette solution garantie les résultats.

Si vous avez la possibilité de ne pas utiliser de curseur, c’est encore mieux 😉

Bonne soirée,

Guillaume