Script Linked Servers Tool to script Linked Servers in SQL Server 2000
Download

Script Linked Servers Ranking & Summary

Script Linked Servers Tags

generator generate SQL Server script generator generate script server script SQL Server Script linked data linked

Script Linked Servers Description

This little tool will allow you to generate scripts for Linked Servers in SQL Server 2000, using C# and SQLDMO. 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.

Script Linked Servers Related Software