Automatically generate report in a specific format using SSRS

If you would like to generate a report in a specific format(html,pdf,xls, csv, xml) without having the user to select the format, use this technique:

PDF : http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF

EXCEL: http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=Excel

With Parameters: Passing State and City as parameters

http://localhost/ReportServer?CompanySales&rs:Command=Render&rs:Format=PDF&State=CA&City=ALI

Inserted and Deleted Tables in SQL Server 2005

Triggers use two logical tables called the INSERTED and the DELETED table. These tables are accessed only inside triggers and you cannot directly modify the data in the tables. The tables hold the new and old rows of the data being inserted or deleted.

If you DELETE a row , a row will be created in the DELETED table. Similarly if you INSERT a row, a row is created in the INSERTED table. If you update a record with an UPDATE statement, the INSERTED table will contain the updated row and a previous state of the row will be added to the DELETED table.

Note: You cannot use a CREATE INDEX on these tables.

Read more about INSERTED and DELETED tables over here:

http://msdn2.microsoft.com/en-us/library/ms191300.aspx

How to check if a string contains numbers

There are number of ways in SQL Server to check if a string has numbers in it. In one of the previous articles, we saw how to Extract Numbers from a String using Sql Server 2005

I recently got a comment on that article from a sqlservercurry.com visitor who asked me to find out if a string has numbers, using PatIndex. Here's how to do so using PatIndex :

DECLARE @str1 varchar(15)
SET @str1 = 'abcde99jtk'

-- Check if PatIndex returns a value > 0. If yes, the string has numbers
-- If the value is 0, the string does not have numbers
IF PATINDEX('%[0-9]%',@str1) > 0
PRINT 'YES, The string has numbers'
ELSE
PRINT 'NO, The string does not have numbers'

Searching a person's Name with the First and Last Name joined together

The title may not be very clear, but let us see the problem scenario. Let us consider a table Customers with the following definition

CREATE TABLE #Customers
(CustID int, CustFName varchar(20), CustLName varchar(20), CustAge int)

INSERT INTO #Customers VALUES(1, 'Jack', 'blicak',26)
INSERT INTO #Customers VALUES(2, 'Henry', 'Snipper',36)
INSERT INTO #Customers VALUES(3, 'Jack', '',55)
INSERT INTO #Customers VALUES(4, 'Joseph', 'Befre',34)
INSERT INTO #Customers VALUES(5, 'Jill', 'blicak',26)
INSERT INTO #Customers VALUES(6, 'Jack', 'Matter',29)

Problem Statement : Assume we have to search for Jack Bilack, the string can be searched in two forms: The user passes in Jack as the first name and Bilack as the Last Name. All's fine till here. But what if the user puts 'Jack Bilack' in the first name field and passes a empty last name. Let us see how to deal with it using COALESCE.

DECLARE @fname as varchar(20)
DECLARE @lname as varchar(20)
SET @fname = 'Jack'
SET @lname = 'Blicak'

SELECT CustID, CustAge from #Customers
WHERE CustFName + coalesce(' ' + CustLName , '') = @fname + coalesce(' ' + @lname, '')

Now search the same name using the following :

SET @fname = 'Jack Blicak'
SET @lname = ''
SELECT CustID, CustAge from #CustomersWHERE CustFName + coalesce(' ' + CustLName , '') = @fname + coalesce(' ' + @lname, '')

The results are the same and as desired.

SET VS SELECT - When to use what?

Let us observe the following two queries :

DECLARE @CID int
SET @CID =(Select CID from Customers)
SELECT @CID = (Select CID from Customers)

Both work well as far as a single row is being returned. When multiple rows are returned, the SET fails , whereas SELECT is the way to go. If the query returns nothing, SET assigns NULL as the value of the variable, whereas SELECT ignores it. SELECT is also capable of making multiple assignments.

Usually when a single value is to be assigned to a variable, go in for SET. However if you are setting the value using a query, as shown above, then its better to go in for SELECT as the rows returned 'could' be more than one, which can be handled by SELECT but not SET.

Another difference is that SET is ANSI standard, SELECT is not.

As a rule of thumb, I go ahead with SELECT when I have to set multiple variables. Else if I have to only set one variable, I am better off with SET.

Here's a good read about SET VS SELECT performance
http://www.sqlmag.com/Articles/ArticleID/94555/94555.html

Find out the tables having the largest size in your database using SQL Server 2005

In one of the previous articles, we saw how to Display the size of all tables in Sql Server 2005 .

In this article, we will see how to find out the largest tables in your database and display the results in a sorted order

