February 11, 2011 at 7:48 am
Hello everyone.
I’m having two problems which maybe someone can help me with. I have a stored procedure which performs 3 things:
1. Read database containing a list of servers. The database has 3 columns: servername, port_nbr, isLinked
2. If isLinked = ‘N’ execute sp_addlinkedserver.
3. Updated islinked = ‘Y’
Problem 1: sp_addlinkedsever is returning an error.
Msg 15427, Level 16, State 1, Procedure sp_addlinkedserver, Line 27
You must specify a provider name for unknown product '1433'.
1433 is the value for the port number parameter.
Problem 2: Even though sp_addlinkedserver returns an error, the code following is executed resulting in isLinked being set to ‘Y’. I can’t figure out how to trap the error. Tried @@ERROR but that didn’t work.
Any suggestions will be greatly appreciated;---------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_LINK_SERVER
AS
DECLARE @id INT
,@ServerName nvarchar(250)
,@Port_nbr nvarchar(5)
,@isLinked nvarchar (5)
DECLARE ServerList CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT PK_ID
, ServerName
, Port_nbr
, isLinked
FROM dbo.HOSTSERVERS
WHERE isLinked <> 'Y'
OPEN ServerList
-- get very first record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, Port_nbr
, @isLinked
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
EXEC sp_addlinkedserver @servername,@Port_nbr
END
BEGIN
UPDATE dbo.HOSTSERVERS
SET isLinked = 'Y'
WHERE pk_id = @id
END
-- get next record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, @Port_nbr
, @isLinked
END
February 15, 2011 at 10:41 am
There is a few syntax errors on your code.
Below is missing @ in front of Port_nbr.
OPEN ServerList
-- get very first record
FETCH NEXT FROM ServerList
INTO @id
, @ServerName
, Port_nbr
, @isLinked
In addition, SQL is expecting product name on the second parameter, hence you can't add port info.
EXEC sp_addlinkedserver @servername,@Port_nbr
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
In terms of trapping errors, you can use begin try/end try and begin catch/end catch to capture errors.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply