création d'une stored proc sp_copy_schema

Version imprimable

Bonsoir

Lorsque je tente de créer la stored proc sp_copy_schema
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_copy_schema] @Srvsource varchar (30), @Dbsource varchar(30), @Schsource varchar(30), @DbDest varchar(30), @SchDest varchar (30) AS declare @sql varchar(max) SELECT @sql = coalesce(@sql, ';') + ' SELECT * INTO [@DbDest].[@Schdest].[' + TABLE_NAME + '] FROM [@Srvsource] + ''.'' + [@Dbsource] + ''.'' + [@Schsource].[' + TABLE_NAME + ']' FROM [@Srvsource].[@Dbsource].INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @Schsource print(@sql) exec (@sql)
J'ai le message d'erreur suivant :
Could not find server '@Srvsource' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Je suis à la recherche d'une solution.

Merci

Bibi

17/03/2010, 10h57
SQLpro

Il faut que votre serveur lié ait été préalablement créé via sp_addlinkedserver et sp_addlinkedsrvlogin.

A +

erreur de syntaxe suite à l'ajout de addlinkedsrvlogin

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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[sp_copy_schema]
@Srvsource varchar (30), 
@Dbsource varchar(30), 
@Schsource varchar(30), 
@DbDest varchar(30),
@SchDest varchar (30)
AS
 
 
declare @sql varchar(max) 
 
EXEC master.dbo.sp_addlinkedserver @server = @Srvsource, @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@Srvsource,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'collation compatible', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'data access', @optvalue=N'true'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'dist', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'pub', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'rpc', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'rpc out', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'sub', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'connect timeout', @optvalue=N'0'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'collation name', @optvalue=null
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'lazy schema validation', @optvalue=N'false'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'query timeout', @optvalue=N'0'
 
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'use remote collation', @optvalue=N'true'
 
SELECT @sql = coalesce(@sql, ';') + ' SELECT * INTO @DbDest.@Schdest.TABLE_NAME
FROM @Srvsource.@Dbsource.@Schsource.[ '+ TABLE_NAME + ']' 
FROM @Dbsource + '.' + [INFORMATION_SCHEMA.TABLES]
WHERE TABLE_SCHEMA = @Schsource
print(@sql)

merci

Bibi

Bonjour,

Je doute que votre dernière requête fonctionne car vous avez laissé le nom des variables dans la chaîne de requête SQL.
Il vous faudrait donc écrire :
Code:

1 2 3 4 5 6 7 8 9 DECLARE @sql varchar(max) SELECT @sql = CASE WHEN @sql IS NULL THEN '' ELSE @sql + 'SELECT * INTO ' + @DbDest + '.' + @Schdest + '.' + TABLE_NAME + ' FROM ' + @DbSource + '.' + @SchSource + '.' + TABLE_NAME + FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @Schsource PRINT @sql
Si vous utilisez COALESCE() ou ISNULL(), la chaîne SQL commencera par un point-virgule ;)

@++ ;)

18/03/2010, 12h56
bibi92

stored proc [sp_copy_schema]

Bonjour,

Merci, je vais essayer cela.

Bonne journée

Bibi