SSRS Error : The user or group name 'Group Name' is not recognized.

Scenario:

Today I got the request to provide permission to a AD Group on one of the SSRS report. When tried to provide browse or any other permissions such as Content Manager, My Reports, Publisher or Report Builder, got below error.

The user or group name 'Group Name' is not recognized.

Solution:

You need to go to Active Directory user and Groups and then check the properties of your Group. 
Make sure Group Scope is Global or Universal and Group Type is security. I had it distribution and that is why SSRS was not able to recognized the AD group.
Once I changed the Group Type to Security, SSRS was able to recognize the AD group and was able to provide required browse permission.


SSRS Error Solution: The user or group name '' is not recognized.

How to Setup Peer To Peer Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA. You are asked to setup Peer to Peer replication in SQL Server 2016. What steps you will follow to perform Peer to Peer Replication in SQL Server?


Solution:

As per Microsoft here is the definition
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

In this video you will learn below items
How to setup Peer to Peer Replication in SQL Server 2016

In this video you will learn
1) Identifying databases to replicate
2) Synchronizing replication databases on all nodes
3) If changes happens after sync , taking backup again for the setup
4) All nodes are distributor or separate distributor
5) What is publisher, subscriber and distributor in Peer to Peer replication
6) What are steps to setup Peer to Peer Replication in SQL server 2016


Video Demo: Step by Step setup of Peer to Peer Replication in SQL Server 2016


How to Create Snapshot Replication in SQL Server - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA. You are asked to perform Snapshot Replication in SQL Server 2016. What steps you will follow to perform Snapshot Replication setup?

Solution:

Video demo explains all the important points those are required to perform Snapshot Replication in SQL Server 2016. Demo covers


  • What is Snapshot Replication in SQL Server
  • When you use Snapshot Replication
  • What are the reasons we need to use Snapshot Replication
  • How Snapshot Replication works
  • What are Snapshot Agent, Distribution Agent and Merge Agent
  • Go through Security Best Practices for Snapshot Replicaiton Setup
  • How to use Replication Monitor in SQL Server
  • How to use Replication Monitor in SQL Server for Snapshot Replication


How to setup Snapshot Replication in SQL Server 2016

How to create Merge Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to create Merge Replication in SQL Server 2016?

Solution:

In this video demo, you will learn how to create Merge Replication in SQL Server 2016.


1. What is Merge Replication in SQL Server 2016?
2. How to setup Merge Replication in SQL Server 2016?
3. How to setup Merge Replication using separate Distributor?
4. What are security best practices of Merge Replication?
5. How to monitor Merge Replication using Replication Monitor?


How to setup Merge Replication in SQL Server 2016

How to setup Transactional Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to setup Transactional Replication in SQL Server 2016?

Solution:

In this demo, you will learn how to setup Transactional Replication in SQL Server 2016. Demo covers below topics

1. What is Transactional Replication in SQL Server 2016?
2. How to setup Transactional Replication in SQL Server 2016 step by step?
3. What are the Security Best Practices of Replication?
4. How to Setup Transactional Replication in SQL Server 2016 using different Distributor?
5. How to monitor Transactional Replication using Replication Monitor?
6. How to Troubleshoot replication issues using Replication Monitor?
7. Resolution to the error “Server is not enabled as a publisher on the Distributor”



How to setup Transactional Replication in SQL Server 2016

Understanding Replication in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario : 

What is Replication in SQL Server?

Solution :

This video demo can be used to Understanding Replication in SQL Server 2016. It explains all the components for Replication step by step.

Demo covers below points
1. What are replication components in SQL Server?
2. What is Publisher?
3. What is Distributor?
4. What is Subscriber?
5. Overview of Replication Agents
Snapshot Agent
Distribution Agent
Merge Agent
Log Reader Agent
Queue Reader Agent
6. Replication Jobs on SQL Server 2016 Agent
7. How to troubleshoot Replication using Replication Agents Jobs?


What is Replication in SQL Server, What are important components of Replication in SQL Server.

How to find Number of Processors, Cores and Logical Processors on Machine

