Cannot Connect to WMI provider - Error while trying to open SQL Server 2008 Configuration Manager

When you try to open SQL Server Configuration Manager, you might get an error which states
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]"

During the setup sometimes, some .mof files don't get installed and registered properly and this causes the above error to pop up.
To fix this issue,

1. Go to the path "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\" using command prompt.
2. And run the following command mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Once the commands execute successfully, you will be able to open the SQL Server configuration Manager.

Startup Parameters - A new tab in Denali's SQL Server Configuration manager

We all know that Denali was launched with many new things built within. In those wide range of new enhancements, a separate tab for Startup parameters is one among them.
To check this out,

1. Go to Denali's "SQL Server Configuration Manager"
2. Right-Click on a SQL Server Service and Choose "Properties"
3. Now, in the properties page you can find a new tab for "Startup Parameters"

The Older versions of SQL Server Configuration Manager used to show the "Startup Parameters" as part of "Advanced" Tab.

Different ways to check your SQL Server(s) Authentication mode

Checking the Authentication mode using T-SQL:

1. Using "xp_LoginConfig" extended Stored Procedure
   

   EXEC Master.dbo.xp_LoginConfig 'login mode'
   

   
   
2. Using "SERVERPROPERTY" Function
   

   SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
   WHEN 1 THEN 'Windows Authentication mode'   
   WHEN 0 THEN 'SQL Server and Windows Authentication mode'   
   END as [Authentication Mode]  
   
   

3. Using Registry
   

   DECLARE @Mode INT  
   EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer',   
   N'LoginMode', @Mode OUTPUT  
   
   SELECT CASE @Mode    
   WHEN 1 THEN 'Windows Authentication mode'
   WHEN 2 THEN 'SQL Server and Windows Authentication mode'
   ELSE 'Not known'
   END as [Authentication Mode]  
   

Checking the Authentication mode using SSMS:

To check the Authentication mode using SSMS,

1. Right-Click on the Server
2. Choose "Properties"
3. Navigate to "Security" Page
4. Check "Server Authentication" Section

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.

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.

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:

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:

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.