Manually attach database in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Manually attach database in SQL Server

Download sample database of SQL server and Manually attach database in SQL Server – AdventureWorksDB

SQL server 2005, does not come with default / sample databases. so it can be downloaded and installed from http://msftdbprodsamples.codeplex.com/releases/view/4004.

After downloading, the challenge for the new developer is that how to associate database with existing SQL server installed on system.

To associate the database, follow below steps:

right click on database and select “Attach” option.

Click on Attach option of Database

Continue reading “Manually attach database in SQL Server”

Convert rows into comma separated values column – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags , with Leave a comment on Convert rows into comma separated values column – SQL Server

Convert rows into comma separated values column – SQL Server

Converting rows values into comma separated column value is required lots of time:

example, i want to convert :

Name
------------------------------
India
USA
Japan
China
Switzerland

to

Continue reading “Convert rows into comma separated values column – SQL Server”

Get Login Session count, Session Mode, User Name, Machine name in SQL Server

Get Login Session count, Session Mode, User Name, Machine name in SQL Server

In SQL server sys.dm_exec_sessions Stores the login information about users like username, authentication mode (Windows or SQL), login time, machine name and therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc.

Continue reading “Get Login Session count, Session Mode, User Name, Machine name in SQL Server”

SQL Server Pivot Table Example

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 9 Comments on SQL Server Pivot Table Example

Example and tutorial of using PIVOT in SQL Server

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and understandability of mined data, pivot tables are common and preferred widely.

Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.
But the table columns are formed by the report data itself.

Microsoft SQL Server has introduced the PIVOT and UNPIVOT commands as enhancements to t-sql with the release of MS SQL Server 2005.

Continue reading “SQL Server Pivot Table Example”

Between Clause problem in Date comparison – SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 2 Comments on Between Clause problem in Date comparison – SQL Server

SQL Server Date Comparison Tips : Never user Between clause with DateTime in SQL Server

My seniors suggested me not to use the Between clause for Datetime. But whenever i tried to found the reason not to use Between clause nobody gave satisfactory answer. One day i caught in a problem where Between clause was not working. 🙂 I was very happy. you must be thinking that why the man is happy even if his code fails ? This time i got the answer of my question.

Why not to use the Between clause in Date comparison?

I will explain this problem with below example.

Continue reading “Between Clause problem in Date comparison – SQL Server”

Get Table information (Column Name, Data Type) in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 7 Comments on Get Table information (Column Name, Data Type) in SQL Server

Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server

To get the Table in information in SQL Server,  we can use below Query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Users'
ORDER BY ORDINAL_POSITION

Where’ Users’ is Table Name
Output is shown in below snap:

SQL Server INFORMATION_SCHEMA.COLUMNS
SQL Server INFORMATION_SCHEMA.COLUMNS

Continue reading “Get Table information (Column Name, Data Type) in SQL Server”

Create new User in SQL Server 2005

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with 1 Comment on Create new User in SQL Server 2005

Create new User in SQL Server 2005

Login into SQL Server and right click on security tab.

Select New -> Login.

SQL Server Security folder
SQL Server Security folder

Continue reading “Create new User in SQL Server 2005”

Install SQL Server 2005 in Windows XP

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on Install SQL Server 2005 in Windows XP

Steps to Install SQL Server 2005 – Standard edition in Windows XP

This article will describe that how to install the SQL server 2005 on Windows XP. Normally a wrong step in installation for beginners results in non working SQL Server instance on his windows XP. To install the SQL Server 2005 on Windows XP follow below steps:

The firts screen will be the license agreement screen, accept the terms and proceed.

SQL Server 2005 Accept License
SQL Server 2005 Accept License

The next screen will check the prerequisites for SQL Server 2005.

SQL Server 2005 Prerequisites
SQL Server 2005 Prerequisites

Continue reading “Install SQL Server 2005 in Windows XP”

Case statement in Where Clause – SQL Server – Conditional Where clause

Using Case statement in Where Clause in SQl Server. This script is used to create the conditional where clause

In few scenario, we might need to perform conditional where clause. Some time the condition can be written well in OR with Where clause but in some scenario it is not possible.

so this time, i have come up with one more article on writing the conditional where clause.

Example:

DECLARE @Student Table (Stream Varchar(20), [Name] Varchar(20), isAllow BIT )

INSERT Into @Student
VALUES
('Computer Engineering','Minal Zaa', 0)

INSERT Into @Student
VALUES
('Civil Engineering','Manoranjan Sahoo', 1)

INSERT Into @Student
VALUES
('scientist','Santosh Karemore', 1)

INSERT Into @Student
VALUES
('BCom','Rahul jha', 0)

SELECT
   Stream,
   [Name]
FROM
   @Student
WHERE
CASE
    WHEN Stream = 'Computer Engineering' THEN 1
    WHEN isAllow = 1 THEN 1
END = 1

Output:

Case statement in Where Clause

In above code, we have checked that if stream is ‘Computer Engineering’ then display the record or if IsAllow bit is true then display the record. Same query can be written using OR operator but here i wanted to demonstrate the use of Case statement in Where Clause.

Execute dynamic Query in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags with Leave a comment on Execute dynamic Query in SQL Server

How to execute the dynamic query in microsoft SQL Server 2005.

This script demonstrates that how to execute the dynamic query in the MS Server 2005.

Declare @Query nvarchar(4000)
SET @Query = 'Select GETDATE()'
EXEC sp_executesql @Query

Output will be like ‘2010-07-16 17:42:38.733’

Note : datatype supported for dynamic query is “ntext/nchar/nvarchar”
EXEC sp_executesql is responsible to run the dynamic query.