Category Archives: SQL Server

SQL Script to find SQL Server users who have been disabled or terminated in AD – Active Directory

by

Following is the SQL Script to find SQL Server users who have been disabled or terminated in AD – Active Directory:

declare @user sysname
declare @domain varchar(100)

set @domain = ‘DomainName’

declare recscan cursor for
select name from sys.server_principals
where type = ‘U’ and name like @domain+’%’

open recscan
fetch next from recscan into @user

while @@fetch_status = 0
begin
begin try
exec xp_logininfo @user
end try
begin catch
–Error on xproc because login doesn’t exist
print ‘drop login ‘+convert(varchar,@user)
end catch

     fetch next from recscan into @user

end

close recscan
deallocate recscan

Reference: [SOLVED] Identify the SQL DB users terminated or disabled at AD level – SQL Server Forum (spiceworks.com) 

In SQL Management Studio how to Edit multiple lines of code at the same time or add character to multiple lines of text

by

I use to use combination of Excel and notepad to prefix or suffix character(/s) to a vertical block of text. Last week I was shown a quick method in SQL management studio itself. Its called ‘Vertical Block Select mode’ from SSMS.

How to:

Keyboard Shortcut – ALT + SHIFT + Arrow Keys
Mouse – ALT + Left-Click + Drag

CTE Common Table Expression What and Why

by

CTE stands for common table expression.
A CTE is a temporary named result set that is available temporarily in the execution scope of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE.

We create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement.

If we use a CTE, we don’t need to create and drop a table. We can simply reference the temporary result set created by the WITH query, as shown below:

— Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
— Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
— Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;

We can use multiple CTE definitions in a single query
The following example shows how to define more than one CTE in a single query. Notice that a comma is used to separate the CTE query definitions.

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)
AS
— Define the first CTE query.
(
SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate)

)
, — Use a comma to separate multiple CTE definitions.

— Define the second CTE query, which returns sales quota data by year for each sales person.
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)
AS
(
SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate)
)

— Define the outer query by referencing columns from both CTEs.
SELECT SalesPersonID
, SalesYear
, FORMAT(TotalSales,’C’,’en-us’) AS TotalSales
, SalesQuotaYear
, FORMAT (SalesQuota,’C’,’en-us’) AS SalesQuota
, FORMAT (TotalSales -SalesQuota, ‘C’,’en-us’) AS Amt_Above_or_Below_Quota
FROM Sales_CTE
JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID
AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear
ORDER BY SalesPersonID, SalesYear;

Reference

Database Normalization Rules 1FN 2FN 3FN

by

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. 
Normalization rules divides larger tables into smaller tables and links them using relationships. 
The purpose of Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

1NF (First Normal Form) Rules
Each table cell should contain a single value.
Each record needs to be unique.


2NF (Second Normal Form) Rules
The table must be already in 1 NF and all non-key columns of the tables must depend on the PRIMARY KEY.
The partial dependencies are removed and placed in a separate table.


3NF (Third Normal Form) Rules
The table must be already in 2 NF and all non-key columns are not transitively dependent on the complete primary key.
The Transitive dependency exists when a non-key column depends on other non-key columns rather than depending upon the primary key.

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine error while importing data from Excel file using SSMS 2016 Import Export wizard

by

One of the developer send email saying that they received error ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine while importing data from Excel file using SSMS 2016 Import Export wizard

On investigation found that if we Change the Excel Version to Microsoft Excel 2007-2010, it works. We get error if the Excel Version is 2016.

Schema changes history is failing with Trace file name ” is invalid error

by

Steps to resolve issue:
1. We need to make sure whether default tracing is enabled or not, we can check this using below options:
sp_configure ‘default trace enabled’ — If the run_value is 1 then the default trace for SQL Server is enabled
(OR)
SELECT* FROM sys.configurations WHERE configuration_id = 1568

2. Once you make sure the trace is enabled then you need to verify what is the default trace file, below are the differnt ways to identify it
select * from sys.traces
(or)
select * from sys.fn_trace_getinfo(NULL)
(or)
select * from :: fn_trace_getinfo(default)

In my case even though the default trace is enabled there is no trace file generated by SQL Server for some reason

So I have disabled  and re-enabled the default trace option.

Viola! It started working.

Reference

Taking SQL Server database offline “take offline” hangs

by

Today for the clean-up purpose we decided to take couple of databases offline.

With the SQL management studio UI, kept the defaults, started with the first one and it hanged.

Opened another session from another machine and ran to check for active connections:

SELECT db_name(dbid) as DB,
count(dbid) as Connections,
loginame as Login
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
ORDER BY DB

So went back to the previous machine and cancelled the operation. It means that some connection is still active, due to some user or client application still accessing the DB.

So one way is TO CHECK the drop all active connections in the UI.

If you need to “kill” the connections in order to do your work, try this:

alter database <dbname> set restricted_user with rollback immediate

This will terminate connections, rollback their transactions, and allow only privileged users to connect.
Once you have finished, you return to multi-user mode with this:

alter database <dbname> set multi_user

This is another solution:

alter database <dbname> set offline with rollback immediate

Reference

how to disable all the SQL server job schedules

by

NOTE: This is not the process for disabling all the SQL Server jobs.

When we have already disabled all the SQL Server jobs, if we want to disable all the schedules associated with them then just execute this script and get all the SQLs and manually execute them:

SET NOCOUNT ON

DECLARE @Schedule TABLE (ScheduleId INT)
DECLARE @ScheduleId AS INT
DECLARE @Sql AS VARCHAR(MAX)

SET @ScheduleId = ”

INSERT INTO @Schedule (ScheduleId)
SELECT s.schedule_id
FROM msdb.dbo.sysschedules AS s
WHERE s.[enabled] = 1
AND s.owner_sid <> 0x01
ORDER BY s.NAME ASC

WHILE @ScheduleId IS NOT NULL
BEGIN
SET @ScheduleId = (
SELECT MIN(ScheduleId)
FROM @Schedule
WHERE ScheduleId > @ScheduleId
)
SET @Sql = ‘
EXEC msdb.dbo.sp_update_schedule @schedule_id = ‘ + ”” + CONVERT(VARCHAR(5), @ScheduleId) + ”” + ‘
,@enabled = 0;
‘

PRINT @Sql
END

Reference