How to stop or disable Recursive Trigger/Nested Trigger in SQL Server.

A trigger execution may occured another trigger execution or same trigger execution. This trigger called Nested Trigger Or Recursive Trigger. SQL Server allows maximum 32 Level recursion.

Disable Nesting/Recursing Triggers

Method 1:

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

Method 2:

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Method 3:




Restrict Trigger Nesting to certain level.

Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.

IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Comma seperated date splitting without using loop.

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012  4:00AM,May 05 2012  4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                 ) strDt,charindex(',',CustomDates) as pos
       
    from @TableVar
    union all
    select s.ID,
        s.CustomDates,
       
             substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)       

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos


    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

---------------------------------------------------------------

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')


DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)

Normal query instead of MERGE operation

/****** Object:  StoredProcedure [dbo].[spBlockHr_SAVE]    Script Date: 04/11/2012 15:32:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Author    : Nikhildas
    Date    : July 01 2011 10:45AM
    Purpose : To Save Block HR value.
    Execute : Exec [spBlockHr_SAVE] <xml Value>,@P_RET_VAL    OUTPUT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Modified By                On                Remarks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    <Next Entry>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
 ALTER PROCEDURE [dbo].[spBlockHr_SAVE]
 (
    @P_XML        XML,
    @P_RET_VAL    INT OUTPUT
 )
 AS
    SET NOCOUNT ON

    DECLARE @DocHandle        INT
   
    DECLARE @BlockHr TABLE
    (
        ID            int ,
        SECTOR        nvarchar(255) ,
        Route        nvarchar(255) ,
        CODE        nvarchar(255) ,
        Month01     float ,
        Month02     float ,
        Month03     float ,
        Month04     float ,
        Month05     float ,
        Month06     float ,
        Month07     float ,
        Month08     float ,
        Month09     float ,
        Month10     float ,
        Month11     float ,
        Month12     float ,
        Total        float
    )   
           
    BEGIN TRY
        BEGIN TRANSACTION
        EXEC sp_xml_preparedocument @DocHandle OUTPUT,@P_XML
       
        INSERT INTO @BlockHr
        SELECT
            ID,
            SECTOR    ,
            Route,
            CODE,
            Month01    ,
            Month02    ,
            Month03 ,
            Month04    ,
            Month05    ,
            Month06    ,
            Month07    ,
            Month08    ,
            Month09    ,
            Month10    ,
            Month11    ,
            Month12    ,
            Total   
        FROM    OPENXML(@DocHandle,'/Root',2)
        WITH
        (
            ID            INT,
            SECTOR        NVARCHAR(1000),
            Route        NVARCHAR(1000),
            CODE        nvarchar(255),
            Month01        FLOAT(53),
            Month02        FLOAT(53),
            Month03        FLOAT(53),
            Month04        FLOAT(53),
            Month05        FLOAT(53),
            Month06        FLOAT(53),
            Month07        FLOAT(53),
            Month08        FLOAT(53),
            Month09        FLOAT(53),
            Month10        FLOAT(53),
            Month11        FLOAT(53),
            Month12        FLOAT(53),
            Total        FLOAT(53)

        )
                   
        EXEC sp_xml_removedocument @DocHandle
/*    Commented on 11-Jul-2011 (For Avoid Merge statement)       
        MERGE BlockHr AS T
        USING @BlockHr    AS S
        ON S.ID = T.ID
        WHEN MATCHED
        THEN
            UPDATE
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
        WHEN NOT MATCHED BY TARGET
        THEN
            INSERT
            (
                SECTOR    ,
                Route,
                CODE,
                Month01    ,
                Month02    ,
                Month03 ,
                Month04    ,
                Month05    ,
                Month06    ,
                Month07    ,
                Month08    ,
                Month09    ,
                Month10    ,
                Month11    ,
                Month12    ,
                Total           
            )
            VALUES
            (
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03 ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total   
            );
*/
            --// Updation
            UPDATE T
                SET SECTOR    = S.SECTOR,
                    Route    = S.Route,
                    CODE    = S.CODE,
                    Month01    = S.Month01,
                    Month02    = S.Month02,
                    Month03 = S.Month03,
                    Month04    = S.Month04,
                    Month05    = S.Month05,
                    Month06    = S.Month06,
                    Month07    = S.Month07,
                    Month08    = S.Month08,
                    Month09    = S.Month09,
                    Month10    = S.Month10,
                    Month11    = S.Month11,
                    Month12    = S.Month12,
                    Total    = S.Total
            FROM [BlockHr] T INNER JOIN @BlockHr S
            ON S.ID = T.ID

            --// Insertion
            INSERT INTO [BlockHr]
            SELECT                
                S.SECTOR    ,
                S.Route        ,
                S.CODE        ,
                S.Month01    ,
                S.Month02    ,
                S.Month03    ,
                S.Month04    ,
                S.Month05    ,
                S.Month06    ,
                S.Month07    ,
                S.Month08    ,
                S.Month09    ,
                S.Month10    ,
                S.Month11    ,
                S.Month12    ,
                S.Total        ,
                1
            FROM  [BlockHr] T RIGHT OUTER JOIN @BlockHr S
            ON S.ID = T.ID   
            WHERE (T.ID IS NULL)
                                           
                   
            EXEC [SPAD_TRX_LOG_SAVE] 0,'USER-COST CENTER','','MODIFY',1,'',NULL
            SET @P_RET_VAL =   1
      COMMIT TRANSACTION
      SET @P_RET_VAL = 1
    END TRY
    BEGIN CATCH
        SET @P_RET_VAL = -1
        ROLLBACK TRANSACTION
    END CATCH