Scenario:
You need to find the Number of CPUs, Cores and Logical Processors on a computer, how would you do that?

Solution:

There are multiple ways you can find Number of CPUs, Number of Cores and Number of Logical Processors.

1) Go to Start and then in Run type msinfo32 and hit Enter.

You will see detail information about System. Look for Processor and you will be able to see information like below depending upon your computer configuration.


2) You can also go to Command Prompt and then paste below statement, you will get same information.


WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors



How to Install SQL Server 2016 in Cluster Mode - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA, you are asked to install SQL Server 2016 in cluster mode, what steps you will take to perform the installation?

Solution:

In this video demo, you will learn all the steps you need to take to install SQL Server 2016 in cluster mode.

Video demo covers
  1. What are pre-requisites of Installing SQL Server 2016 in cluster mode? 
  2. How to Prepare Cluster nodes to install SQL Server 2016 in cluster mode?
  3. How to check SQL Server 2016 pre-requisites
  4. What is shared storage and how to configure Shared Storage 
  5. Best practices to configure Shared Storage in SQL Server 2016
  6. How to Add Shared Storage to the windows 2012 Server?
  7. Best practices of installing SQL Server 2016 in cluster mode?


Part 1 - How to install SQL Server 2016 in Cluster Mode


Part 2- How to install SQL Server 2016 in Cluster Mode

How to setup Distributed Availability Group in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you are asked to setup Distributed Availability Group in SQL Server 2016, What steps would you follow?

Solution:

The video demo, will explain all the steps you need to take to configure Distributed Availability Group. Demo covers below items

  • What is Distributed Availability Group in SQL Server 2016?
  • What are pre-requisites of Distributed Availability Group in SQL Server 2016?
  • How to create availability groups on different clusters to use in Distributed AG?
  • How to create Availability Group with Automatic seeding
  • How to create Listener in Availability Group
  • How to setup Distributed Availability Group using two different listeners?
  • How to Join Secondary cluster in Distributed Availability Group?
  • How to check status of Distributed Availability Group
  • Troubleshooting Distributed Availability Group


Part 1



Part 2



Part 3

How to Add Contained Databases in SQL Server 2016 Availability Group - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you are asked to add a Contained Database in AlwaysOn Availability Group. What steps you will follow?

Solution:

In this video demo, you will go through all the steps you need to add a Contained Database to AlwaysOn Availability Group.


Items covered

  • How to create Contained Databases in SQL Server 2016?
  • How to add Contained Database into Availability Group?
  • How to Connect to Contained Database using Availability Listener?
  • How to Failover Contained Database Availability Group?

How to Create Availability Group with Automatic Seeding in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to create Availability Group with Automatic Seeding in SQL Server 2016?

Solution:
The video demo will cover below items to perform Automatic Seeding in SQL Server 2016 for AlwaysOn Availability Group.


  1. What is Automatic Seeding?
  2. What are the pre-requisites of Availability Group with Automatic Seeding?
  3. How to prepare database to be added in Availability Group? 
  4. How to create Availability Group with Automatic Seeding using T-SQL? 
  5. What are the advantages of Availability Group with Automatic seeding?
  6. What are the limitation of Availability Group with Automatic Seeding? 
  7. How to provide Availability Group Create any Database permissions? 


How to find duplicate records by using Group by and Having clause in SQL Server - SQL Server / TSQL Tutorial Part 132

Scenario:

You are working as SQL Server developer. You need to write the query for dbo.Customer table that should return the duplicate records and count of duplicate record.


Solution:

You can use Group by and Count aggregate function to find all the duplicate records in table and count.

Let's say that we have dbo.Customer table with below definition and data. We would like to find the record if it is duplicate by columns FName,LName and CountryShortName.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'Raza','M','Pk',Null)


Let's write our query, we have include all the columns in select list with count(*) and then group by the same columns. At the end we will be using Having clause to filter the record which are duplicate.


SELECT fname, 
       lname, 
       countryshortname, 
       Count(*) AS RecordCount 
FROM   dbo.customer 
GROUP  BY fname, 
          lname, 
          countryshortname 
