June 8, 2012

sp_setnetname (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_setnetname(nvarchar @server
, nvarchar @netname)

MetaData:

 create procedure sys.sp_setnetname  
@server sysname -- server name
,@netname sysname -- new net name
as
DECLARE @sqlproduct bit,
@is_linked bit,
@dataaccess bit

-- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_setnetname')
return (1)
end

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any linked server') = 1)
begin
raiserror(15247,-1,-1)
return (1)
end

BEGIN TRAN

-- RESOLVE/LOCK SERVER NAME
EXEC %%LinkedServer ( Name = @server ) . Lock ( Exclusive = 1 )
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
raiserror(15015,-1,-1,@server)
return (1)
END

-- GET INFORMATION ON THIS SERVER
SELECT @sqlproduct = CASE WHEN srvproduct = N'SQL Server' THEN 1 ELSE 0 END,
@dataaccess = dataaccess
FROM master.dbo.sysservers WHERE srvname = @server

-- MUST BE A SQL SERVER
IF @sqlproduct = 0
begin
ROLLBACK TRAN
raiserror(15576,-1,-1,@server)
return (1)
end

-- CHECK FOR LOOPBACK SERVER AND ISSUE WARNING
-- Only check linked server for "data access" since that's where the limitation
-- lies. Replication calls this code although only for RPC servers, so they
-- shouldn't be seeing this message
if @netname = @@SERVERNAME and @server <> @netname AND @dataaccess = 1
raiserror(15577,-1,-1) -- THIS IS ONLY A WARNING

-- is this a linked server?
select @is_linked = is_linked from sys.servers where name = @server

-- DO THE UPDATE
EXEC %%LinkedServer ( Name = @server ) . SetDatasource ( Datasource = @netname )

if @is_linked = 1
begin
-- EMDEventType(x_eet_Alter_Linked_Server), EMDUniversalClass(x_eunc_Linked_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 263, ID = 102, ID = 0, ID = 0, Value = @server,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @server, Value = @netname, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
else
begin
-- EMDEventType(x_eet_Alter_Remote_Server), EMDUniversalClass(x_eunc_Linked_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 217, ID = 100, ID = 0, ID = 0, Value = @server,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 2, Value = @server, Value = @netname, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end

-- SUCCESS
COMMIT TRAN
return (0) -- sp_setnetname

No comments:

Post a Comment

Total Pageviews