DateTime Formats in SQL Server

The conversion of datetime data into many formats is everyday requirement. Here is a table of commonly used formats, to deal with datetime data.

For Eg., Use Convert function as follow

SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm

SQL Query to select a random row from a table

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic,but each database server requires different SQL syntax.

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Microsoft SQL Server 2008 Sys.Objects Catalog View object types

For Eg.,    Select * From Sys.Objects where type = 'P'

It returns the all the user defined stored procedure in the database.

I have listed below the various objects types of sysojects table.

type='AF'  'Aggregate function (CLR)'

type='C'  'CHECK constraint'

type='D'  'DEFAULT (constraint or stand-alone)'

type='F'  'FOREIGN KEY constraint'

type='FN'  'SQL scalar function'

type='FS'  'Assembly (CLR) scalar-function'

type='FT'  'Assembly (CLR) table-valued function'

type='IF'  'SQL inline table-valued function'

type='IT'  'Internal table'

type='P'  'SQL Stored Procedure'

type='PC'  'Assembly (CLR) stored-procedure'

type='PG'  'Plan guide'

type='PK'  'PRIMARY KEY constraint'

type='R'  'Rule (old-style, stand-alone)'

type='RF'  'Replication-filter-procedure'

type='S'  'System base table'

type='SN'  'Synonym'

type='SQ'  'Service queue'

type='TA'  'Assembly (CLR) DML trigger'

type='TF'  'SQL table-valued-function'

type='TR'  'SQL DML trigger'

type='U'  'Table (user-defined)'

type='UQ'  'UNIQUE constraint'

type='V'  'View'

type='X'  'Extended stored procedure'

SSRS Interview Questions

What are sub reports and how to create them? 
 A sub report is like any other reports which can be called in main report and can be generate
through main report. Parameters can be passed from main report to sub report and basis of
that report can be generated.

What is the report model project?
Report model project is for creating Adhoc reporting. You can create the adhoc reports
through report builder. Report model project can be created on bids or report server. This
model can have simple view.

What is report server project?
Report Server Project contains the RDL file and it need to be deployed on report server to
view the report files to application and user.
It a solution where we design our reports. You can add it by going into BIDS clicking on
new item and then selecting reports server project. Once the solution is created you can start
creating reports.

What is the report builder?
Report builder is used to create small reports and it a define interface. You can’t change the
report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project.

In which SQL Server version report builder introduced?
Report builder introduced in SQL Server 2005. While creating or deploying report model
project on report server you can get error or it might not get created. For this you need to
check whether the service pack 22 is installed or not.

How to deploy the Report?
Report can be deployed in three ways.
1. Using visual studio: In visual studio you can directly deploy the report through
solution explorer by providing the report server URL in project properties at Target
Server URL. This will deploy entire project or single report as per you selection.
2. Using report server: Can directly go to the report server and deploy the report by
browsing the report from the disk location on server.
3. Creating the utility: SQL server provides the utility using that which can be used to
create a customize utility for your report deployment in bulk.

What is RS.exe utility?
Rs.exe utility is used for deploying the report on report server. It comes with the report
server and can be customize accordingly.

What is the name of reporting services config file and what’s it’s used for?
Reporting service config file is used for report configuration details. It contains the report
format and also the report import types. Report service config reside at ISS.

What are the three different part of RDL file explain them?
In visual studio RDL files has three parts.
1. Data: It contains the dataset on which we write the query. Data set is connected with
data source.
2. Design: In design you can design report. Can create tables and matrix reports. Drag
columns values from source.
3. Preview: to check the preview after the report run.

Which language rdl files made of?
RDL files are written in XML.

What is the chart in report?
Chart reports are for graphical representation. You can get pie charts columns harts and
various other options.
3d charts are also available in reporting services.

What is Data Set in report?
Data set are the set of data which we want to show in report. Data creates on data source.
Data source is the source of data from where we are getting this data i.e. database server and
database name connection string.

What are the different types of data sources in SSRS?
SSRS use different data source. Some of them are listed below.
1. Microsoft SQL Server
2. OLEDB
3. Oracle
4. ODBC
5. SQL Server Analysis Service
6. Report Server Model
7. SAP Net weaver BI
8. Hyperion
9. Teradata
10. XML

What is the web service used for reporting services?
Reporting Service Web Service used in SSRS. By accessing this web service you can access all
report server component and also get the report deployed on report server.

How to add the custom code in Report?
To add the custom codes in report go to report tab on top then properties and there you will
find the options for custom code.