USE YourDB

DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL,
row varchar(10),
reserved varchar(10),
data varchar(10),
index_size varchar(10),
unused varchar(10)
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
INSERT INTO @TblNames EXEC sp_spaceused @str
SET @I = @I +1
END

-- Display results in Sorted order

SELECT tbl_name as TableNm, CAST(REPLACE(data, ' kb', '') as int) as TblSize,
CAST(REPLACE(index_size, ' kb', '') as int) as IdxSize
FROM @tblNames ORDER BY TblSize DESC

Resolving CREATE DATABASE Permission denied in database 'master' error on Vista and SQL Express

Have you encountered the error 'CREATE DATABASE Permission denied in database 'master'' even though you are logged into Windows Vista with administrator privileges.

Reason for the error : Windows Vista users that are members of the Windows Administrators group are not automatically granted permission to connect to SQL Server, and they are not automatically granted administrative privileges.

Resolution: Grant rights to the administrator. Follow these steps:

Step 1: Go to Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.

Step 2: Click on 'Add New Administrator'.

Step 3: In the 'SQL Server User Provisioning on Vista' dialog box, look out for the 'Member of the SqlServer SysAdmin role ' in the 'Available Privileges' box. Transfer it to the 'Privileges that will be granted to' box. Click Ok.

Note: You will be able to see 'Add New Administrator' in the Configuration tool only if you have logged in as an administrator

DATENAME() function in SQL Server 2005

The DATENAME() is quiet a handy function if you want to return a literal form (string) of the part of the date specified.

The syntax goes like:
DATENAME ( datepart ,date )

Where :
datepart - specifies the part of the date to return
date - datetime or smalldatetime expression

USAGE:

DECLARE @Dt datetime
SET @Dt = '2008-04-15 8:34:54.713'

SELECT DATENAME(year, @Dt) as 'Year'
-- Returns 2008

SELECT DATENAME(quarter, @Dt) as 'Quarter'
-- Returns 2

SELECT DATENAME(month, @Dt) as 'Month'
-- Returns April

SELECT DATENAME(dayofyear, @Dt) AS 'Day of Year';
-- Returns 106

SELECT DATENAME(day, @Dt) AS 'Day';
-- Returns 15

SELECT DATENAME(week, @Dt) AS 'Week';
-- Returns 16

SELECT DATENAME(weekday, @Dt) AS 'Weekday';
-- Returns Tuesday

SELECT DATENAME(hour, @Dt) AS 'Hour';
-- Returns 8

SELECT DATENAME(minute, @Dt) AS 'Minutes';
-- Returns 34

SELECT DATENAME(second, @Dt) AS 'Seconds';
-- Returns 54

SELECT DATENAME(millisecond, @Dt) AS 'Milliseconds';
-- Returns 713

Find First and Last Day Of Each Month

The first day of each month is always 1. However if you interested to find the First and Last Date of each month using a T-Sql Query, here it is:

-- Return First and Last Date of a Month
DECLARE @Month smallint
SET @Month = 1

SELECT DAY(DATEADD(Month, DATEDIFF(Month, -1, getdate()) - 2, -1) + 1) as FirstDayofMonth,
DAY(DATEADD(Month, DATEDIFF(Month,0,getdate())+@Month, -1)) as LastDayOfMonth

If you wish to find out the Lastday of the previous month, just set @Month to 0.

Note: The value for @Month depends on the current month you are in. So if this month is September, the value of @Month is 1. Similarly for Oct(2), Nov(3), Dec(4), Jan(5), Feb(6) and so on.

Also read SQL Server: First and Last Sunday of Each Month

Populate a table from a .CSV or .TXT file using SQL Server 2005

You can use Bulk Insert or SSIS to import text/csv files. There are some advantages and disadvantages using any of these methods. In this article, we will explore how to use the OPENROWSET to read a data file and populate a table.

Note: In SQL Server 2005, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

Follow these steps:

Step 1: Create a database called 'Employees'. Create a table called '
EmployeeDetails' in it using the script given below:

USE [Employee]
GO
/****** Object: Table [dbo].[EmployeeDetails] Script Date: 04/11/2008 11:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeDetails](
[EmployeeID] [nvarchar](50) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeAddress] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Step 2: To run ad-hoc queries on the SQL server, you would first need to enable it using the following query:

sp_configure 'show advanced options',1
RECONFIGURE WITH override
GO
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
GO

Step 3: Create a txt/csv file with the following format on your C:\. The file is called 'Employee.csv'

EmployeeID EmployeeName EmployeeAddress
1 Kat 23/Avenue. Park
2 Jim Jeoff Street
3 Tom Lifer Road

Step 4: The final step is to run the query and populate the EmployeeDetails table

USE Employee
GO
INSERT INTO EmployeeDetails(EmployeeID,EmployeeName,EmployeeAddress)
SELECT *
FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=CSV;','SELECT * FROM Employee.csv')

References : http://msdn2.microsoft.com/en-us/library/ms190312.aspx

How to find out if the year is a LEAP year

Here's a simple script that can detect if the current year is a LEAP year. You can create a function out of this query and use it in your projects to detect if the year is a leap year and take decisions based on the same.

-- Detect if the year is a LEAP year

DECLARE @dt datetime
DECLARE @yr int
DECLARE @y char(4)
SET @dt = GETDATE()
SET @yr = YEAR(@dt)
SET @y = CAST(@yr as char(4))

SELECT CASE
-- Years like 2000, 2400 are always leap years
WHEN @yr % 400=0 then @y + ' is a Leap Year'
-- Years like 1700, 1900 divisible by 100
-- but not divisible by 400, are not leap years
WHEN @yr % 100=0 then @y + ' is not a Leap Yr'
-- Years like 2004 that are not divisible by
-- 400 and 100 but are divisible by 4, are leap years
WHEN @yr % 4=0 then @y + ' is a Leap Year'
ELSE @y + ' is not a Leap Yr'
END
AS LeapYearDetection

Extract Numbers from a String using Sql Server 2005

I have seen a common requirement where we have numbers and alphabets mixed up in a column (house no in the address field) and the number has to be extracted out of the string.

Here's a query to do so:

-- This query is a Modification of the Original query by 'usenetjb'

DECLARE @NumStr varchar(1000)
SET @NumStr = 'This string contains 5 words and now 9 words';

BEGIN
WHILE PATINDEX('%[^0-9]%',@NumStr)> 0
SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')
END

PRINT @NumStr

Find Hours, Minutes and Seconds in between two Datetime

In one of the previous articles, we explored how to find the difference between two dates.

In this short snippet, let us see how to find the Hours, Minutes and Seconds in between two Datetime.

-- Find Hours, Minutes and Seconds in between two datetime
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()

SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds

SSRS Report Design Layout Tricks

Here are 3 commonly used features while designing report layout using Sql server reporting services(SSRS) in SQL Server 2005

Tip 1: Add a Page Header to the report
Open a report. Choose the Layout view.Go to Report menu > Page Header/Page Footer > Click on it.Drag a TextBox from the tools menu to the Header/Footer and set the properties on it

Tip 2: Hide a Page Header on the First and Last Page of the report
Open a report. Choose the Layout view.Click the page header. In the Properties window, set the PrintonFirstPage and PrintonLastPage property to False.

Tip 3: Add a Page Break
Open a report. Choose the Layout view.Right click an item and choose properties. On the General tab, Page breaks, select Insert a page break before the list/after the list.

How to delete records from a large table in small portions

At times, you need to delete records from a large table. Performing a delete operation on such a table can become quiet resource intensive. In order to increase performance, you can delete the rows in small portions. Let us see how using this query:


-- Create a temporary table CustomersTemp
SELECT * into Northwind.dbo.CustomersTemp from
Northwind.dbo.Customers

-- Procedure to delete in small groups
CREATE PROC spoc_deleteinsmallgroups
@NoOfRowsToDelete int
AS
DECLARE @CurrentRowCount int

-- Count the number of rows in the table
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)

-- Loop and delete records in small bits till the rowcount is 0
WHILE(@CurrentRowCount > 0)
BEGIN
DELETE TOP (@NoOfRowsToDelete) FROM Northwind.dbo.CustomersTemp
-- RECOUNT
SELECT @CurrentRowCount = (SELECT COUNT(*) FROM Northwind.dbo.CustomersTemp)
Print @CurrentRowCount
END

-- Execute the procedure and pass the number of rows
-- to delete at a time
EXEC spoc_deleteinsmallgroups 25

How to retrieve record from a specific row for tables that do not have an ID

Taking the example of the Northwind database, the table Customer has a primary key that is non-numeric. So in order to find the row at position 20 (ordered by CustomerID), we will need to do some workarounds to achieve the same. Let us see how we can do this easily by using the
ROW_NUMBER()

Query : To find the record at a particular row

USE NORTHWIND
GO
SELECT *
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS cid, CustomerID, CompanyName, ContactName, ContactTitle
FROM Customers) C
WHERE cid = 20