February 17, 2009 at 10:39 am
Hi,
I'm trying to set up a stored procedure that reads information from a table and creates a linked server. If I set up the linked server through SQL Server Management Studio using the same information it works (I can query data on the linked server). If I set it up using the stored procedure I can see it in the list of linked servers but when I query the data I get a "Could not find server 'APP-SMT04' in sysservers. Execute sp_addlinkedserver to add the server to sysservers." error. Here's the code (SQL 2005, running under WIndows 2003 SP2):
ALTER PROCEDURE [dbo].[sp_CreateLinkedServers]
AS
BEGIN
SET NOCOUNT ON;
declare @_servername nchar(80),
@_serverproduct nchar(80),
@_accountname nchar(80),
@_password nchar(80)
declare users_cursor cursor read_only forward_only
for
select servername,serverproduct,accountname,password from
ictables.dbo.linkedserver_master where Runit is not null
open users_cursor
fetch next from users_cursor
into @_servername, @_serverproduct, @_accountname, @_password
while (@@FETCH_STATUS=0)
begin
declare @servername nchar(80),
@serverproduct nchar(80),
@accountname nchar(80),
@password nchar(80),
@x int
set @x = 1
set @servername = @_servername
set @serverproduct = @_serverproduct
set @accountname = @_accountname
set @password = @_password
execute @x=master.dbo.sp_addlinkedserver @server=@servername,
@srvproduct=@serverproduct
if @x=0
execute @x=master.dbo.sp_addlinkedsrvlogin @servername, N'False', NULL,
@accountname, @password
fetch next from users_cursor
into @_servername, @_serverproduct, @_accountname, @_password
end
END
It executes fine, no errors, I can see the linked server in the list but I get an error if I try to use it. Any idea what I'm missing in the stored procedure?
Thanks,
JTM
February 17, 2009 at 2:36 pm
More Information. Named Pipes protocol is disabled on the server. Any way to force tcp/ip when using sp_addlinkedserver? Is there an additional configuration command or something? Using profiler I didn't see anything.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply