删除和添加链接服务器 [英] Dropping and Adding Link Servers

问题描述

可能的重复:
SQL 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.

所以我想对 LinkServer 采取相同的方法.每次运行此代码时，删除它并重新创建它.

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.

这是我的代码:

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

推荐答案

这实际上是至少 SQL Server:是否有IF EXISTS"?测试链接服务器?

但你想要的是:

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

在另一个问题中也有回答 - 您可能想看看:http://msdn.microsoft.com/en-us/library/ms178530.aspx

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

这篇关于删除和添加链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！