troubleshooting Question

Script for create linkedserver

Avatar of bibi92
bibi92Flag for France asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
4 Comments1 Solution422 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros