Dropping and Adding Link Servers [duplicate]

Question

This question already has answers here:

Closed 8 years ago.

Possible Duplicate:
SQL Server: Is there an “IF EXISTS” test for a linked server?

I am trying to create a block of code that will Create a Link Server/Drop a link server. I had posted similar question about adding and dropping a function and the solution was to drop the function and re-create it.

So I want to take the same approach with the LinkServer. DROP it and Re-create it every time I run this code.

However, I cannot re-create the link server after dropping and I get the error message: Server already exists.

Here is my code:

IF OBJECT_ID('AccessDataSource') IS NOT NULL
EXEC master.sys.sp_dropserver 'AccessDataSource','droplogins'

GO

EXEC sp_addlinkedserver
@server     = 'AccessDataSource'
,@srvproduct = 'OLE DB Provider for ACE ' 
,@provider   = 'Microsoft.ACE.OLEDB.12.0'
,@datasrc    = 'N:\Database_Tools\AccessDB\delphi.accdb'
GO

Answer 1

This is actually a duplicate of at least SQL Server: Is there an "IF EXISTS" test for a linked server?

But what you want is something like:

IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AccessDataSource')
EXEC master.sys.sp_dropserver 'AccessDataSource','droplogins'  
GO

As is also answered in the other question - you might want to take a look at: http://msdn.microsoft.com/en-us/library/ms178530.aspx