Handle Devide By Zero Error

Hi ..
set Arithaboth and Ansi_Warnings as OFF to handle devide by Zero or any kind of arithmatic errors

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
Declare @Denominator int =0
Declare @Numerator int =5
SELECT isnull(@Numerator / @Denominator,0) as Result

happy coding....

Steps to Remove Menu options in Export for Report Page in SSRS

Hi,
In SSRS(Sql Server Reporting Service), I wanted to remove extra options to export my report.

Here are the steps to remove option to export like (word, Excel, PDF, MHTML etc...) in SSRS (Sql Server Reporting Service)

Installation folder may be different from MSSQL.1, can be MSSQL.x (search for rsreportserver.config under Program Files\Microsoft SQL Server if not found)
Here are the steps for Disabling Excel Export Option in Reporting Services -

1. Go to Database server where reporting services is installed, open the rsreportserver.config file located at <Drive Letter>:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer 
2. Go to section.  
3. Change this entry to i.e. add Visible="false" for Excel rendering extension 
4. Same for PDF.  

Happy Reporting

Set Default Value as Function to column of a Table

Hello,
Generally we need to set default value to particular column in Database Table like isActive is true, IsDeleted is false.
same way many times we set CreatedOn Date as CurrentDate and that is by query of insert GetDate().
Static Values :
Its very easy to set some static value as Default value for column and that is as follow :


Functions :
Set Default Date for Createdon Field as Follow :

User Defined Functions :
Set User Defined function as Default Value as follow:
In my case I am supposed to get Random Hexa Code for Color. so I Have created an User Defined function for that

CREATE FUNCTION [dbo].[GetRandomColorCode]
( 
)
RETURNS varchar(6)
AS
BEGIN
Declare @Color varchar(6)
 RETURN (SELECT MyNewID FROM dbo.MyNewID)
END

Now set the function in columns as follow :





If this do not allow us to set Default value try following query to set

ALTER TABLE dbo.Table_1 ADD CONSTRAINT
 DF_Table_1_ColorCode DEFAULT ([dbo].[GetRandomColorCode]()) FOR ColorCode
GO

All the best



Happy Coding..

Second Highest Salary- Sql Query

Create Table #Emp(id int identity primary key,
    Emp varchar(100),
    Salary int)

insert into #Emp
select 'XXXX',17000 union all
select 'DDDD',21000 union all
select 'WWWW',30000 union All
Select 'HHHH',21000 union All
select 'CCCC',30000 union All
Select 'TTTT',21000 union All
SElect 'PPPP',21000

select * from #Emp

select #Emp.* 
from (select ROW_NUMBER() over (order by salary desc) as RowNumber, Salary
   from #Emp
    group by Salary) as tempEmp inner join #Emp on tempEmp.Salary = #Emp.Salary
   where tempEmp.RowNumber=2

Drop Table #Emp

Data Will Look like this



Output will be like this

Get Nth record from Table

Hello Friends Here is a simple query to get nth Record from Table

SELECT @NewPriority=Priority 
FROM (    SELECT 
                 Id
                , Priority
                , ROW_NUMBER() OVER (ORDER BY priority) as row 
         FROM Job 
         where IsDeleted=0     
               and Id not in ( select jobId from Job_Cylinder where IsDeleted=0)
       ) a
WHERE row=@cnt-(@NewPriority)+1

There are many ways to get this.. All the Best

DataBase Specific Queries

Hello friends

Here are few Database Specific Queries

• Select All Database list 

1: SELECT [name] FROM sys.databases

2: SELECT [name] FROM sys.sysdatabases

This will Result

• Select All Tables of particular Database. 

1: SELECT Name, type,type_desc FROM sys.objects 

2: where Type='u'

This will Result

• Here Fields Type and Type_Desc are

Type	Type_Desc
C	CHECK_CONSTRAINT
D	DEFAULT_CONSTRAINT
F	FOREIGN_KEY_CONSTRAINT
FN	SQL_SCALAR_FUNCTION
IF	SQL_INLINE_TABLE_VALUED_FUNCTION
IT	INTERNAL_TABLE
P	SQL_STORED_PROCEDURE
PK	PRIMARY_KEY_CONSTRAINT
S	SYSTEM_TABLE
SQ	SERVICE_QUEUE
TF	SQL_TABLE_VALUED_FUNCTION
TR	SQL_TRIGGER
U	USER_TABLE
UQ	UNIQUE_CONSTRAINT
V	VIEW

can be more than this…

• Select Columns from particular Table

1: SELECT column_name as [Column Name],data_type as [Data Type],Character_maximum_length as [Character Maximum Length]

2: FROM information_schema.columns

3: WHERE table_name = 'table_1'

This will Result

 
All The Best

Query for Recursive Data

Hi friends..

here is the query for Data kind of Recursive.
For Example….
for a particular thread in forum, its Comments
so in this case…
The Data structure will be like this
Table_3

Id	int
ThreadText	nvarchar(MAX)
PostedBy	int
PostedOn	DateTime
ParentId	int

Now if we want to have the hierarchical View of Data.
We can have query like this

   1: SELECT     T2.id , T2.name ,T1.id as ParentId, T1.name as ParentName

   2: FROM         Table_3 AS T1 INNER JOIN

   3:                       Table_3 AS T2 ON T1.id = T2.parentid

now the Task is to Arrange the Data how you want to Display..





All the best

Trigger with multiple insert/ Update/Delete

Hello Friends
Recently i faced a problem and got solution of it so..
i would like to put the solution on my Blog

I wanted to use Trigger to drop entries in a table on insertion of records in its parent table...
on first phase i created a trigger for the parent table (Table_1) to drop entries on Table_2.

this was working very perfectly...
but suddenly i came to know that it is not as per i expected...

i mean when i fire query of Insert using single record insert
i.e Insert into Table_1 (...fields..) values (...values..)

in this case it was perfectly fine...

but issue was when i fire query of multiple records to insert on a single query
i.e. Insert into Table_1
     select [Name],[Address] from Table_3
In this case the trigger fires only once for first record
so i was not getting rest records inserted into Table_2

after doing small RnD I came to know...the exact solution

Custom paging Query

Hi...

here is a way to write query for custom paging

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end

Sql Injection

Hello friends,

After a long time again now i am going to write something in my blog. Today i am
going to write about Sql injection.
So i will cover this many steps in my this topic

1. Create fear
2. what is Sql Injection
3. Description about Sql Injection