Discussion :

[Delphi-ne]

Bonjour,

J'ai besoin de pouvoir transférer régulièrement certaines procédures stockées d'une base SQL Serveur vers une autre.
Je pense effectuer ce traitement dans les travaux planifiés (de nuit)

Pour transférer une procédure stockée j'ai écrit le code suivant qui reçoit en paramètre le nom de la procédure à copier. Il fonctionne sur la base Cible.
:

Code :

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

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
BEGIN
Declare @LC_Texte     VarChar(max)
Declare @LC_Texte2    VarChar(8000)
Declare @LN_Procedure Int
 
-- récupérer le texte de la procédure stockée
	SET @LC_Texte = ''
	SELECT @LC_Texte = @LC_Texte + COALESCE(Source.dbo.syscomments.Text, '') 
	 FROM Source.dbo.syscomments, Source.dbo.sysobjects
	WHERE Source.dbo.syscomments.id = sysobjects.id
	AND Source.dbo.sysobjects.xtype = 'P'
	AND Source.dbo.sysobjects.name = @PC_Procedure
 
-- tester et détruire
    Set @LN_Procedure = (SELECT Count(*) 
                           FROM sys.objects 
                          Where Name = @PC_Procedure)
	If @LN_Procedure > 0 Begin
	   Set @LC_Texte2 = 'DROP PROCEDURE ' + @PC_Procedure
	   --Execute (@LC_Texte2)
    End
 
-- créer la nouvelle version
Execute (@LC_Texte)
 
END

Ca fonctionne parfaitement.
Où les choses se gâtent c'est lorsque je veux enfermer cela dans un job
Je voudrais sélectionner les n bases Cibles et appeler pour chacune la procédure stockée

J'ai ce code dans le Maintenance Plans

Code :

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

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
Declare @LC_Commande VarChar(50)
Declare @LC_Base     VarChar(50)
declare @return_value int
 
 
DECLARE TmpCopy_cursor CURSOR FOR
Select BaseSQL
From Source.dbo.Table
Where BaseSQL is not Null And BaseSQL <> ''
 
Open TmpCopy_cursor
fetch next from TmpCopy_cursor
 
WHILE @@FETCH_STATUS = 0
BEGIN
	fetch next from TmpCopy_cursor
    Set @LC_Commande =   'USE [' + @LC_Base + '] '
    Execute(@LC_Commande)
    Execute CopyProcStock('Procedure1')
    Execute CopyProcStock('Procedure2')
etc...
  End
 
CLOSE TmpCopy_cursor
DEALLOCATE TmpCopy_cursor

Là j'ai un message d'erreur que je comprends pas et qui m'énerve.

Code :

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

1
2
Msg 166, Level 15, State 1, Line 1
'DROP PROCEDURE' does not allow specifying the database name as a prefix to the object name.

Je suppose que de faire un Insert Select des tables Source.dbo.syscomments, Source.dbo.sysobjects serait dangereux et je n'ose pas trop m'y risquer.

[Delphi-ne]

Si ma demande n'est pas suffisamment claire n'hésitez pas.
J'ai vraiment besoin de pouvoir parvenir à cette solution et quelque soit la façon dont je retourne les deux procédures cela bloque toujours
Merci d'avance de votre aide.

[Ptit_Dje]

Salut,

Pourrais tu poster le résultat d'un débug de ta requete ? (utiliser print plutot que exec)

[Delphi-ne]

Merci mais je ne sais pas activer de débogueur

[Ptit_Dje]

Pourrais tu executer ta procedure de migration en remplacant la ligne qui execute la commande de creation par une ligne qui affiche la commande de création et nous faire parvenir cette ligne de commande ?

Le probleme ici est, je pense, que tu essayes de créer une procédure de la facon suivante : create procedure DBName.Schema.Procedure ...
Create procedure n'accepte pas le DBName.
Il te faudra donc trouver un systeme pour generer ton code sans celui ci.

[Delphi-ne]

Merci
Je vous poste cela Lundi matin

[elsuket]

Bonjour Delphi-ne,

Effectivement on ne peut pas porter une procédure stockée "simplement" d'une base de données vers une autre (on ne peut pas non plus écrire CREATE PROCEDURE maBD.monSchema.maProc).

Le mieux serait donc d'écrire une petite procédure stockée pour exporter le code de vos procédures stockées à mettre à jour dans un fichier .sql avec l'utilitaire bcp, puis réaliser la mise à jour de vos procédures stockées en passant en paramètre ce script à l'utilitaire sqlcmd (un autre utilitaire en ligne de commande) avec l'option -i <input file>

Exemple d'utilisation de BCP :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT SQL.definition
INTO ##TOTO
FROM maBD.sys.procedures PROCS
JOIN maBD.sys.sql_modules SQL ON SQL.object_id = PROCS.object_id
WHERE PROCS.name in ('mProc0', 'maProc1', ..., 'maProcN');
 
DECLARE @szBCP VARCHAR(256);
SET @szBCP = 'bcp "SELECT * FROM ##TOTO" queryout C:\mesProcs.sql -T -c';
EXEC xp_cmdshell @szBCP;
 
DROP TABLE ##TOTO;

[Delphi-ne]

Bonjour elsuket et Ptit_Dje

La procédure stockée qui effectuer la copie marche bien
J'en avais donné le code, le revoici

Code :

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

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
BEGIN
Declare @LC_Texte     VarChar(max)
Declare @LC_Texte2    VarChar(8000)
Declare @LN_Procedure Int
 
-- récupérer le texte de la procédure stockée
	SET @LC_Texte = ''
	SELECT @LC_Texte = @LC_Texte + COALESCE(Source.dbo.syscomments.Text, '') 
	 FROM Source.dbo.syscomments, Source.dbo.sysobjects
	WHERE Source.dbo.syscomments.id = sysobjects.id
	AND Source.dbo.sysobjects.xtype = 'P'
	AND Source.dbo.sysobjects.name = @PC_Procedure
 
-- tester et détruire
    SET @LN_Procedure = (SELECT Count(*) 
                           FROM sys.objects 
                          WHERE Name = @PC_Procedure)
	IF @LN_Procedure > 0 Begin
	   SET @LC_Texte2 = 'DROP PROCEDURE ' + @PC_Procedure
	   --Execute (@LC_Texte2)
    End
 
-- créer la nouvelle version
Execute (@LC_Texte)
 
END

Il s'exécute sur la base cible. Donc il n'y a pas besoin de spécifier le dbName.
C'est d'illeurs pour cela que je l'ai mis dans la base cible.
Voici le début du script généré

Code :

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

1
2
CREATE PROCEDURE [dbo].[XXXX_Import] 
  @User                   varchar( 50 ) = '',

L'erreur survient si j'essaie d'appeler cette procédure stockée depuis un script.
Ce que je voudrais c'est par script lister les différentes base à actualiser et, pour chacune jouer la procédure stockée de copie.
Ca ca plante