?
Solved

script for insert sequences

Posted on 2010-03-28
4
Medium Priority
?
547 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:bibi92
  • 2
4 Comments
 

Author Comment

by:bibi92
ID: 28887277
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
0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 1000 total points
ID: 28955291
In the update statement:

UPDATE [dbo].[SEQUENCE]
SET [usr] = '@Schdest'
where [usr] = '@Schsource'

you have your variable references in quotes. Remove them.

Were there any other errors?
0
 
LVL 10

Assisted Solution

by:Bodestone
Bodestone earned 1000 total points
ID: 28957948
here:
set @sql=@sql+' FROM ['+@Srvsource + '].['+ @Dbsource + '].dbo.SEQUENCE'
set @sql=@sql+' where [usr] = '''+@Schsource+''' execute (@sql)'

Why do you have execute(@sql) as part of the  @sql variable?
--here

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 31708052
Thanks a lot
bibi
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question