Fix Row Height for a Row Reporting Services 2005

Might be you have ever faced a problem that we set Row Height on Visual Studio but when we export report to excel , row expands to its original size.

Here is the solution for this in Reporting Services 2005:
1. Click on each cell of the row and Set Properties CAN GROW = False
2. Put a under scrore in each cell . (Its mandatory otherwise CAN Grow facility will not work)

Grant permission on a Store Procedure To Login

The EXECUTE permission was denied on the object 'SPName', database 'Database', schema 'dbo'.

This error accours when we try to execute a Stored procedure using Login that does not have execute permission on that database.

Here is the solutuion :

Note : Only sysadmin or dbowner can grant permission.

1. Login with sysadmin or dbowner login on server.
2. Go to the properties of Login on which you want to grant permission.
3. Click on User Mapping, and Map the login with database.
4. Then execute Grant statement :
GRANT EXECUTE ON SPName TO LoginName;

Try again now to execute the Stored Procedure with Login that we have permitted now.

Primary Key Without Clustered Index

We have read/heard many times that clustered index is created when we create Primary key on column.

But is it possible to create PK witout Clustered Index?

Yes it is possible but one of the index will created either Clustered or NonClustered .

See SQL:

Create Table dbo.PKWithoutCLIdx
(
ID Int Primary Key NONCLUSTERED
)

In this way we can force SQL Server to created NonClustered on Primary Key.


Thanks,
Randhir

Get size of all tables

Here is the SP to get the size of all table:


Create PROCEDURE dbo.GetAllTableSizes
AS

Print 'Read complete Article in case of collation conflict error '

IF Object_ID ('tempdb..#TempTable') Is Not Null DROP TABLE #TempTable

----Declare Cursor
DECLARE @TableName VARCHAR(100)
DECLARE tableCursor CURSOR
FOR
Select Schema_Name(Schema_ID) + '.'+T.Name-----Concate schema also
From Sys.Tables T
For Read Only

--Temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Close cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT Schema_Name(Schema_ID) AS [Schema],T.*
FROM #TempTable T
Inner Join sys.tables ST On T.tableName = ST.Name ----To Disply the schema
Order By T.numberofRows Desc

Go
Exec Dbo.GetAllTableSizes

/*-If you face collation conflict error like "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
"
Just do simply following steps:
1. Execute sp_Help [sys.tables]
2. Copy Collation for the column NAME
3. Paste that copied collation in SP at temp table script part,
Your temp table in SP will be looks like:
CREATE TABLE #TempTable
(
tableName varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

4. Alter the SP
5. Execute again.


Thanks,
Randhir

7 Differences Between SP and FN

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored procedure may or may not retun a value.
2.Functions can return a table whereas stored procedures can create a table but can't return table.
3. Stored procedures can be called independently using exec keyword whereas function are called using select statements.
4. Stored procedure can be used to change server configuration(like setting permissions ) whereas function can't be used for this.
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a funtion or another stored procedure similarly a function can call another function and a stored procedure.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.


Thanks..

Find nth highest salary..

How to find the nth highest salary to salary table??
I have faced many times this question in interviews, I think,
it is the most popular question asked in interview.

Read many articles on Internet and found that following is the best one to find Nth highest salary using TOP clause.

Suppose we want to find 3rd highest salary, then:-

Create Table Salary
(
EmpID Int,
Salary Int
)

Insert Into Salary
Select 101, 25000
UNION
Select 102, 45000
UNION
Select 103, 75000
UNION
Select 104, 5000
UNION
Select 105, 3000
UNION
Select 106, 2000
UNION
Select 106, 97000

Select * From Salary
Order By Salary Desc

--Identify
Select Top 1 Salary
From
(
Select Top 3 Salary
From Salary
Order By Salary Desc
) aa
Order By Salary


OUTPUT : 45000

So simply using this SQL we can find Nth highest salary to change the Second Top clause.

Thanks...

Delete Duplicate Record using CTE?

Hi All,
I know there are many ways to delete duplicate records to table, but some times we find that there is no column to identity each record like Identity or any PK.
So handle this requirement , I think CTE is best option here..
Try sql:

----Delete Duplicate
Create Table Product
(
Product Varchar(100),
Price Int
)

Insert Into Product
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Refined','300'
UNION ALL
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Juice','75'

UNION ALL
Select 'Juice','75'

Select *,'Before' from Product;

WITH CTE (ID,Product,Price)
AS
(
Select ROW_Number () OVER(Partition By Product Order By Product) AS ID,*
from Product
)
Delete
From CTE
Where ID >1;

Here Row_Numer function generates unique number for each partition and then we can simply delete the records having ID value > 1 and solve our problem.

Thanks...