-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Calculating elapsed time from DATETIME

Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:

-- Vishal - http://SqlAndMe.com

DECLARE @startTime DATETIME
DECLARE @endTime DATETIME

SET @startTime = '2013-11-05 12:20:35'
SET @endTime = '2013-11-10 01:22:30'

SELECT	[DD:HH:MM:SS] =…

Read more

8 comments, 7 reads

Posted in SQL and Me on 23 December 2013

SQL Server – Kill all sessions using database

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Read more

3 comments, 14 reads

Posted in SQL and Me on 25 November 2013

SQL Server – Kill all sessions using database

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Read more

3 comments, 1,209 reads

Posted in SQL and Me on 25 November 2013

SQL Server – Kill all sessions using database

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Read more

3 comments, 149 reads

Posted in SQL and Me on 25 November 2013

SQL Server – Custom sorting in ORDER BY clause

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.

Sometimes, we need result set to be sorted in a custom order,… Read more

2 comments, 257 reads

Posted in SQL and Me on 18 November 2013

SQL Server – Custom sorting in ORDER BY clause

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.

Sometimes, we need result set to be sorted in a custom order,… Read more

2 comments, 2,339 reads

Posted in SQL and Me on 18 November 2013

SQL Server – Custom sorting in ORDER BY clause

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It orders the result set by specified column list. When used with character data type columns it sorts data in dictionary-order.

Sometimes, we need result set to be sorted in a custom order,… Read more

2 comments, 27 reads

Posted in SQL and Me on 18 November 2013

SQL Server – Script to get Service Account for all local instances

Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a single instance. If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information… Read more

1 comments, 9 reads

Posted in SQL and Me on 26 August 2013

SQL Server – Script to get Service Account for all local instances

Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a single instance. If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information… Read more

1 comments, 1,049 reads

Posted in SQL and Me on 26 August 2013

SQL Server – Script to get Service Account for all local instances

Earlier on my blog I posted on How to get SQL Server Service Account using T-SQL. That works on a single instance. If you multiple instances of SQL Server installed on a server you can use below script to get SQL Server and SQL Server Agent service account information… Read more

1 comments, 140 reads

Posted in SQL and Me on 26 August 2013

SQL Server – Get SQL Server Service Account using T-SQL

SQL Server service account information is stored in Windows Registry database. You can get this information from Services Console or SQL Server Configuration Manager.

To get account information from Services Console:

1. Go to Start > Run > Services.msc

2. Right Click on SQL Server Service, i.e. “SQL… Read more

5 comments, 2,235 reads

Posted in SQL and Me on 20 August 2013

SQL Server – Import text file using xp_cmdshell

There are several options available to import data from external sources to SQL Server. Such as Import & Export Wizard, BULK INSERT command, SSIS and OPENROWSET.

Apart from this options you can also use xp_cmdshell to import text file to SQL Server. We need to utilize dos command TYPE for… Read more

0 comments, 805 reads

Posted in SQL and Me on 29 July 2013

SQL Server – Get values as DATETIME from sysjobhistory

This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter… Read more

0 comments, 629 reads

Posted in SQL and Me on 22 July 2013

SQL Server – SELECTing/Displaying Top N rows from a table

To SELECT only top N rows from a table we can use TOP clause in SELECT statement. Using TOP clause we can also specify percentage option.

For example, both of these statements are valid:

USE SqlAndMe

GO

 

SELECT TOP 5 EmployeeId,FirstName,LastName

FROM   dbo.Table_Employees

GO Read more

0 comments, 697 reads

Posted in SQL and Me on 15 July 2013

SQL Server – How to change SQL Server ERRORLOG location

By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.

To change the location of ERRORLOG you need to modify the startup parameter -e.

For example, if you need to move logs to C:\Logs\… Read more

3 comments, 2,962 reads

Posted in SQL and Me on 8 July 2013

SQL Server – How to connect to SQL Server when ‘sa’ account is disabled

If you have lost the password for sa account or does not have any administrative account and you are locked out of SQL Server you can still login to SQL Server as an Administrator using Local administrator account.

 

You can use any account which is part of local Administrators… Read more

6 comments, 3,897 reads

Posted in SQL and Me on 1 July 2013

SQL Server – How to Enable a Disabled SQL Server Login

When an account is disable it cannot be used to login to SQL Server. You will receive below error when logging-in to SQL Server using a disabled Login account:

You can enable a disabled SQL Server Login using SQL Server Management Studio or via T-SQL code.

 

To enable a… Read more

2 comments, 762 reads

Posted in SQL and Me on 24 June 2013

SQL Server – Update Table with INNER JOIN

Often we may need to update a column in a table based of another column in another table.

In SQL Server you can do this using UPDATE statement by joining tables together.

To understand this better let’s take a look at below contrived example.

USE [SqlAndMe]

GO

 

SELECT CustomerID Read more

1 comments, 988 reads

Posted in SQL and Me on 18 June 2013

SQL Server – How to get sizes of all databases on a server

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Column Name Description
database_id ID of the database to which the file belongs to
type_desc Description…

Read more

8 comments, 994 reads

Posted in SQL and Me on 10 June 2013

SQL Server – How to Detach a Database

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database… Read more

0 comments, 956 reads

Posted in SQL and Me on 3 June 2013

Newer posts

Older posts