• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Script for create linkedserver

Hello,

I try to script the creation of linkserver, I have this error :
Msg 102, Level 15, State 1, Procedure transfer_schema2, Line 17
Incorrect syntax near '@rmtsrvname'.

Do you have a solution?
CREATE PROCEDURE [dbo].[transfer_schema2]
@Srvsource varchar (30),
@Dbsource varchar(30),
@Schsource 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
Thanks

Bibi
0
bibi92
Asked:
bibi92
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to duplicate any single quote within the string:
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname =N''' + @Srvsource + ''', @useself=N''False'',@locallogin=NULL,@rmtuser=''sa'',@rmtpassword=''' +@Pwd + ''' '
exec sp_executesql @sql1

Open in new window

0
 
dodge20Commented:
Change  
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrvlogin '@rmtsrvname=N'@Srvsource',@useself=N''False'',@locallogin=NULL,@rmtuser=''sa'',@rmtpassword='''  +@Pwd + '''''

to
set @sql1 = 'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'''+@Srvsource +',@useself=N''False'',@locallogin=NULL,@rmtuser=''sa'',@rmtpassword=''' +@Pwd + ''''

Open in new window

0
 
bibi92Author Commented:
Thanks a lot, perfect. Regards bibi
0
 
dodge20Commented:
Disregard my answer, I had a typo and angelIII's looks to be correct.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now