HAVING Count(*) > 1

As from data we can see that the only record which is duplicate by FName,LName and Country short name is  'Raza','M','PK'and have total two records as returned by our query.

How to find duplicate records by using Group by and Having Clause in SQL Server



How to apply Having Clause with Group by in Select Query - SQL Server / TSQL Tutorial Part 131

Scenario:

You are working as SQL Server developer, you are asked to write a query that should return Total SaleAmount from dbo.Customer table by CountryShortName. You are also asked to filter the records where Total SaleAmount by CountryShortName is greater than 10.


Solution:

From above scenario, you have noticed couple of things. First we need to sum the SaleAmount. Second we need to group by the SaleAmount by CountryShortName. The Last thing we need to filter those records after calculating the sum and only returns where total SaleAmount is greater than 10. We can not use Where clause here as where will filter the records before Group by. SQL Server provide us Having clause that we can use to filter the records after group by.

Let's create dbo.Customer Table with below records and then write our query by using Group by and Having clause.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)



Let's write our query by using Group by and then use Having to filter the record where Sum(SaleAmount) is greater than 10.

SELECT countryshortname, 
       Sum(saleamount) SaleAmountByCountry 
FROM   dbo.customer 
GROUP  BY countryshortname 
HAVING Sum(saleamount) > 10 

How to filter aggregated data by using Having clause  in SQL 



Video Demo : How to use Having Clause in SQL Select Statement






How to Configure Availability Group in SQL Server 2016 - SQL Server 2016 DBA Tutorial

Scenario:

How to Configure Availability Group in SQL Server 2016?

Solution :

The video demo will walk you through all the steps required to create / configure AlwaysOn availability Group in SQL Server 2016. Below items are covered.


  • What are requirements and best practices of configuring Availability group in SQL Server 2016?
  • How to prepare Windows node to be added as part of AG 2016 Cluster
  • How to Create Windows Cluster step by step 
  • What are the best practices of creating Availability Group Windows Cluster
  • How to Configure Availability Group Role in SQL Server 2016
  • How to resolve error like 
  • User/Cluster doesn’t have permission to create computer object in Active Directory or DNS
  • Node 1 cannot communicate with Node2 
  • Joining Database takes forever and throw permission error
  • Missing End Point in Availability Group properties

Part 1:

Part 2:


Part 3:


How to Install and Configure SQL Server 2016 Master Data Services - SQL Server 2016 DBA Tutorial

Scenario:

How to install Master Data Services in SQL Server 2016?

Solution : 

The video demo shows how to install SQL Server Master Data Services Step by Step in SQL Server 2016.


  • How to Install and Configure SQL Server 2016 Master Data Services 
  • How to install SQL Server 2016 Master Data Services
  • What are SQL Server 2016 Master Data Services
  • What are pre-requisites of SQL Server 2016 Master Data Services and how to install them step by step
  • MDS Database Configuration
  • MDS 2016 Web Configuration
  • Best practices of Master Data Services installation and Configuration
  • How to provide permission to Master Data Services
  • How to connect to MDS 2016 Web interface


How to Install SQL Server 2016 Management Studio - SQL Server 2016 DBA Tutorial

How to Install SQL Server 2016 Management Studio
1.     How to install SQL Server 2016 Management Studio using SQL Server 2016 Media
2.     How to direct download SSMS 2016
3.     What pre-requisites of SQL Server gets installed during installation


Video Demo : How to Install SQL Server Management Studio( SSMS) 




Understand Group by Clause in SQL Server - SQL Server / TSQL Tutorial Part 130

Scenario: 

You are working as SQL Server Developer. You have a dbo.Customer table which has CountryShortName and SaleAmount. You are asked to write a query that should return Sum of SaleAmount , count of records by CountryShortName.


Solution:

Group by clause is often used with aggregate functions such as Sum, Avg,Count,Max,Min to group the result set by column/s.

Let's create our sample table with some data and write our query with Group by to answer our question.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)


Let's write our query by using Sum, Count and Group by Clause

SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname 
FROM   dbo.customer 
GROUP  BY countryshortname

How to use Group by Clause in SQL Server


