SQL Server 2000 链接服务器脚本工具
4.55/5 (15投票s)
2004 年 9 月 19 日
4分钟阅读
103413
3107
用于生成 SQL Server 2000 中链接服务器脚本的工具，使用 C# 和 SQLDMO。
引言
SQL Server 目前不提供生成现有实例上链接服务器脚本的选项，它为其他对象提供了此功能，我们在上下文菜单中看到“生成 SQL 脚本”。本文提供的工具会为链接服务器创建脚本，并提供将脚本保存到文件或立即在另一台服务器上运行它的选项。生成的脚本使用未公开的 SQL Server 扩展存储过程来访问注册表，并且只能由 sysadmin 使用。有多种方法可以创建此脚本。我发现 SQLDMO 非常有用，因为它能够轻松地遍历 SQLDMO 对象并将属性转换为 SQL 语句。使用其他方法，如 ADO 或存储过程调用，需要更多的代码行并使编程逻辑复杂化。SQLDMO 的一个问题是它在 SQLDMO_SRVOPTION_TYPE 枚举中没有“Disallow Adhoc Access”提供程序选项（我不知道为什么！）。为了克服这个限制，我们可以使用 xp_instance_regread 或 xp_instance_regenumvalues 调用来获取值并生成脚本。
使用该工具
下图显示了该工具的运行情况
运行程序
- 在组合框中输入 SQL Server 实例名称，或按“获取服务器列表”按钮填充列表以选择一个。
- 输入用户名和密码，或选中“受信任的连接”。
- 按“脚本链接服务器”生成脚本并将其放入文本框中。注意：生成的
sp_addlinkedsrvlogin语句中将缺少密码，脚本才能正常运行，需要提供密码。 - 您可能想
- 将 SQL 语句复制到剪贴板
- 将它们保存到文件
- 通过按“从脚本创建链接服务器”直接从工具本身针对 SQL Server 运行它们
代码详情
在项目引用中添加对 COM 库下的 Microsoft SQLDMO Object Library 的引用，并且一个 using SQLDMO; 语句会负责在代码中使用该命名空间。大多数脚本语句都在 OnScript() 事件处理程序中，该处理程序使用辅助函数 GetProviderOptionScript()、GetOnOffValue() 和 GetSQLStringValue()。现在，让我们仔细看看 OnScript() 方法。以下代码生成 sp_addlinkedserver SQL 语句。由于链接服务器是服务器级别的对象，我们遍历 LinkedServers 集合。
foreach (LinkedServer2 ls in sqlserver.LinkedServers)
{
........
scriptText += "sp_addlinkedserver '" + ls.Name + "','" + ls.ProductName
+ "'";
// script Provider Parameters if the product is not "SQL Server"
if (ls.ProductName != "SQL Server")
scriptText += ",'" + ls.ProviderName +
"','" + ls.DataSource +
"','" + ls.Location + "','"
+ ls.ProviderString + "','" + ls.Catalog + "'";
scriptText += "\ngo\n";
........
........
}
接下来，链接服务器使用的登录名通过以下代码段进行脚本化。我们使用一个辅助函数 GetSQLStringValue()，它将返回 null 或字符串值本身。在 SQLDMO 中，密码不支持 get 属性！因此，我们将脚本中的密码替换为 PASSWORD_STRING（在我的程序中定义为“enter password here”）
// script the linked server logins
foreach (LinkedServerLogin login in ls.LinkedServerLogins)
{
scriptText += ("sp_addlinkedsrvlogin '" + ls.Name + "','" +
((login.Impersonate)? "true" : "false") + "',"
+ GetSQLStringValue(login.LocalLogin)
+ "," + GetSQLStringValue(login.RemoteUser) +
"," + PASSWORD_STRING + "\ngo\n");
}
之后，链接服务器选项将通过下面的代码进行脚本化。值得注意的是传递给 sp_serveroption 存储过程的连接超时和查询超时值之间的区别。存储过程接受连接超时的数值和查询字符串值！对于所有其他选项（除排序规则名称外），它们在 SQL Server 中表示为位，我们使用 GetOnOffValue() 辅助函数来检查位是否设置，然后返回“on”或“off”值。
// script the following Server Options -
// Collation Compatible, Data Access, RPC, RPC Out,
// Use Remote Collation, Collation Name,
// Connection Timeout, Query Timeout
scriptText += ("sp_serveroption '" + ls.Name + "','collation compatible',"
+ GetOnOffValue(ls.Options &
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_CollationCompatible)
+ "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','data access',"
+ GetOnOffValue(ls.Options &
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_DataAccess)
+ "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','rpc',"
+ GetOnOffValue(ls.Options & SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_RPC)
+ "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','rpc out',"
+ GetOnOffValue(ls.Options & SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_RPC_out)
+ "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','use remote collation',"
+ GetOnOffValue(ls.Options &
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_UseRemoteCollation)
+ "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','collation name',"
+ GetSQLStringValue(ls.CollationName) + "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','connect timeout',"
+ ls.ConnectTimeout + "\ngo\n");
scriptText += ("sp_serveroption '" + ls.Name + "','query timeout','"
+ ls.QueryTimeout + "'\ngo\n");
在方法结束时，将对提供程序选项进行脚本化。应该注意的是，这些选项是提供程序级别的，而不是链接服务器级别的。也就是说，我们可能正在为多个链接服务器使用相同的提供程序。这就是为什么为每个提供程序仅脚本化一次选项就足够了。providersListForOptions 字符串用于跟踪已生成脚本的提供程序，以避免重复脚本。我认为如果 SQLDMO 可以在服务器级别提供这些选项，而不是将它们附加到 LinkedServer 对象，那会更好。
应该注意的是，这些提供程序选项由 SQL Server 存储在注册表中，位于 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\PROVIDER 键下作为值。GetProviderOptionScript() 辅助方法生成将这些值写入或从注册表中删除的代码。我们在下面的辅助函数中使用未公开的扩展存储过程 xp_regdeletevalue 和 xp_regwrite。
需要注意的一点是——我不知道为什么 SQLDMO 在 SQLDMO_SRVOPTION_TYPE 枚举中没有“Disallow Adhoc Access”选项。您可以通过打开“添加链接服务器”对话框在任何 SQL Server 上找到它。如果有人知道，请在此分享。
另外，为了克服这个限制，我们可以使用 xp_instance_regread 或 xp_instance_regenumvalues 调用来获取值并生成脚本。
private string GetProviderOptionScript(SQLDMO_SRVOPTION_TYPE options,
SQLDMO_SRVOPTION_TYPE check,
string value_name,
string provider)
{
string optionString = "exec master.."
+ (((options|check) == 0)?"xp_regdeletevalue ":"xp_regwrite ")
+ @"'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Providers\"
+ provider + "','" + value_name + "'"
+ (((options|check) == 0)? "" : ",'REG_DWORD',1");
return optionString + "\ngo\n";
}
private void OnScript(object sender, System.EventArgs e)
{
.......
.......
// script the Provider Options if the product is not "SQL Server"
// send them into registry directly
if (providersListForOptions.IndexOf(ls.ProviderName, 0) == -1)
{
scriptText += "\n-- Options for provider : " + ls.ProviderName
+ "\n";
// add the provider to the list
providersListForOptions += (ls.ProviderName + ",");
scriptText += (
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_DynamicParameters,
"DynamicParameters", ls.ProviderName) +
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_NestedQueries,
"NestedQueries", ls.ProviderName) +
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_LevelZeroOnly,
"LevelZeroOnly", ls.ProviderName) +
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_InProcess,
"AllowInProcess", ls.ProviderName) +
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_NonTransacted,
"NonTransactedUpdates", ls.ProviderName) +
GetProviderOptionScript(ls.Options,
SQLDMO_SRVOPTION_TYPE.SQLDMOSrvOpt_IndexAsAccessPath,
"IndexAsAccessPath", ls.ProviderName));
}
......
......
sqlserver.DisConnect();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
其余代码不言自明。