@echo off 
Echo.
Echo  ............XOS CLIENT CHANGE NAME SCRIPT BATCH v.1.1............
Echo.
Echo.
Echo NOTE: ....THIS MUST BE RUN WITH ADMIN RIGHTS....

timeout /t 3 /nobreak > NUL 
Echo.
Echo.
Echo.


Echo      .... REMOVING ANY OLD INSTANCE OF SQL CHANGE NAME SCRIPT ON C:\ ....
Echo. 
Echo.
Echo.
Del "C:\Change and check SQL ServerName ALLinOne.sql" 

timeout /t 2  /nobreak > NUL 

Echo.
Echo.
Echo.
Echo      ....CREATING SQL CHANGE NAME SCRIPT....

timeout /t 2  /nobreak > NUL 
Echo.
Echo.
Echo.

echo USE master >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo GO >> "C:\Change and check SQL ServerName ALLinOne.sql"

echo DECLARE @serverproperty_servername varchar(100) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo DECLARE @servername varchar(100) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo DECLARE @loginName SYSNAME >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo DECLARE @newLoginName SYSNAME >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo DECLARE @sql NVARCHAR(1000) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo -- Get the value returned by the SERVERPROPERTY system function. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName')) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo -- Get the value returned by @@SERVERNAME global variable. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo SELECT @servername = CONVERT(varchar(100), @@SERVERNAME) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo -- Drop XOSAdmin login if it does not reflect the current server name. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo SET @loginName = SUBSTRING(@serverproperty_servername, 0, CHARINDEX('\', @serverproperty_servername)) + '\XOSAdmin' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo IF NOT EXISTS (select loginname from master.dbo.syslogins where name = @loginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo 	SELECT @newLoginName = loginname FROM master.dbo.syslogins WHERE name LIKE '%%XOSAdmin' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	IF EXISTS (select loginname from master.dbo.syslogins where name = @newLoginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		PRINT 'Dropping ' + @newLoginName + ' login' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		SET @sql = 'DROP LOGIN [' + @newLoginName + ']' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		EXEC sp_executesql @sql >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo -- Create XOSAdmin login >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo SET @loginName = SUBSTRING(@serverproperty_servername, 0, CHARINDEX('\', @serverproperty_servername)) + '\XOSAdmin' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo IF NOT EXISTS (select loginname from master.dbo.syslogins where name = @loginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	PRINT 'Creating ' + @loginName + ' login' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN TRY >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- Try to create the login. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		SET @sql = 'CREATE LOGIN [' + @loginName + '] FROM WINDOWS' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		EXEC sp_executesql @sql >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END TRY >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN CATCH >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- Check the error number. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- If error is known, inform that Windows user does not exist. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- If error is unknown, report error and contact support. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		IF (ERROR_NUMBER() = 15401) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'Unable to create login for ' + @loginName + ' as the Windows user does not exist.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'The Windows user will be created when Thunder is installed.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		ELSE >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			EXECUTE usp_GetErrorInfo >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'Please contact XOS Support.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END CATCH >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.		
echo	IF EXISTS (select loginname from master.dbo.syslogins where name = @loginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		SET @sql = 'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @loginName + ']' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		EXEC sp_executesql @sql >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END  >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo -- Create BUILTIN\Administrators login >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo SET @loginName = 'BUILTIN\Administrators' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo IF NOT EXISTS (select loginname from master.dbo.syslogins where name = @loginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	PRINT 'Creating ' + @loginName + ' login' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN TRY >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- Try to create the login. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		SET @sql = 'CREATE LOGIN [' + @loginName + '] FROM WINDOWS' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		EXEC sp_executesql @sql >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END TRY >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN CATCH >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- Check the error number. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- If error is known, inform that Windows user does not exist. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		-- If error is unknown, report error and contact support. >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		IF (ERROR_NUMBER() = 15401) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'Unable to create login for ' + @loginName + ' as the Windows user does not exist.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'The Windows user will be created when Thunder is installed.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		ELSE >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			EXECUTE usp_GetErrorInfo >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo			PRINT 'Please contact XOS Support.' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END CATCH >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.		 
echo	IF EXISTS (select loginname from master.dbo.syslogins where name = @loginName) >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		SET @sql = 'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @loginName + ']' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo		EXEC sp_executesql @sql >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo PRINT '@serverproperty_servername = ' + @serverproperty_servername  >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo PRINT '@servername = ' + @servername >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo IF @serverproperty_servername != @servername >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	PRINT 'updating to ' + @serverproperty_servername >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	-- Drop the server with incorrect name >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	EXEC sp_dropserver @server=@servername >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo.
echo	-- Add the correct server as a local server >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	EXEC sp_addserver @server=@serverproperty_servername, @local='local' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo END >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo ELSE >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo BEGIN >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo	PRINT 'no update necessary' >> "C:\Change and check SQL ServerName ALLinOne.sql"
echo END >> "C:\Change and check SQL ServerName ALLinOne.sql"


timeout /t 2 /nobreak > NUL 

Echo.
Echo.
Echo.

Echo      ....CHECKING STATUS OF SQL SERVICES....

Echo.
Echo.
Echo.

  timeout /t 2 /nobreak > NUL 
Echo.
Echo.
Echo.

net start "MSSQL$COACHINGSOLNS" /y 
Echo.
Echo.
Echo.

  timeout /t 2 /nobreak > NUL 
Echo.
Echo.
Echo.

Echo      ....RUNNING THE SQL CHANGE NAME SCRIPT....

  timeout /t 2 /nobreak > NUL 

Echo.
Echo.
Echo.


sqlcmd -S localhost\coachingsolns  -U sa -P xostech  -i "C:\Change and check SQL ServerName ALLinOne.sql"

  timeout /t 2 /nobreak > NUL 

Echo.
Echo.
Echo.

Echo      ....RESTARTING SQL AND XOS SERVICES....
Echo.
Echo.
Echo.

net stop "MSSQL$COACHINGSOLNS" /y
Echo.
Echo.
Echo.
   
net start "MSSQL$COACHINGSOLNS"
Echo.
Echo.
Echo.

net start "xoslocalassetmanagementservice"
Echo.
Echo.
Echo.

timeout /t 2 /nobreak > NUL 

Echo      .... REMOVING CHANGE NAME SCRIPT FROM C:\ ....
Echo.
Echo.
Echo.

Del "C:\Change and check SQL ServerName ALLinOne.sql"

timeout /t 2 /nobreak > NUL 

Echo.
Echo.
Echo.
Echo      ....PROCESS IS COMPLETE....
Echo.
Echo.
Echo.
Echo.
pause