creating a linked sql in a sproc

hi,

here sproc create linked server. ran successfully.

but did not create link myremotesql, instead, linked @serverinstance.

 

use [master]
go

set ansi_nulls on
go
set quoted_identifier on
go

alter procedure [dbo].[createdlinkedsql]
@serverinstance varchar (50)
as

if exists (select * sys.servers [name] = n'@serverinstance')
 exec sp_dropserver @serverinstance, 'droplogins'

exec master.dbo.sp_addlinkedserver @server = n'@serverinstance',
@srvproduct=n'sql server'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n'@serverinstance',
@useself=n'false',@locallogin=null,@rmtuser=n'mylogin',@rmtpassword='########'

 

 

use [master]
go

declare @return_value int

exec @return_value = [dbo].[createdlinkedsql]
  @serverinstance = n'myremotesql'

select 'return value' = @return_value

go

 

any help?

 

thanks

 

take @server = n'@serverinstance' out of quotes.  use @server = @serverinstance

 

try instead:

 

 

use [master]
go

set ansi_nulls on
go
set quoted_identifier on
go

alter procedure [dbo].[createdlinkedsql]
@serverinstance varchar (50)
as

if exists (select * sys.servers [name] = @serverinstance)
 exec sp_dropserver @serverinstance, 'droplogins'

exec master.dbo.sp_addlinkedserver @server = @serverinstance,
@srvproduct=n'sql server'
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@serverinstance,
@useself=n'false',@locallogin=null,@rmtuser=n'mylogin',@rmtpassword='########'

 

 

SQL Server  >  SQL Server Database Engine