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