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
Microsoft SQL Server Help
Microsoft SQL Server database help,articles and t-sql Tips, Business Intelligence, Interview questions and tutorials etc..
Wednesday, April 25, 2012
Tuesday, April 24, 2012
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)
--// 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)
Labels:
T-Sql
Wednesday, April 11, 2012
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
Labels:
T-Sql
Monday, January 9, 2012
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
For Eg., Use Convert function as follow
SELECT CONVERT(VARCHAR(20), GETDATE(), 108) As dtm
Labels:
Microsoft SQL Server,
T-Sql
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
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
Labels:
T-Sql
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'
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'
Labels:
T-Sql
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.
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.
Labels:
MSBI
Subscribe to:
Posts (Atom)