As a prezzie here's a stored procedure to connect to and disconnect from a linked server:
--connects to and disconnects from the mysql online database
--example call to connect:
--dbo.LinkedServerConnect_SP @connect = 1
--example call to disconnect:
--dbo.LinkedServerConnect_SP @connect = 0
CREATE PROC dbo.LinkedServerConnect_SP
(@connect as int)
AS
BEGIN
if @connect = 1
begin
if not exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sp\_addlinkedserver 'yadayada', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=yadayada;SERVER=mysql.yadayada.com;option=512;uid=yadayada;pwd=yadayada'
end
end
if @connect = 0
begin
if exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sys.sp\_dropserver @server = 'yadayada', @droplogins = 'droplogins';
end
end
end
GO
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
