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

 

CREATING YOUR OWN REPORT TEMPLATES WHEN USING MICROSOFT SQL SERVER REPORTING SERVICE

Posted on Updated on

Recently I have configured Scale-out deployments for Sql Server Reporting services which are used to increase scalability of report servers to handle more concurrent users and larger report execution loads.

I have created some sample reports and shown to my VP, She told me that she wants the company logo , name and address of company on every report then I have created a customized SSRS template for Organization .

In this post I am going to tell you how to create your own SSRS template for your own organization instead of using predefined template. For example header of all report should be your company logo and footer will be your company address and more info like when the report is run and more. With this, you can ensure consistent report layout across all reports.

Report template is nothing but it’s an .rdl file which has a default location  as

For SQL Server 2005 : C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2008 : C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2012: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

For SQL Server 2016 : C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Creating a Custom Report Template

Here are the steps:

1. Open a new Report Server project in your Business Intelligence Development Studio.

2. go to solution explorer, reports folder, Right click and choose Add –> New Item. Select report -> Add

3. Add the report header / report footer, Page header / Page footer, your logo, some image / color background for the report, common globals like Page numbers/ total pages,userId, parameters etc.

4. Save the report.

5. Now copy the above created RDL file and paste it in the Visual studio project items folder,

In my case the location is C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now you can use this template for your further reports

All you need to do is, Add a new item in a similar way (From the solution explorer, reports folder, Right click and choose Add –> New Item.)

Congratulation  , You made it now ..!