Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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