Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

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_addlinkedserver @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_addlinkedsrvlogin @rmtsrvname =N''' + @Srvsource + ''', @useself=N''False'',@locallogin=NULL,@rmtuser=''sa'',@rmtpassword=''' +@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
Avatar of bibi92
bibi92
Flag of France image

ASKER

Hello,

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_addlinkedserver @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_addlinkedsrvlogin @rmtsrvname =N''' + @Srvsource + ''', @useself=N''False'',@locallogin=NULL,@rmtuser=''sa'',@rmtpassword=''' +@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]

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
ASKER CERTIFIED SOLUTION
Avatar of Tim Humphries
Tim Humphries
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Thanks a lot
bibi