You can also use multiple columns in group by clause. think about if our table would have states and you would like to group by CountryShortName and State, You would simple include State in query as shown below.


SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname,
       [State]
FROM   dbo.customer 
GROUP  BY countryshortname,[State]





Video Demo: What is Group by Clause in SQL Server


How to Get Max and Min values from a Table by using Aggregate Function - SQL Server / TSQL Tutorial Part 129

Scenario:

Let's say you are working as SQL Server Developer, you have dbo.Customer table with SaleAmount. You are asked to write a query that should return Max SaleAmount and Min SaleAmount from the dbo.Customer table.


Solution:

You can use Max and Min aggregate functions in SQL Server to find the Maximum and Minimum values.

Let's create dbo.Customer Table with sample data so we can use Max and Min functions.


Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)

Let's run the below query with Max and Min functions to finder the answer of our question.

SELECT Max(saleamount) AS MaxSaleAmount, 
       Min(saleamount) AS MinSaleAmount 
FROM   dbo.customer 

How to use Max and Min function in SQL Server




How to use Sum, Avg and Count in Select Statement - SQL Server / TSQL Tutorial Part 128

Scenario:

Let's say that you have dbo.Customer table with SaleAmount column and you are asked to write a query that should return you sum of SaleAmount, Average of SaleAmount and Count of all the records.

Solution:

You can use Aggregate functions such as Sum, Avg and count in TSQL to find the answer of your question.

Let's create dbo.Customer Table with sample data so we can use Sum, Avg and Count aggregate functions.



Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int)
GO
--Insert Rows in dbo.Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null)


Let's run our query to find sum of SaleAmount, Avg of SaleAmount and Count of records.

SELECT Sum(saleamount) AS TotalSale, 
       Avg(saleamount) AS AvgSale, 
       Count(*)        AS SaleRecordCount 
FROM   dbo.customer 


How to use Sum, Avg and Count Aggregate Functions in SQL Server




How to Install and Configure SQL Server 2016 Analysis Services - SQL Server 2016 DBA Tutorial

Scenario:

You are working as SQL Server DBA and you need to install SQL Server 2016 Analysis Services. How would you perform SSAS 2016 installation.


The below video demo will show you all the steps you need to perform SSAS installation.
1. SSAS 2016 installation step by step
2. What are pre-requisites of SQL Server 2016 Analysis Services
3. How to add SSAS 2016 to an existing SQL Server
4. When to choose Multidimensional Data and Mining mode
5. When to Choose Tabular Mode
6. When to Choose PowerPivot Mode
7. SSAS 2016 Installation best practices
8. SSAS 2016 Configuration best practices
9. How to connect to Analysis Services using SQL Server Management Studio


Video Demo : Install SSAS 2016 Installation


How to Install and Configure SQL Server Reporting Services 2016 SSRS - SQL Server 2016 Tutorial

Scenario: 

How to install and Configure SQL Server 2016 Enterprise Reporting Services?

In this video you will learn below items.
1. How to install SSRS 2016 step by step
2. What are the pre-requisites of SQL Server 2016 Reporting Services
3. SQL Server 2016 Reporting Services installation best practices
4. SQL Server 2016 Reporting Services Configuration best practices
5. Brief introduction of Scale-out deployment of Reporting services
6. Brief introduction of new features such as Power BI Integration


Video Demo : How to Install SSRS 2016 Step by Step from scratch

MariaDB : How to change Root Password for MariaDB

Scenario:

You are working as MariaDB / MySQL DBA and you need you change the password for root. How would you do that?

Solution:

You can use mysqladmin command to change the password for root user. use below statement to change the password

shell > sudo mysqladmin -u root -p password mynewpassword

Once you will hit enter, you will be asked to provide old password for root. Provide the password for root and hit Enter. 

You are all set to login to MariaDB by using new root password. Use below command to login to MariaDB


shell> mysql -u root -p
provide new password, in my case it was mynewpassword. 

O


How to Install SQL Server 2016 Enterprise Step by Step - SQL Server 2016 Tutorial

Scenario:

