DBA

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

Posted on Updated on

Here are some SQL Server cluster related T-SQL queries which helps you to find SQL Server Cluster Nodes and their shared drives.

  1. Find name of the Node on which SQL Server Instance is Currently running
    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS[CurrentNodeName]

    If the server is not cluster, then the above query returns the Host Name of the Server.

  2. Find SQL Server Cluster Nodes
    a. Using Function

    SELECT * FROM fn_virtualservernodes()

    b. Using DMV

    SELECT * FROM sys.dm_os_cluster_nodes
  3. Find SQL Server Cluster Shared Drives
    a. Using Function

    SELECT * FROM fn_servershareddrives()

    b. Using DMV

    SELECT * FROM sys.dm_io_cluster_shared_drives

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.