Since I use both SSMS 2005 and SSMS 2008, I made the importable registered servers file fit the format for 2005, then I could just use the "Import previous registered servers" function to import them into SSMS 2008.
In order for this script to work correctly, I have a table that holds a list of all our instances with their environments and versions. This will make a category for each environment and for each version. So an instance will appear in both categories.
set nocount on
declare @fulllist varchar(MAX)
declare @serverheader varchar(8000)
declare @serverfooter varchar(8000)
declare @serverentry varchar(max)
declare @environments table
(
environment varchar(20)
, rowid int
)
declare @versions table
(
version varchar(15)
, rowid int
)
declare @instances table
(
instancename varchar(100)
, environment varchar(20)
, version varchar(15)
, rowid int
)
declare @x int
declare @y int
insert into @environments
select case
when Environment like 'Dev%' then 'Development'
else Environment
end as 'Environment'
,ROW_NUMBER() OVER(order by Environment) as 'Rowid'
from Instances
group by Environment
insert into @versions
select case
when Version = '8.0' then 'SQL 2000'
when Version = '9.0' then 'SQL 2005'
when Version = '10.0' then 'SQL 2008'
when Version = '10.50' then 'SQL 2008 R2'
end as 'Version'
,ROW_NUMBER() OVER(order by Version) as 'Rowid'
from Instances
group by Version
insert into @instances
select SQLInstance
,case
when Environment like 'Dev%' then 'Development'
else Environment
end as 'Environment'
,case
when Version = '8.0' then 'SQL 2000'
when Version = '9.0' then 'SQL 2005'
when Version = '10.0' then 'SQL 2008'
when Version = '10.50' then 'SQL 2008 R2'
end as 'Version'
,ROW_NUMBER() OVER(order by sqlinstance) as 'Rowid'
from instances
set @fulllist =
'<?xml version="1.0" encoding="utf-8"?>
<Export serverType="8c91a03d-f9b4-46c0-a305-b5dcc79ff907">
<ServerType id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
'
set @serverheader =
' <ConnectionInformation>
<ServerType>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</ServerType>
<ServerName>'
set @serverfooter =
'</ServerName>
<AuthenticationType>0</AuthenticationType>
<UserName />
<Password />
<AdvancedOptions>
<PACKET_SIZE>4096</PACKET_SIZE>
<CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>
<EXEC_TIMEOUT>0</EXEC_TIMEOUT>
<ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>
</AdvancedOptions>
</ConnectionInformation>
</Server>
'
set @x = (select MIN(rowid) from @environments)
while(@x <= (select MAX(rowid) from @environments))
begin
select @fulllist +=
' <Group name="'+environment+'" description="">
'
from @environments
where rowid = @x
set @y = (select MIN(rowid) from @instances)
while(@y <= (select MAX(rowid) from @instances))
begin
if((select environment from @instances where rowid = @y) <> (select environment from @environments where rowid = @x))
begin
set @y = @y +1
continue
end
select @serverentry = ' <Server name="'+instancename+'" description="">'+@serverheader+instancename+@serverfooter
from @instances
where rowid = @y
select @fulllist += @serverentry
set @y = @y + 1
end
select @fulllist +=
' </Group>
'
set @x = @x + 1
end
set @x = (select MIN(rowid) from @versions)
while(@x <= (select MAX(rowid) from @versions))
begin
select @fulllist +=
' <Group name="'+version+'" description="">
'
from @versions
where rowid = @x
set @y = (select MIN(rowid) from @instances)
while(@y <= (select MAX(rowid) from @instances))
begin
if((select version from @instances where rowid = @y) <> (select version from @versions where rowid = @x))
begin
set @y = @y +1
continue
end
select @serverentry = ' <Server name="'+instancename+'" description="">'+@serverheader+instancename+@serverfooter
from @instances
where rowid = @y
select @fulllist += @serverentry
set @y = @y + 1
end
select @fulllist +=
' </Group>
'
set @x = @x + 1
end
select @fulllist +=
' </ServerType>
</Export>'
select convert(xml,@fulllist)