How to install SQL Server 2016 Enterprise Step by Step?

Solution:

In this video, we will learn how to install SQL Server 2016 Enterprise step by step. Following items are covered in this video.
  • What are the pre-requisites of SQL Server 2016 Enterprise
  • Brief introduction of new features in SQL server Enterprise 2016 such as Polybase
  • How to install JRE as a pre-requisites of SQL Server 2016 Enterprise Polybase new feature
  • New Tempdb configuration in SQL Server 2016 Enterprise
  • What are the best practices to install SQL server 2016

Video Tutorial : How to Install SQL Server Enterprise in real time 

MariaDB - How to Restore .sql file to Database in MariaDB or MySQL

Scenario: 

In our last post we created the logical backups of a database by using mysqldump. Now we need to restore that .SQL file to database.

Solution:

Login to MariaDB or MySQL database. Create an empty Database by using "create database DatabaseName" statement. In my case I create database ABC.

Fig 1: Create database in MariaDB or MySQL

Once you exited form MariaDB or MYSQL, on Shell provide below statement to restore .sql file to database. In my case I used empty database ABC.

shell> mysql -u root -p DatabaseName< sqlfile.sql




MariaDB - mysqldump: Got error: 1045: "Access denied for user 'username'@'localhost' (using password: NO)" when trying to connect

Scenario:

Today when was trying to take logical backup of MariaDB database by using below 
Shell> mysqldump DatabaseName > DatabaseName.sql

got below error

Shell> mysqldump MariaDB>MariaDB1.sql
error: Found option without preceding group in config file: /etc/my.cnf.d/server.cnf at line: 6
mysqldump: Got error: 1045: "Access denied for user 'username'@'localhost' (using password: NO)" when trying to connect

Solution:

Checked the server.cnf file but could not find anything missing. Found out that if you will use root with password that will work. Used the below statement and it worked.


Shell> mysqldump -u root -p DatabaseName > BackupFileName.sql

How to get list of all the tables with all the columns from SQL Server Databases on SQL Server Instance

Scenario:

Was working on a project and needed to get list of all the tables from all the databases with columns. Write below script. The script uses cursor to loop through all the databases to get the list of tables with columns. You can filter the databases on which you would like to run the query. I have ignored the system databases such as master, model, tempdb and msdb. 


USE master
GO
--Declare Variables
DECLARE @DatabaseName AS VARCHAR(500)
--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results
CREATE TABLE #Results (
    ServerName VARCHAR(128)
    ,DatabaseName VARCHAR(128)
    ,SchemaName VARCHAR(128)
    ,TableName VARCHAR(128)
    ,ColumnName VARCHAR(128)
     ,Data_Type VARCHAR(128)
   ,Is_Nullable VARCHAR(25)
   ,Character_Maximum_Length VARCHAR(10)

    )
DECLARE CUR CURSOR
FOR
--Filter the Database for which you would like to get all the tables with columns
SELECT '[' + NAME + ']' AS DBName
FROM sys.databases
WHERE NAME NOT IN (
         'master'
        ,'tempdb'
        ,'model'
        ,'msdb'
    
        )
OPEN Cur
FETCH NEXT
FROM Cur
INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Build dynamic sql for each database 
    DECLARE @SQL VARCHAR(MAX) = NULL
    SET @SQL = 'Insert into #Results
      Select
      @@ServerName,
   Table_Catalog as DatabaseName,
   Table_Schema AS TableSchema,
   Table_Name AS TableName,
   Column_Name AS ColumnName
    ,Data_Type
   ,Is_Nullable
   ,Character_Maximum_Length
  From' + @DatabaseName + '.information_Schema.Columns 
  where Table_Name in (Select Table_Name from ' + @DatabaseName + '.information_schema.Tables
  where table_type=''BASE TABLE'')'
    EXEC (@SQL)
    PRINT @SQL
    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END
CLOSE Cur
DEALLOCATE Cur

SELECT *
FROM #Results
order by DatabaseName,TableName

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 namespace [0x8004100e]

Scenario: SQL Server 2016 Installed successfully as per Wizard but can't find SQL Server Service and can't connect to SQL Server from SSMS.


