Tool to script Linked Servers in SQL Server 2000

• Download source files - 9.72 Kb
• Download executables - 267 Kb

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
               + "'";

    // 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";
    ........
    ........
}

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'):

// 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");
}

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.

// 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");

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)
    {
        .......
        .......
        // 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;
    }
}

The rest of the code is self explanatory.