Can linked server be disabled?

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could set up a job to disable data access and the enable it back.

-- Disable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = '0'

-- Enable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = '1'

-- Gianluca Sartori

SkyBVI SSCoach Points: 15128 More actions · Answer 2

I tried to do that, It threw an error:-

Msg 15600, Level 15, State 1, Procedure sp_serveroption, Line 177

An invalid parameter or option was specified for procedure 'sys.sp_serveroption'.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

My bad, the parameter had to be passed in as boolean...

-- Disable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'false'

-- Enable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'true'

-- Gianluca Sartori

SkyBVI SSCoach Points: 15128 More actions · Answer 4

@ Gianluca

Thanks a lot.

Regards,

Sushant

DBA

West Indies.

Regards
Sushant Kumar
MCTS,MCP