Introduction
SQL Server presently does not provide option to generate script for linked servers on an existing instance it provides for other objects which we see as "Generate SQL Scripts" on context menus. The tool provided in this article creates script for linked servers and gives an option to either save the script to a file or run it immediately on another server. The script generated uses undocumented SQL Server extended stored procedures to access registry and can only be used by a sysadmin. There are number of ways to create this script. I found SQLDMO very useful because of the ease with which we can traverse through the SQLDMO objects and convert the properties into SQL statements. The same effort with any other way, like using ADO or stored procedure calls, would have taken more lines of code and complicated the programming logic. One problem with SQLDMO is that it does not have the "Disallow Adhoc Access" provider option in the SQLDMO_SRVOPTION_TYPE enumeration (I do not know why!). To overcome this limitation, we can use a xp_instance_regread or xp_instance_regenumvalues call to get the value and generate the script.
Using the tool
Screen shot below shows the tool in action:
To run the program:
- Give the SQL Server instance name in the combo box, or press "Get Servers List" button to populate the list to select one.
- Give the user name and password, or check "Trusted Connection".
- Press "Script Linked Servers" to generate and put the script in the text box. Note: A password will be missing in
sp_addlinkedsrvlogin statements generated, it should be provided for the SQL scripts to run properly.
- You may want to:
- copy the SQL statements to the clipboard
- save them to a file
- run them against a SQL Server directly from the tool itself by pressing "Create Linked Servers from Script"
Code Details
A reference to Microsoft SQLDMO Object Library under COM libraries is added to the project references, and a using SQLDMO; statement takes care of using the namespace in the code. Most of the scripting statements are inside the OnScript() event handler which uses the helper functions GetProviderOptionScript(), GetOnOffValue(), and GetSQLStringValue() in the code. Now, let us take a closer look at the OnScript() method. The following code generates the sp_addlinkedserver SQL statement. Since linked servers are server level objects, we iterate through the LinkedServers collection:
foreach (LinkedServer2 ls in sqlserver.LinkedServers)
{
........
scriptText += "sp_addlinkedserver '" + ls.Name + "','" + ls.ProductName
+ "'";
if (ls.ProductName != "SQL Server")
scriptText += ",'" + ls.ProviderName +
"','" + ls.DataSource +
"','" + ls.Location + "','"
+ ls.ProviderString + "','" + ls.Catalog + "'";
scriptText += "\ngo\n";
........
........
}
Next, the logins used by the linked servers are scripted by the following code segment. We use a helper function GetSQLStringValue() which will return null or the string value itself. In SQLDMO, get property is not supported on passwords! Hence, we substitute the passwords in the script with PASSWORD_STRING (defined in my program as 'enter password here'):
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");
}
After this, the linker server options are scripted by the code shown below. It is interesting to note the difference between the Connection Timeout and Query Timeout values passed to the sp_serveroption stored procedure. The stored procedure accepts numeric value for Connection Timeout and string value for Query Timeout! For all other options, which are represented as bits inside SQL Server (except Collation Name), the GetOnOffValue() helper function is used to return a "on" or "off" value after checking if the bit is set or not.
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");
At the end of the method, the Provider Options are scripted. It should be noted that these options are at the provider level and not at the linked server level. I.e., we may be using the same provider for a number of linked servers. That is why it is enough if we script the options only once for every provider. The providersListForOptions string is used to keep track of the providers for which the script is already generated, to avoid duplicate scripting. I think it's better if SQLDMO can provide these options under a separate collection at server level instead of attaching them to a LinkedServer object.
It should be noted that these provider options are stored by SQL Server in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\PROVIDER key as values. The GetProviderOptionScript() helper method generates the code to write into or delete these values from the registry. We use undocumented extended stored procedures called xp_regdeletevalue and xp_regwrite inside the helper function as given below.
One point to be noted - I do not know why SQLDMO does not have the "Disallow Adhoc Access" option in the SQLDMO_SRVOPTION_TYPE enumeration. You can find it on any SQL Server by opening the Add Linked Server dialog box. If anybody has any idea, please share it here.
Also, to overcome this limitation, we can use a xp_instance_regread or xp_instance_regenumvalues call to get the value and generate the script.
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)
{
.......
.......
if (providersListForOptions.IndexOf(ls.ProviderName, 0) == -1)
{
scriptText += "\n-- Options for provider : " + ls.ProviderName
+ "\n";
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;
}
}
The rest of the code is self explanatory.
