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