-- Find server name, instancename and version
Declare @insname varchar(25)
if convert(varchar(25),serverproperty('instancename')) is null
set @insname='MSSQLSERVER'
else
set @insname=convert(varchar(25),serverproperty('instancename'))
select host_name() as 'HostName',@insname as 'Instance',
serverproperty('productversion') as 'Ver', serverproperty('productlevel') as 'Level',
serverproperty('edition') as 'Edition'
if @insname<>'MSSQLSERVER'
begin
select 'THIS IS BAD! You must reinstall MSSQL' as 'MSSQL is a named instance!'
end
begin
Declare @inspath varchar(10),@reg varchar(100),@tcpremote as int
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@value_name=@insname,
@value=@inspath OUTPUT
set @reg='Software\Microsoft\Microsoft SQL Server\' + @inspath + '\MSSQLSERVER\SuperSocketNetLib\tcp\'
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg,
@value_name='Enabled',
@value=@tcpremote OUTPUT
select @inspath as 'Instance path',@tcpremote as 'Remote connections enabled?'
if @tcpremote='0'
begin
EXEC master.dbo.xp_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg, @value_name='Enabled', @type='REG_DWORD',@value=1
select '! Please reboot !' as 'I had to enable remote connections'
end
else
begin
select 'This is good!' as 'Remote connections already enabled'
end
end
