Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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, 1,991 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, 659 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, 530 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, 551 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, 1,801 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,568 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, 566 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, 833 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, 788 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, 777 reads

Posted in SQL and Me on 3 June 2013

SQL Server – Filtering objects in Object Explorer – Management Studio

When you are working on a database with a large number of objects sometimes it becomes a challenge to locate objects. Object Explorer in SQL Server Management Studio lists all objects according to category and it may be hard to locate specific object.

To resolve this you can Object Explorer… Read more

4 comments, 569 reads

Posted in SQL and Me on 28 May 2013

SQL Server – Show/Hide Results Pane in Management Studio 2012

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide results pane using keyboard shortcut Ctrl+R. This shortcut was also present in Query Analyzer (SQL Server 2000).

This shortcut is no longer available in SQL Server Management Studio 2012. In this version you… Read more

6 comments, 1,079 reads

Posted in SQL and Me on 20 May 2013

SQL Server – How to Move Table to Another Schema

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I… Read more

2 comments, 1,468 reads

Posted in SQL and Me on 13 May 2013

SQL Server – How to find Default data and log path for SQL Server 2012

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data… Read more

1 comments, 650 reads

Posted in SQL and Me on 6 May 2013

SQL Server – Finding TCP Port Number SQL Instance is Listening on

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL… Read more

6 comments, 3,865 reads

Posted in SQL and Me on 1 May 2013

SQL Server – How to check if you are running 32-bit or 64-bit version

There are two different command you can use to check if you are running 32-bit or 64-bit version of SQL Server.

Using @@VERSION:

You can user @@VERSION system variable to determine edition and architecture of SQL Server as below:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0… Read more

0 comments, 732 reads

Posted in SQL and Me on 22 April 2013

SQL Server – Differences between Clustered and Non-Clustered Indexes

Both Clustered and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.

Below are some characteristics of Clustered Indexes and Nonclustered Indexes in SQL Server.

Clustered Index:

1. The leaf node of a Clustered Index contains data pages of the… Read more

6 comments, 1,225 reads

Posted in SQL and Me on 19 April 2013

SQL Server – Import Data from Excel using T-SQL

To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.

For this example I have created a… Read more

6 comments, 19,591 reads

Posted in SQL and Me on 15 April 2013

SQL Server – Add Date/Time to output file of BCP / SQLCMD (2)

Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when… Read more

2 comments, 708 reads

Posted in SQL and Me on 12 April 2013

SQL Server – Add Date/Time to output file of BCP / SQLCMD

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify… Read more

1 comments, 2,582 reads

Posted in SQL and Me on 9 April 2013

Newer posts

Older posts