Solution:

Today I installed SQL Server 2016 on my 64 bit machine. 


The file for installation that I used was aslso 64Bit. After installation I tried to connect to SQL Server Engine by using SSMS but could not find.

Next thing I did , I checked tried to open SQL Server configuration manager but got this error
"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 namespace [0x8004100e]"

Then I went to Control Panel to take a look if SQL Server is installed on machine.

I found out the Size and Version was blank. Looks like the SQL server was not installed correctly. But Wizard should have given an error but it completed successfully.

When was doing research, people are talking about the file can be damaged if you have uninstalled and instance of SQL Server, that I have not done it. I did the fresh installation and it was first time.

"This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder."

Anyways to fix this, you need to run below command on cmd after making sure you have below file on below path.

The Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.



Open cmd by going to Run in windows. I am using SQL Server 2016, so my number is 130 in below statement, your will be depending upon SQL version.
and paste below command.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"


Now you can open the SQL Server Configuration Manager without any problem but you will not the SQL server Service. You have to reinstall SQL Server.

I restarted the machine and then Reinstalled SQL Server. The installation completed successfully and was able to use SQL Server without any problem.

How to check if User is Enabled or Disabled in all the databases in SQL Sever

Scenario: How to check if the database user is enabled or disabled in multiple or all the databases in SQL Server

You have created a login and then user in multiple databases by using that login. You need to take a look if the user is enable or disable in those databases. 

If the user is disabled in a database you will see a red arrow pointing downwards. I have disabled Aamir user as can be seen below by using 

REVOKE CONNECT FROM Aamir


If you want to enable the user, you can use below statement

GRANT CONNECT TO UserName

But our goal is find out in all databases or in multiple database if the user is enabled or disabled. Also if the query does not return the Database name, that means the user does not exits in that database.


--Provide the user name that you would like to check if Enabled or Disabled in Databases
    DECLARE @UserName VARCHAR(128) 
    SET @UserName='Aamir' -- I have provided Aamir as my User Name
    

  IF OBJECT_ID('tempdb..##Temp_Table') IS NOT NULL
        DROP TABLE ##Temp_Table

    CREATE TABLE ##Temp_Table (
    ServerName VARCHAR(1000),
        DatabaseName VARCHAR(500)
        ,UserName VARCHAR(500)
        ,IsEnabled INT
        )
   
    DECLARE @SQLStatement VARCHAR(MAX)
    SET @SQLStatement='Insert into ##Temp_Table
