Moving SQL Agent Log file "SQLAGENT.OUT" to a different location

In one of my previous posts "Undocumented stored procedure for retrieving SQL Agent properties", I had explained how to retrieve the SQL Agent Properties.

In this post I will explain how to change the location of the SQL Agent Log file "SQLAGENT.OUT".

To find the current location of SQLAGENT.OUT file, execute the below SP and look at the value of the column "errorlog_file". This is location where SQLAGENT.OUT file is located.

?

1 2	EXEC msdb..sp_get_sqlagent_properties GO

Output:

Now, to change the location of SQLAGENT.OUT file, run the below command and re-start the SQL Server Agent Service and you are done.

?

1 2	EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'<new path>\SQLAGENT.OUT' GO

T-SQL Query to change the datatype of multiple columns of single or multiple tables

There might be a situation where a person designed a database with a particular datatype for many tables and now you want to change the datatype to a different one for all those columns in a particular table or multiple tables due to various reasons.
Changing the datatype for a single table or five tables or 10 tables is a easy task, but when the tables list is in hundreds how easy is it do in the traditional way?
Below stored procedure gives you the flexibility of changing the datatype of multiple columns in a single or multiple tables at one go.

Things to note before running the scripts

1. Backup your database 
2. These scripts are provided AS IS without warranty of any kind.

Script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

CREATE PROC usp_ChangeColumnDatatype (@currentDataType nvarchar(25),    @DataTypeToSet nvarchar(50),    @ScanTables nvarchar(100),    @PrintCommandsOnly bit )   AS SET NOCOUNT ON DECLARE @ScanTables_Local nvarchar(100) SET @ScanTables_Local = '''' + REPLACE(REPLACE(@ScanTables,',',''','''),' ','') + '''' IF @ScanTables = 'All' BEGIN CREATE TABLE #Temp (CommandsToExecute nvarchar(max)) INSERT INTO #temp SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) +     ' ALTER COLUMN ' + c.name + ' ' + @DataTypeToSet +     CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END AS CommandsToExecute FROM sys.objects o INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type='u' and t.name = @currentDataType END   IF @ScanTables <> 'All' BEGIN CREATE TABLE #Temp_SpecificTables (CommandsToExecute nvarchar(max))   DECLARE @Cmd nvarchar(max) SET @Cmd = 'INSERT INTO #Temp_SpecificTables  SELECT ''ALTER TABLE '' + OBJECT_NAME(o.object_id) +     '' ALTER COLUMN '' + c.name + ''' + @DataTypeToSet + ''' +     CASE WHEN c.is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END AS CommandsToExecute FROM sys.objects o INNER JOIN sys.columns c ON o.object_id=c.object_id INNER JOIN sys.types t ON c.system_type_id=t.system_type_id WHERE o.type=''u'' and t.name = '''+@currentDataType+''' and OBJECT_NAME(o.object_id) in ('+ @ScanTables_Local + ')' --PRINT @cmd EXECUTE (@cmd) END if @PrintCommandsOnly = 'True' and @ScanTables = 'All' BEGIN SELECT * FROM #Temp DROP TABLE #Temp END   if @PrintCommandsOnly = 'False' and @ScanTables = 'All' BEGIN --SELECT * FROM #Temp PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp) <> 0 BEGIN DECLARE @varTemp nvarchar(max) SELECT @varTemp = CommandsToExecute FROM #Temp EXECUTE (@varTemp) DELETE FROM #temp WHERE CommandsToExecute = @varTemp END DROP TABLE #Temp PRINT 'Changing of the datatypes of table(s) '+ @ScanTables +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar) END   if @PrintCommandsOnly = 'True' and @ScanTables <> 'All' BEGIN SELECT * FROM #Temp_SpecificTables DROP TABLE #Temp_SpecificTables END   if @PrintCommandsOnly = 'False' and @ScanTables <> 'All' BEGIN --SELECT * FROM #Temp_SpecificTables PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' started at ' + CAST(GETDATE() AS varchar) WHILE (SELECT COUNT(*) FROM #Temp_SpecificTables) <> 0 BEGIN DECLARE @varTemp_SpecificTables nvarchar(max) SELECT @varTemp_SpecificTables = CommandsToExecute FROM #Temp_SpecificTables EXECUTE (@varTemp_SpecificTables) DELETE FROM #Temp_SpecificTables WHERE CommandsToExecute = @varTemp_SpecificTables END DROP TABLE #Temp_SpecificTables PRINT 'Changing of the datatypes of table(s) '+ @ScanTables_Local +' from ' + @currentDataType + ' to '+ @DataTypeToSet + ' ended at ' + CAST(GETDATE() AS varchar) END SET NOCOUNT OFF GO

Usage:

?

1 2 3 4

EXEC usp_ChangeColumnDatatype @currentDataType = 'nvarchar',          @DataTypeToSet = 'varchar(50)',          @ScanTables = 'Table_1,Table_2', --Table1, Table2,Table3 or ALL          @PrintCommandsOnly = 'FALSE' -- TRUE - Will print the commands or FALSE - Will execute the commands.