June 8, 2012

sp_serveroption (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_serveroption(nvarchar @server
, varchar @optname
, nvarchar @optvalue)

MetaData:

 create procedure sys.sp_serveroption  
@server sysname, -- server name to change
@optname varchar(35), -- option name to turn on/off
@optvalue nvarchar(128) -- true or false, on or off, collation name, or timeout value
as
-- VARIABLES
SET NOCOUNT ON
declare @fSet bit, -- 0 or 1 for setting boolean option
@timeout int,
@collationID int,
@ret int,
@is_linked bit


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

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any linked server') = 1)
begin
EXEC %%System().AuditEvent(ID = 1279349580, Success = 0, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @server)
raiserror(15247,-1,-1)
return (1)
end
else
begin
EXEC %%System().AuditEvent(ID = 1279349580, Success = 1, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @server)
end

-- DO SERVER-INDEPENDENT CHECKS AGAINST MOST OPTIONS
SELECT @optname = RTRIM(lower(@optname collate Latin1_General_CI_AS))
IF @optname NOT IN ('name')
SELECT @optvalue = RTRIM(lower(@optvalue collate Latin1_General_CI_AS))

IF @optname = 'collation name'
BEGIN
-- Validate Collation Name: May reset by string 'null' or NULL value
IF @optvalue = 'null'
SELECT @optvalue = NULL
SELECT @collationID = convert(int, COLLATIONPROPERTY(@optvalue, 'collationid'))
IF @optvalue IS NOT NULL AND @collationID IS NULL
goto bad_option

-- VALIDATE COLLATION PROPERTIES (no-op for NULL)
if convert(int, COLLATIONPROPERTY(@optvalue, 'isunicodeonly')) = 1
BEGIN
raiserror(15301, -1, -1, @optvalue)
return (1)
END
if convert(int, COLLATIONPROPERTY(@optvalue, 'issupportedbyos')) = 0
BEGIN
raiserror(15394, -1, -1, @optvalue)
return (1)
END
END
ELSE IF @optname IN ('connect timeout','query timeout')
BEGIN
-- TIMEOUT OPTIONS: Value must be integer-numeric >= 0
IF ISNUMERIC (@optvalue) = 0 OR convert (int, @optvalue) IS NULL OR convert (int, @optvalue) < 0
goto bad_option
SELECT @timeout = convert (int, @optvalue)
END
ELSE IF @optname IN ('rpc','pub','sub','dist','nonsqlsub','rpc out','data access',
'collation compatible','system','use remote collation','lazy schema validation',
'remote proc transaction promotion')
BEGIN
-- Get value for Bit-valued option
-- NOTE: CANNOT MAKE A SYSTEM SERVER INTO NON-SYSTEM --
SELECT @fSet = CASE WHEN @optvalue IN ('true','on') THEN 1
WHEN @optvalue IN ('false','off') AND @optname <> 'system' THEN 0
ELSE NULL END
IF @fSet IS NULL
goto bad_option

-- Lazy Schema Validation for Enterprise-edition only
IF @optname = 'lazy schema validation' AND @fSet = 1 AND serverproperty('EngineEdition') <> 3 -- Enterprise edition
BEGIN
raiserror(15169,-1,-1, @optname)
RETURN 1
END
END
ELSE IF @optname = 'name'
BEGIN
-- RENAME SERVER: Validate new name
EXEC @ret = sys.sp_validname @optvalue
IF @ret <> 0
RETURN @ret
END
ELSE
goto bad_option

BEGIN TRANSACTION

-- 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

-- DO THE SERVER-DEPENDENT CHECKS AND THE UPDATE
IF @optname = 'rpc'
EXEC %%LinkedServer(Name=@server).SetRPCIn( Value = @fSet )
ELSE IF @optname = 'rpc out'
EXEC %%LinkedServer(Name=@server).SetRPCOut( Value = @fSet )
ELSE IF @optname = 'data access'
EXEC %%LinkedServer(Name=@server).SetDataAccess( Value = @fSet )
ELSE IF @optname = 'system'
EXEC %%LinkedServer(Name=@server).SetSystem( Value = @fSet )
ELSE IF @optname = 'pub'
EXEC %%LinkedServer(Name=@server).SetReplPub( Value = @fSet )
ELSE IF @optname = 'sub'
EXEC %%LinkedServer(Name=@server).SetReplSub( Value = @fSet )
ELSE IF @optname = 'dist'
BEGIN
-- ONLY ONE SERVER MAY BE A DISTRIBUTION SERVER
IF @fSet = 1
BEGIN
DECLARE @distributor sysname
SELECT @distributor = srvname from master.dbo.sysservers where dist = 1
IF @distributor is not null
BEGIN
ROLLBACK TRAN
raiserror(14099,-1,-1, @distributor)
RETURN 1
END
END
EXEC %%LinkedServer(Name=@server).SetReplDist( Value = @fSet )
END
ELSE IF @optname = 'nonsqlsub'
EXEC %%LinkedServer(Name=@server).SetReplNonSQLSub( Value = @fSet )
ELSE IF @optname = 'lazy schema validation'
EXEC %%LinkedServer(Name=@server).SetLazySchemaCheck( Value = @fSet )
ELSE IF @optname = 'remote proc transaction promotion'
EXEC %%LinkedServer(Name=@server).SetDtcPromotionForRemoteProc( Value = @fSet )
ELSE IF @optname = 'use remote collation'
EXEC %%LinkedServer(Name=@server).SetUseRemoteCollation( Value = @fSet )
ELSE IF @optname = 'collation compatible'
BEGIN
EXEC %%LinkedServer(Name=@server).SetCollationCompatible( Value = @fSet )
-- IF Collation-Compatible TRUE, THEN COLLATION SETS TO NULL
IF @fSet = 1
EXEC %%LinkedServer(Name=@server).SetCollationID( Value = NULL )
END
ELSE IF @optname = 'collation name'
BEGIN
EXEC %%LinkedServer(Name=@server).SetCollationID( Value = @collationID )
-- IF NON-NULL COLLATION, Collation-Compatible SETS TO FALSE
IF @collationID IS NOT NULL
EXEC %%LinkedServer(Name=@server).SetCollationCompatible( Value = 0 )
END
ELSE IF @optname = 'connect timeout'
EXEC %%LinkedServer(Name=@server).SetConnectTimeout( Value = @timeout )
ELSE IF @optname = 'query timeout'
EXEC %%LinkedServer(Name=@server).SetQueryTimeout( Value = @timeout )
ELSE IF @optname = 'name'
BEGIN
-- Change name, checking for duplicate
EXEC %%LinkedServer(Name=@server).SetName( Name = @optvalue )
IF @@error <> 0
BEGIN
ROLLBACK TRAN
raiserror(15335,-1,-1,@optvalue,N'SERVER')
return 1
END
END

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

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 = 3, Value = @server, Value = @optname, Value = @optvalue, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
else
begin
-- EMDEventType(x_eet_Alter_Remote_Server), EMDUniversalClass(x_eunc_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 = 3, Value = @server, Value = @optname, Value = @optvalue, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end

-- SUCCESS
COMMIT TRAN
RETURN (0) -- sp_serveroption


bad_option: -- INVALID PARAMETER
raiserror(15600,-1,-1,'sys.sp_serveroption')
RETURN 1 -- sp_serveroption

No comments:

Post a Comment

Total Pageviews