Select @@ServerName AS ServerName,DB_Name() AS DatabaseName,name, hasdbaccess 
FROM sys.sysusers WHERE name='''''+@UserName+''''''

Print @SQLStatement
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run Check If user Enabled or Disabled in Database
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@SQLStatement+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

    Select * From ##Temp_Table





How to generate script to REFRESH all views in a SQL Server Database

Scenario: How to generate script to REFRESH all views in a SQL Server Database

Let's say you have made changes to data types of few of the tables and you had views created on those tables. You need to refresh the views definition after that. The below script can be used to generate script to refresh views.

If you have single view, you can use sp_refreshview 'ViewName' to refresh view definition.

To generate the script for all views in a database, you can run below statement.


SELECT 'sp_refreshview  ''' 
       + Schema_name(schema_id) + '.' + NAME + '''' 
       + Char(13) + Char(10) + ' GO' AS RefreshViewQuery 
FROM   sys.views 



Click on icon "Results to Text" or press Ctrl+T and then copy the script to run for all views. You can always filter the list in your above select query if you would only want to refresh the views related to one schema or as per view names etc.


How to check when the view definition was refreshed in SQL Server

Scenario: How to check when last time view definition was refreshed or changed

We know that if the data type changes for columns for objects which are used in the view, we have to refresh the view definition by using sp_refreshview to update meta data for columns used in the view.

Sometime we need to know what was the last time the view definition was altered or refreshed.
You can use system view to get that information.


Select * from sys.views


Notice that if the view is created and never been altered or definition never been refreshed then created_date=modify_date.

Once you will refresh view definition or alter the view, then modify date will be the date time when the view as altered or definition was refreshed.

How to drop Stored Procedure from All or Multiple databases in SQL Server

Scenario: How to drop Stored Procedure from multiple or all Databases in SQL Server

In my last post, I wrote How to create a Stored Procedure in all the databases or multiple databases in SQL Server. You can Click here to see that post.

In this post we are going to write the script how to drop the Stored Procedure in multiple or all SQL Server databases. You can filter the list of Databases on which you like to run the script by changing the where clause "Select name from sys.databases where *********"

I have cursor to loop through the databases. You need to provide the Schema Name and Stored Procedure that you would like to drop from multiple or all the databases in SQL Server.


--Provide the Stored Procedure Name and Schema Name that you would like to drop from multiple or all databases
    Declare @SP_Schema VARCHAR(128)
    DECLARE @SP_Name VARCHAR(128)

    --Provide Schema Name and Stored Procedure you would like to drop 
    SET @SP_Schema='dbo'
    SET @SP_Name='SP_Test'


    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (Select * from sys.objects
where name='''''+@SP_Name+'''''
and schema_id=schema_id('''''+@SP_Schema+''''')  
and type=''''P''''
and type_desc=''''SQL_STORED_PROCEDURE''''))
BEGIN
Drop Procedure '+@SP_Schema+'.'+@SP_Name+'
END'

Print @DDL
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to drop Stored Procedure
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to Create Same Stored Procedure on All Databases in SQL Server

Scenario:How to Create Same Stored Procedure on All Databases or multiple Databases in SQL Server 

We often face this situation where we need to create a same Stored Procedure in multiple databases. This script can be really usefully when you are working as SQL Server DBA and you have to deploy same Stored Procedure to all databases or multiple databases Or you are working as developer and your team asked you to prepare script which will create the Stored Procedure in all databases in SQL Server.

I used cursor in below script to loop through the databases. You can always change your Where clause to get the list of databases on which you would like to create Stored Procedure. 

It is always great idea to test your script in DEV, QA and UAT environment before deploying/ running on Production environment.

--Provide the DDL Statment that you would like to run for Create Stored Procedure in Each Databsae in SQL Server
-- Notice that if you have string in your SP, then you have to have 4 single quote around it.
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL=    
    'Create Procedure dbo.SP_Test
    AS
    BEGIN
    --My all statements in SP
    Select 1
    Print ''''Test''''
    END'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(128);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to create Stored Procedure
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR

How to drop View from Multiple or All Databases in SQL Server

Scenario: How to drop View from multiple or all SQL Server Databases

You are working as SQL Server DBA or SQL Server developer, you need to come up with scripts to drop the view from many databases in one of your SQL Server Instance. 

Here is the script that can help to drop the view from multiple databases. I have used the cursor to loop through the databases. You can always modify your select query to choose required databases on which you would like to run the script. You have to change the schema name and view name and in drop view statement, the name of view.

--Provide the DDL Statment that you would like to run to drop the View if exists in Database
  --Change the schema and View name in script as per your requirement
    DECLARE @DDL VARCHAR(MAX)
    SET @DDL='IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = ''''dbo'''' -- change to your Schema
                 AND  TABLE_NAME = ''''MyTestView'''' --Change to your View Name
                 and Table_Type=''''View''''))
BEGIN
Drop view dbo.MyTestview
END'
    DECLARE @DatabaseName AS VARCHAR(500)
    
--DECLARE CURSOR
    DECLARE CUR CURSOR
    FOR
    --Choose the DBs on which you would like to run the script
    SELECT NAME
    FROM sys.databases
    WHERE database_id > 4

--OPEN CURSOR
    OPEN CUR

--NEXT NEXT RECORD 
    FETCH NEXT
    FROM CUR
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @DBName AS NVARCHAR(500);

        SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

        --USE Dynamic SQL To Change DB name and run DDL statement to drop View
        EXEC (
                N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'
                );

        FETCH NEXT
        FROM CUR
        INTO @DatabaseName
    END

    CLOSE CUR

    DEALLOCATE CUR