Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_serveroption

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_adddistributor (Procedure)
sp_dropdistributor (Procedure)
sp_MSadd_subserver (Procedure)
sp_MSdrop_subserver (Procedure)
sp_MSprocesslogshipmonitorlink (Procedure)
sp_MSprocesspublisherlink (Procedure)
sp_MSrepl_adddistpublisher (Procedure)
sp_MSrepl_testadminconnection (Procedure)
sp_vupgrade_heterogeneous_publishers (Procedure)
sp_vupgrade_publisher (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash