Discussion :

[gabouille]

Bonjour a tous,

je sollicite une fois encore votre aide...

je souhaite mettre un script en procédure stocké ( script seul ne retourne pas d'erreur), avec en paramètre le nom d'un server/instance (le nom peut varier suivant qui on veut interroger)

Code :

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

1
2
3
4
5
6
7
8
CREATE PROCEDURE ma_procédure
(
@Nomdemonserver sysname
)
 
AS
 
Monscript..

il est retrouvé dans le script sous 2 formes:

1. select * from [@Nomdemonserver].table
2. exec @sqlcommand at "@Nomdemonserver"

Autant pour mettre les dates dans la déclaration de la procédure stocké il n'y pas de problème, mais la je suis un bloque:

il me dit a juste raison que '@Nomdemonserver' n'est pas retrouvé dans sysservers et de le créer avec addlinkedserver ( erreur 7202)

si vous avez des suggestion quant a la déclaration de mon paramètre server je suis preneur!

vous remerciant!

Gabouille

[DotNetMatt]

Il faut utiliser du Dynamic T-SQL, pour construire la requête dynamiquement... En complément : http://support.microsoft.com/kb/314520

[gabouille]

Merci pour ces informations mais après lecture et re-lecture j'ai du mal a saisir le fonctionnement du dynamic SQL dans la création de la procédure stocké..

Je précise que ma requête fais près de 300 lignes...

[DotNetMatt]

Le Dynamic SQL ça consiste à construire la requête dynamiquement, un peu comme si tu découpais ta requête en plusieurs string, et que tu les concatènes à la fin.

Que ta requête fasse 3 ou 3000 lignes, ce que tu souhaites faire ne peut être réalisé que par cette méthode (du moins à ma connaissance), donc disons tu n'as pas vraiment le choix

Voici un exemple extrêmement basique de Dynamic SQL :

Code SQL :

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
-- Déclaration des variables
DECLARE @EmployeeID AS SMALLINT
DECLARE @Query AS NVARCHAR(300)
DECLARE @DBName AS NVARCHAR(20)
 
-- Attribution des valeurs
SET @EmployeeID = 521
SET @DBName = N'YourDatabase'
SET @Query = 'SELECT LastName, FirstName
    FROM ' + @DBName + '.HumanResources.Employee
    WHERE Id = ' + CAST(@EmployeeId AS NVARCHAR(6)) + ''
 
-- Exécute la requête T-SQL
EXECUTE(@Query)
 
-- Requête générée (visible avec PRINT @Query) :
-- SELECT LastName, FirstName
-- FROM YourDatabase.HumanResources.Employee
-- WHERE Id = 521

[gabouille]

Ok,

je l'utilisais sans vraiment savoir son nom ..
Je sens que je vais m'amuser a tout revoir: cela va faire de la requête dynamique dans de la requête dynamique...

Merci pour tout

Bonne journée

[gabouille]

Bon j'ai fait un peu comme vous me l'avez conseillez mais une simple commande ne fonctionne pas... je commence a perdre mes cheveux ^^

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @server nvarchar (100)
DECLARE @commande nvarchar (256)
 
 
SET @server = 'SERVER\INSTANCE'
 
SET @commande = 'SELECT dsn FROM' + @server +'.nomDb.nomTable'
 
print @server
print @commande
 
exec @commande

Commentaires :

- Les print me lancent une requete qui fonctionne correctement si je l’exécute a part
- l’exécute me dit que la commande ne fonctionne pas (erreur 7202)

nb: l'instruction interroge obligatoirement un linked server

j'ai aussi un probleme si je met :
SET @commande = @instruction AT @server

Merci d'avance pour les conseils/correction


existe t il une commande sous sql server 2005 dans le genre de PREPARE ?

[aieeeuuuuu]

SET @commande = 'SELECT dsn FROM' + @server +'.nomDb.nomTable'

Il manque une espace après le FROM

--pensez aux balises [code]

[DotNetMatt]

En complément du retour d'aieeeuuuuu, as-tu enregistré le serveur que tu veux utiliser en tant que LinkedServer ?

Cela se fait via addlinkedserver au préalable. Tu peux l'avoir utilisée soit en "brut" (en SQL directement), soit via l'interface graphique.

Si tu ne l'as pas fait, aucune chance que ça marche...

Et pour en remettre une couche :

[gabouille]

héhé

bon l'espace est une maladresse de ma part en ayant recopié sur developpez.
donc cela ne peux etre ca.

et pour le addlinkedserver:

SET @server = 'monserver'


@server n'est pas enregistré comme un linked server mais 'monserver' est bien un linked server c'est a dire que les commandes sans mettre le server en paramètre fonctionnent.

le problème que j'ai c'est que il n'accepte pas le passage en paramètre du server.
concernant le passage en paramétrage, en ayant cherché sur internet, deux 'solution' aurait pu s'offrir a moi:
:setvar monserver -s
commande PREPARE.

Cependant je n'arrive pas à les mettre en place.

Merci pour votre volontariat et vos aides précieuses.

[aieeeuuuuu]

Code :

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

1
2
 
'SELECT dsn FROM' + @server +'.nomDb.nomTable'

Il semble qu'il manque aussi le nom du schéma

Code :

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

1
2
 
'SELECT dsn FROM' + @server +'.nomDb.nomSchema.nomTable'

[DotNetMatt]

As-tu vu ce lien, où Microsoft utilise OPENQUERY ?

How to pass a variable to a linked server query (KB314520)

[EDIT] : Grillé par aieeeuuuuu vérifie sa solution avant de tenter celle qui utilise OPENQUERY, histoire de ne pas complexifier inutilement ton code.

[aieeeuuuuu]

[EDIT] : Grillé par aieeeuuuuu vérifie sa solution avant de tenter celle qui utilise OPENQUERY, histoire de ne pas complexifier inutilement ton code.

Cependant, vu qu'il dit que quand il copie/colle la requete, ça fonctionne, je penche pour une deuxième erreur de copier/coller dans son post.

Dans tous les cas, avoir le message d'erreur nous permettrait d'y voir plus clair !

[gabouille]

Je crois avoir une idée de mon erreur, qui ne viendrais finalement pas de l'utilisation du dynamique SQL avec le server en paramètre.
Par un commande simple en suivant les indications que vous m'avez donnée cela marche.

Le problème du post est résolu.

par un vision de mon code cela aurait été effectivement plus simple pour vu mais comme je code sur un machine en réseaux industriel sans connections internet le Copier/Coller est tendu (bon je sais les clé usb ca existe)^^

Donc il s'agirait d'un problème de syntax avec les ' comme je fait du dynamique sql imbriqué les un dans les autres avec curseurs ça fait un beau boxon..

Merci encore pour votre aide et le temps accordé a résoudre ce problème.

Bonne journée


ps: la limite de caractère est bien 4000?

comment faire si la définition d'une requete @requete comporte plus de 4000 caractère?

[DotNetMatt]

Donc il s'agirait d'un problème de syntax avec les ' comme je fait du dynamique sql imbriqué les un dans les autres avec curseurs ça fait un beau boxon..

Ah oui effectivement il fallait tenir compte du nombre de '

comment faire si la définition d'une requete @requete comporte plus de 4000 caractère?

Tu coupes ta requête en plusieurs (N)VARCHAR, et tu concatènes

[EDIT] : quand je dis "et tu concatènes", ça veut dire que quand tu exécutes, tu fais quelque chose comme ça :

Code SQL :

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

EXEC (@variable1 + @variable2 + @variable3 + @variable4)

[gabouille]

Bon comme je disait juste avant le paramètre @server fonctionne,
mais comme d'hab quand un problème est résolu un autre arrive....
pour etre plus simple je vais mettre mon code :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
 
DECLARE moncurser CURSOR
	For select DSN From [server\INSTANCE3].CC_OS_09_06_04_16_15_49R.dbo.AMT
		where DBStop <= convert(datetime,'2012-02-03',120) 
			and DBStart >= convert(datetime,'2012-01-25',120)
			and dsn like '%ALG%'
 
Open moncurser
 
Fetch next from moncurser into @DSN
 
 
	begin
 
	set @sqlcommandbackup = 
	'backup database [' + @DSN + '] 
	TO DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\'' + @DSN + '.bak'' 
	WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10'
 
	set @sqlcommandrestore = 
	'restore database [' + @DSN + '] 
	FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\' + @DSN + '.bak' 
	WITH 
	MOVE N''WinCC_data1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_data1.mdf'',  
	MOVE N''WinCC_log1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_log1.ldf'''
 
	set @sqlcommandRenomme = 
	'USE MASTER; ALTER DATABASE ' + @DSN + ' MODIFY NAME = "DATA.RECEPTION";'
 
 
print @sqlcommandbackup
print @sqlcommandrestore
print @sqlcommandRenomme
 
	exec (@sqlcommandbackup) AT "server\INSTANCE3";
	exec (@sqlcommandrestore);
	exec (@sqlcommandRenomme);
 
	end
 
close moncurser
 
DEALLOCATE moncurser

Donc voila une partie de mon code qui représente bien al structure du script
Rappel Obj: passer en procédure stocké avec 4 paramètres:
Date début
Date fin
Server\instance
DATA.RECEPTION.

je n'ai eu des problème que pour le server.

je voulais faire un test simplifié du passage en paramètre du server du code ci dessus:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
 
DECLARE @p1 nvarchar (4000)
DECLARE @p2 nvarchar (1000)
DECLARE @SERVER (100)
 
SET @SERVER= 'SERVER\INSTANCE3'
 
SET @p1 = '
DECLARE moncurser CURSOR
	For select DSN From ['+ @SERVER +'].CC_OS_09_06_04_16_15_49R.dbo.AMT
		where DBStop <= convert(datetime,''2012-02-03'',120) 
			and DBStart >= convert(datetime,''2012-01-25'',120)
			and dsn like ''%ALG%''
 
Open moncurser
 
Fetch next from moncurser into @DSN
 
 
	begin
 
	set @sqlcommandbackup = 
	''backup database ['' + @DSN + ''] 
	TO DISK = N''' +  'C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\''' + @DSN + ''.bak'' 
	WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD,  STATS = 10''
 
	set @sqlcommandrestore = 
	''restore database ['' + @DSN + ''] 
	FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\''' + @DSN + ''.bak'' 
	WITH 
	MOVE N''WinCC_data1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_data1.mdf'',  
	MOVE N''WinCC_log1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_log1.ldf''''
 
	set @sqlcommandRenomme = 
	''USE MASTER; ALTER DATABASE '' + @DSN + ''MODIFY NAME = "DATA.RECEPTION";''
 
	exec (@sqlcommandbackup) AT "'+@SERVER+'";
	exec (@sqlcommandrestore);
	exec (@sqlcommandRenomme);
 
	end
 
close moncurser
 
DEALLOCATE moncurser'

et la c'est le drame,
il ne l'accepte pas. . .

[DotNetMatt]

C'est quoi le message d'erreur ?

[gabouille]

mess 203

'le résumé de ma commande' is not a valid identifier

[DotNetMatt]

Ca ressemble à un problème de guillemets... J'ai fait quelques corrections :

Code SQL :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE @p1 nvarchar(4000)
DECLARE @p2 nvarchar(1000)
DECLARE @SERVER nvarchar(100)
 
SET @SERVER= 'SERVER\INSTANCE3'
 
SET @p1 = '
DECLARE moncurser CURSOR
For select DSN From ['+ @SERVER +'].CC_OS_09_06_04_16_15_49R.dbo.AMT
where DBStop <= convert(datetime,''2012-02-03'',120) 
and DBStart >= convert(datetime,''2012-01-25'',120)
and dsn like ''%ALG%''
 
Open moncurser
 
Fetch next from moncurser into @DSN
 
begin
 
set @sqlcommandbackup = 
''backup database ['' + @DSN + ''] 
TO DISK = N''' + 'C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\'' + @DSN + ''.bak'' 
WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10''
 
set @sqlcommandrestore = 
''restore database ['' + @DSN + ''] 
FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\'' + @DSN + ''.bak'' 
WITH 
MOVE N''WinCC_data1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_data1.mdf'', 
MOVE N''WinCC_log1'' TO N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\DATA\DATA.RECEPTION_log1.ldf''''
 
set @sqlcommandRenomme = 
''USE MASTER; ALTER DATABASE '' + @DSN + ''MODIFY NAME = "DATA.RECEPTION";''
 
exec (@sqlcommandbackup) AT "' + @SERVER + '";
exec (@sqlcommandrestore);
exec (@sqlcommandRenomme);
 
end
 
close moncurser
 
DEALLOCATE moncurser'

N'hésite pas à faire un PRINT @p1 pour vérifier la validité de ta syntaxe

[aieeeuuuuu]

où déclarez vous vos variables (@DSN, ...) ???

[gabouille]

Toutes mes variables sont déclaré d'un bloc au tout début.
je vais tester le code modifié et je vous tiens au courant.
merci

EDIT:
Pour la déclaration, effectivement il doit y avoir une subtilité, un message d'erreur est apparu pour les paramètre @dsn @

Code :

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

1
2
3
@sqlcommandbackup
@sqlcommandrestore
@sqlcommandRenomme

donc au lieu de tout mettre en haut j'ai mis ceux ci dans le SET

maintenant je vais travailler sur deux erreurs :
102 ---> probleme syntax '\'
132 ---> The label 'C' has already been declared.

Edit 2: résolution de l'erreur 132:

Code :

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

1
2
3
4
SET @sqlcommandrestore = 
''restore DATABASE ['' + @DSN + ''] 
FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL
\Backup\'' + @DSN + ''.bak''

Devient

Code :

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

1
2
3
SET @sqlcommandrestore = 
''restore DATABASE ['' + @DSN + ''] 
FROM DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\''' + @DSN + '''.bak''

j'ai juste rajouté deux simple cote sur la ligne juste au dessus.

EDIT : EUREKA dis t on!!
il ne faut pas avoir peur de mettre jusqu’à 8 ' a la suite pour les modéliser .
ce bout de requête fonctionne, je vais continuer mon chemin tranquilou

Merci a vous deux en tout cas !