bibi92
asked on
script for insert sequences
Hello,
I try to do a script for transfer the sequences from a db to another db. Ihave these errors :
Msg 102, Level 15, State 1, Procedure vcf_transfer_sequences, Line 50
Incorrect syntax near '+ @Dbsource + '.
Msg 137, Level 15, State 2, Procedure vcf_transfer_sequences, Line 56
Must declare the scalar variable "@Schsource".
Do you have a solution ?
CREATE PROCEDURE [dbo].[move_sequences]
@Srvsource varchar (30),
@Dbsource varchar(30),
@DbDest varchar(30),
@SchDest varchar(30),
@Pwd varchar (30)
AS
EXEC master.dbo.sp_addlinkedser ver @server = @Srvsource, @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
declare @sql1 nvarchar(max);
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrv login @rmtsrvname =N''' + @Srvsource + ''', @useself=N''False'',@local login=NULL ,@rmtuser= ''sa'',@rm tpassword= ''' +@Pwd + ''' '
exec sp_executesql @sql1
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'true'
EXEC master.dbo.sp_serveroption @server=@Srvsource, @optname=N'rpc out', @optvalue=N'true'
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'
declare @sql varchar(max)
set @sql = 'INSERT INTO ['+@DbDest+ '].'+ [dbo].[SEQUENCE]
([name]
,[usr]
,[value]
)
select [name], [usr], [value] FROM ['+@Srvsource + '].'+ @Dbsource + '.'[dbo].[SS_SEQUENCE] where [usr] = '@Schsource''
execute (@sql)
print (@sql)
UPDATE [dbo].[SEQUENCE]
SET [usr] = @Schdest
where [usr] = @Schsource
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = @Srvsource)
EXEC master.dbo.sp_dropserver @server=@Srvsource, @droplogins='droplogins'
Thanks
bibi
I try to do a script for transfer the sequences from a db to another db. Ihave these errors :
Msg 102, Level 15, State 1, Procedure vcf_transfer_sequences, Line 50
Incorrect syntax near '+ @Dbsource + '.
Msg 137, Level 15, State 2, Procedure vcf_transfer_sequences, Line 56
Must declare the scalar variable "@Schsource".
Do you have a solution ?
CREATE PROCEDURE [dbo].[move_sequences]
@Srvsource varchar (30),
@Dbsource varchar(30),
@DbDest varchar(30),
@SchDest varchar(30),
@Pwd varchar (30)
AS
EXEC master.dbo.sp_addlinkedser
/* For security reasons the linked server remote logins password is changed with ######## */
declare @sql1 nvarchar(max);
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrv
exec sp_executesql @sql1
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
declare @sql varchar(max)
set @sql = 'INSERT INTO ['+@DbDest+ '].'+ [dbo].[SEQUENCE]
([name]
,[usr]
,[value]
)
select [name], [usr], [value] FROM ['+@Srvsource + '].'+ @Dbsource + '.'[dbo].[SS_SEQUENCE] where [usr] = '@Schsource''
execute (@sql)
print (@sql)
UPDATE [dbo].[SEQUENCE]
SET [usr] = @Schdest
where [usr] = @Schsource
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = @Srvsource)
EXEC master.dbo.sp_dropserver @server=@Srvsource, @droplogins='droplogins'
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot
bibi
bibi
ASKER
I have another error with this script :
ALTER PROCEDURE [dbo].[move_sequences]
@Srvsource varchar (30),
@Dbsource varchar(30),
@Schsource varchar(30),
@SchDest varchar(30),
@DbDest varchar(30),
@Pwd varchar (30)
AS
EXEC master.dbo.sp_addlinkedser
/* For security reasons the linked server remote logins password is changed with ######## */
declare @sql1 nvarchar(max);
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrv
exec sp_executesql @sql1
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
declare @sql varchar(max)
set @sql = 'INSERT INTO ['+@DbDest+ '].'+ [dbo].[SEQUENCE]
([name]
,[usr]
,[value]
) select [name], [usr], [value]
set @sql=@sql+' FROM ['+@Srvsource + '].['+ @Dbsource + '].dbo.SEQUENCE'
set @sql=@sql+' where [usr] = '''+@Schsource+''' execute (@sql)'
execute (@sql)
print (@sql)
UPDATE [dbo].[SEQUENCE]
SET [usr] = '@Schdest'
where [usr] = '@Schsource'
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = @Srvsource)
EXEC master.dbo.sp_dropserver @server=@Srvsource, @droplogins='droplogins'
Regards
bibi