Month: January 2017

How to get working days between two dates in SQL Server

Posted on Updated on

This post is about how to find the working day between Two Dates.

Consider the working days from Monday to Friday. We know that DATEDIFF function is used to calculate the Difference between two dates.

CREATE FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME
  ,@EndDate  DATETIME
)
RETURNS  INT
AS
BEGIN
     DECLARE @CountOfWorkingDays INT
     SELECT @CountOfWorkingDays= ((DATEDIFF(dd, @StartDate, @EndDate) + 1) 
 -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
 -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END))
RETURN @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-01-01','2017-01-31') CountOfWorkingDays 
GO
CountOfWorkingDays
22

In above function  DATEDIFF(dd,@StartDate,@EndDate ) + 1 gives us  the number of days between the two dates and  DATEDIFF(wk,@StartDate,@EndDate ) this gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count.CASE statement will excludes the day if it’s a Saturday or Sunday.

In case you want to calculate working days from Monday to Saturday then just remove last case statement and * 2 from  (DATEDIFF(wk, @StartDate, @EndDate) * 2)

ALTER FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME, 
 @EndDate    DATETIME )
RETURNS  INT
AS
BEGIN
 DECLARE @CountOfWorkingDays INT
 SELECT @CountOfWorkingDays=
             ((DATEDIFF(dd, @StartDate, @EndDate) + 1)
 -(DATEDIFF(wk, @StartDate, @EndDate) )
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) )
RETURN @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-02-01','2017-02-28') CountOfWorkingDays 
GO
CountOfWorkingDays
24

 

Whats the Difference Between Detaching And Taking Database offline?

Posted on Updated on

Once you detach your database there will not be an entry in the sysdatabases system table.
i.e you can query and confirm the same as

 Select * from master..sysdatabases

In the end result of the above query you cannot see the db you have detached whereas
if you bring the database offline, there will be an entry present in the sysdatabases system table;
You can verify the same by running the above query and you can see that the db you took OFFLINE will be present in the sysdatabases table !

Basically, detaching/attaching is used to move database files between instances(servers) where as in Offline you can not move files. After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. The purpose of these commands are totally different though it looks like same.

Rename Table In SQL SERVER

Posted on Updated on

We can rename table by using T-SQL or SQL server management Studio.

Before renaming any object in SQL server we have to check the dependency of that object.

We can check it by using sp_depends  Or sys.sql_expression_dependencies this DMV.

After renaming table we have to change the table name in each queries, stored procedure, views, function and all other objects where it was being referenced.

Using SQL Server Management Studio

To rename a table

  1. In Object Explorer, right-click the table you want to rename and choose Design from the shortcut menu.
  2. From the View menu, choose Properties.
  3. In the field for the Name value in the Properties window, type a new name for the table.
  4. To cancel this action, press the ESC key before leaving this field.
  5. From the File menu choose Save table name.

 or Simply select the object and press F2 key and type the new name.

Using T SQL:

USE YourDatabaseName;

GO

EXEC sp_rename ‘Schema_name.old_Table_name’, ‘new_Table_name ‘;

Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Posted on Updated on

Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Recently I was dropping orphan user from database SSISTEST and Got below error:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138).

The error message clearly tell us that the user is associated with one of the database schema, So to delete this user we have to transfer this schema to another user.

To find which schema is associated with that user use below query:

USE SSISTEST

GO

SELECT s.Name

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘test’);

This query will give below output:

name
db_ddladmin
db_datareader
db_datawriter

 

Then use the output to assign to other user let’s say dbo.

ALTER AUTHORIZATION ON SCHEMA:: db_ddladmin  TO dbo

GO

ALTER AUTHORIZATION ON SCHEMA:: db_datareader  TO dbo

GO

ALTER AUTHORIZATION ON SCHEMA:: db_datawriter  TO dbo

 

DROP USER test

Generic Script:

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘YourUserID’);

Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

Fix Error: Could not drop login ‘test’ as the user is Currently logged in( Microsoft SQL Server , Error:15434)

Posted on Updated on

Fix Error: Could not drop login ‘test’ as the user is Currently logged in( Microsoft SQL Server , Error:15434)

 Recently I have created a test Login on UAT server to test some cases.

When I ran the script to drop the Login.

DROP LOGIN test

GO

It has given bellow error.

Msg 15434, Level 16, State 1, Line 1
Could not drop login ‘test’ as the user is currently logged in.

First of all, I have closed all the connections from SSMS there was no connection open.

And Finally I come with solution to kill all connection of test user, I ran below script to kill all open connection of ‘test’ user.

Here is the script.

SELECT session_id

FROM sys.dm_exec_sessions

WHERE login_name = ‘test

And killed all active connection

Kill 51

GO

Kill 55

Replace 51, 52 with your session_id.

DROP LOGIN test

And finally it is dropped.

Task- Bulk Insertion

Posted on Updated on

Bulk insertion is used to transfer data from files to SQL server tables.

In this article, I provide an overview of bulk insertion task of SSIS.

  1. Create a package named BulkInsert.dtsx
  2. Open SSMS and create Employee table in AdventureWorks2012.

USE [AdventureWorks2012]

GO

CREATE TABLE [dbo].[Employee](

[EmpId] [int] NOT NULL,

[JobTitle] [nvarchar](50) NOT NULL,

[BirthDate] [date] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [date] NOT NULL

) ON [PRIMARY]

GO

  1. Back to your dtsx package and drag -n drop Bulk insert task on control flow design pane. The Task editor has red sign on upper right corner of task indicates it’s not configured yet.
  2. Right click on Task and choose edit option or double click on task.

In General provide name LoadEmployeeDetails AND in description load Employee details from flat file.bulk-insertion-task

1

  1. Click on Connection tab. From the Connection drop down box, select.

This will open the Configure OLE DB Connection Manager dialog. Now, you’re going to create a connection to the AdventureWorksDW12 database

2

  1. Click OK to go back to the previous screen, and click OK again to return to the Bulk Insert

Task Editor.

You’ll now see that the Connection Manager you just created has been transposed into the Connection dropdown box.

  1. To Define Destination Table – Click on destination table and select dbo.Employee
  2. Leave the remaining options set to the defaults. The RowDelimiter property option will be

{CR}{LF} (a carriage return) and the ColumnDelimiter property should be set to Comma {,}.

  1. For the File option, again select to create a new Connection Manager.

This will open the File Connection Manager Editor.

3

  1. For the Usage Type, select Existing File. Then point to employee.csv for the File option. Click

OK to return to the editor. Your final Bulk Insert Task Editor screen should look similar to

If you have column header in flat file then go to Options and change first row to 2.

This would start to import data from second row.

Finally right-click on package and execute it. After execution completed go to SSMS and check the table data.

Concatenate Rows using FOR XML PATH()

Posted on Updated on

To concat multiple rows of data into single row we can use XML PATH(”).

SELECT ‘,’ + Column_Name … FOR XML PATH(”)

Generates a set of comma separated values, based on combining multiple rows of data from the Column_Name column. It will produce a value  ,abc,pqr,

STUFF(…,1,1,”)

Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

For example :

We will create a Temp table and insert some sample data.

DECLARE @ConcateTest TABLE(Id INT,CityName VARCHAR(20))

INSERT @ConcateTest(Id,CityName)

VALUES(1,’Los Angeles’),(1,’Austin’)

,(2,’Houston’),(2,’         Fort Worth’)

,(3,’New York’),(3,’Dallas’)

,(4,’Atlanta’),(4,’cape town’)

SELECT DISTINCT ID ,

STUFF((SELECT ‘,’+CityName

FROM @ConcateTest CT1

WHERE CT1.ID=CT2.ID

FOR XML PATH(”)),1,1,”) AS CityName FROM @ConcateTest CT2

ID CityName
1 Los Angeles,Austin
2 Houston,      Fort Worth
3 New York,Dallas
4 Atlanta,cape town