SQL Server - XML - Error - "The FOR XML clause is not allowed in a INSERT statement"

When we try to insert into column of XML datatype,the result set from (SELECT * FROM TABLE FOR XML RAW/AUTO/PATH),we will end up with following error:

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.

Two methods to overcome the above error :


--Sample data

CREATE TABLE TEST_TABLE(Col1 INT IDENTITY(1,1),Col2 VARCHAR(20))
INSERT TEST_TABLE SELECT 'SQL Server 2000'
INSERT TEST_TABLE SELECT 'SQL Server 2005'
INSERT TEST_TABLE SELECT 'SQL Server 2008'
INSERT TEST_TABLE SELECT 'SQL Server 2008R2'
INSERT TEST_TABLE SELECT 'SQL Server 2012'

 


SELECT * FROM TEST_TABLE
SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE



CREATE TABLE TEST_XML(Column_XML XML)

--let us try to insert
 
INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.


--again,let us try to insert

INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.




Method 1:

DECLARE @VAR VARCHAR(MAX) = 'SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE'
INSERT TEST_XML EXEC (@Var)


SELECT * FROM TEST_XML

Method 2:

INSERT TEST_XML SELECT(SELECT * FROM TEST_TABLE FOR XML AUTO)


SELECT * FROM TEST_XML



 

SQL Server 2008 - Audit SELECT performed on particular table


Trigger will fire for INSERT/UPDATE/DELETE  statements,not for SELECT statement

So SQL Server Profiler was used as the only way to audit SELECT statements,prior to SQL Server 2008.

SQL Server 2008 Enterprise edition helps us to audit SELECT statement performed on a particular table as shown in below example:

Step 1:

           Under Object Explorer,create a new Audit object as shown in below image:





Step 2:

               Mention Audit name,configure Audit settings as needed and Audit log destination type and destination path,if file is selected as Audit destination.



SQL Server - Dynamic Pivoting

In this post, let us see an example for dynamic pivoting.



CREATE  TABLE TMP(DBName NVARCHAR(30),ID INT)
INSERT INTO TMP SELECT 'SQL Server',1
INSERT INTO TMP SELECT 'Oracle',2

--Example for dynamic pivot

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+DBName+']' FROM
 (SELECT DBName FROM TMP ) AS T FOR XML PATH('')),1,2,'') 
   
SET @query = N'
SELECT ' + @Columns + '
FROM
(
  SELECT DBName,ID FROM TMP
)  i
PIVOT
(
  MAX(ID) FOR DBName IN ('
  + @Columns
  + ')
)  j;';
EXEC sp_executesql @query;


SQL Server - SET DATEFIRST and @@DATEFIRST

SELECT * FROM sys.syslanguages

Above query will return resultset(date & language details) as shown in below image:


--@@LANGUAGE will give us the language detail for the current session
--@@DATEFIRST will give us the "first day of a week" value for the current session
--for language 'us_english', default value (first day of a week) = 7

 
SELECT @@LANGUAGE Currentsession_Language
SELECT @@DATEFIRST Defaultvalue_for_usenglish

--to override the default value (first day of a week) = 6 for this current session alone,use SET DATEFIRST

 
SET DATEFIRST 6
SELECT @@DATEFIRST Overriddenvalue_first_day_of_week 



SQL Server - Split CSV into separate row values

In this post, let us see how to split delimited string into separate rows using User defined function.

--Example for splitting CSV into separate row values


SQL Server - Combine separate row values as CSV

In this post, let us see how to combine values from separate rows into delimited string.
Example 2 in this post, shows how to combine values from separate grouped records into delimited string.

Example 1:





--Examples for Combining separate row values as CSV 

DECLARE @csv TABLE (ID INT,
Name VARCHAR(30))
INSERT INTO @csv SELECT 1,'SQL Server'
INSERT INTO @csv SELECT 2,'Oracle'
INSERT INTO @csv SELECT 3,'DB2'
SELECT * FROM @csv

 
--Using COALESCE 
DECLARE @V VARCHAR(100)
SELECT @V = COALESCE(@V+', ','' )+Name FROM @csv 
SELECT @V CSV

 
--Using STUFF & XML PATH
SELECT STUFF( 
 (SELECT  ', ' + Name FROM 
   (SELECT Name FROM @csv ) AS T FOR XML PATH('')),1,2,'') AS CSV

 
--Using SUBSTRING & XML PATH
SELECT SUBSTRING(
 ( SELECT (', ' + Name)
  FROM @csv
  FOR XML PATH('')
 ) , 3,2000) 
AS CSV





SQL Server - Unique index to allow multiple NULL

UNIQUE Constraint/Index :

Both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness.

Unlike PRIMARY KEY constraints, UNIQUE constraints allow the value NULL,however only one null value is allowed per column. 

--Example for unique index to allow multiple NULL

CREATE TABLE Unique_Duplicate (Col1 INT,Col2 VARCHAR(100))
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 IS NOT NULL
GO
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
GO
SELECT * FROM Unique_Duplicate
GO
---------------------------------------------------------
--Example Unique index to allow one particular duplicate value

SQL Server 2012 - IIF ( ) and CHOOSE ( )

IIF - Returns one of two values, depending on whether the Boolean expression evaluates to true or false.

Syntax:
IIF ( boolean_expression, true_value, false_value )
 
Examples :


SELECT IIF(1>2,'TRUE','FALSE')
GO
 -- output: FALSE 
 
 
 
SELECT IIF(2>1,'TRUE','FALSE')
GO 
 -- output: TRUE
 
 
SELECT IIF(1>2,NULL,NULL)
GO 
 -- Msg 8133, Level 16, State 1, Line 1
-- At least one of the result expressions in a CASE specification must be an expression other than the NULL constant. 
 

DECLARE @Exp CHAR(5)
SELECT IIF(1>2,NULL,@Exp)
GO
 -- output: NULL

SQL Server 2012 - THROW - Enhancement in error handling using TRY CATCH


THROW  statement can be used to raise an exception,like RAISERROR

Syntax : 

THROW [ { error_number | @local_variable },
        { message | @local_variable },
    { state | @local_variable }
] [ ; ]

error_number
 error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.
message
message is nvarchar(2048).
state
 between 0 and 255 that indicates the state to associate with the message. state is tinyint.

EXAMPLES:

-- Exceptions raised by THROW statement will always have a severity of 16

THROW 50000, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
THROW 2147483647, 'Exceptions raised by THROW statement will always have a severity of 16 ', 1;
 
--The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
USE AdventureWorks2012

CREATE PROCEDURE THROW_Example
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
DELETE FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1
END TRY

BEGIN CATCH
SELECT 'The statement before the THROW statement must be followed by the semicolon (;) statement terminator';
THROW
END CATCH

END
GO
EXECUTE THROW_Example

GO



 For more information  & differences between the RAISERROR and THROW statements - http://msdn.microsoft.com/en-us/library/ee677615.aspx
Structured Error Handling Mechanism in SQL Server 2012 -  http://social.technet.microsoft.com/wiki/contents/articles/20002.structured-error-handling-mechanism-in-sql-server-2012.aspx 

SQL Server 2012 - TRY_CONVERT( ) -Data type conversion function


TRY_CONVERT function tries to convert the values passed to it ,into a specified data type.


 If the conversion is successful then it will return the value of the specified data type,

 else it will return a NULL value.


Syntax :


TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
 
data_type [ ( length ) ] - The data type into which to cast expression.
                          
expression - The value to be cast         

style  -    Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.



Examples for TRY_CONVERT( ):

 USE AdventureWorks2012

--using TRY_CONVERT() , to convert integer to character 
 
SELECT TRY_CONVERT(NVARCHAR(1),1)
GO


--if the conversion fails , CONVERT ( ) will return error, 
TRY_CONVERT( ) will return NULL

SELECT CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'M' to data type int.

SELECT TRY_CONVERT(INT,Gender) FROM [HumanResources].[Employee] WHERE BusinessEntityID = 1


--sufficient data type length is not specified ,CONVERT ( ) will return error, TRY_CONVERT( ) will return NULL

SELECT CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7

Msg 6354, Level 16, State 10, Line 1
Target string size is too small to represent the XML instance

SELECT TRY_CONVERT(varchar(100),Instructions) FROM [Production].[ProductModel]  WHERE ProductModelID = 7


--third argument in TRY_CONVERT( ) i.e ., style accepts the same values as the style parameter of the CONVERT ( ) function.

SELECT TRY_CONVERT(NVARCHAR(30),getdate(),107)
SELECT TRY_CONVERT(NVARCHAR(30),getdate(),104) 



--TRY_CONVERT returns an error when the cast is explicitly not permitted.


SELECT TRY_CONVERT(xml, 10) AS Result;
GO

     Msg 529, Level 16, State 2, Line 1
     Explicit conversion from data type int to xml is not allowed.
 
 

SQL Server 2012 - CONCAT() - String function

CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values.

The CONCAT function requires a minimum of two input values, else the concatenation fails.

SYNTAX: - 
                   CONCAT (string_value1, string_value2 [,string_valueN])


In previous versions of SQL Server, we had plus symbol " +  "  to concatenate values.
Even in SQL Server 2012, we can use plus symbol " +  "  to concatenate values.

CONCAT( )  function overcomes data type conversion problem while concatenating values of different data types, in a simple way by implicitly coverting all arguments to string types before concatenating the inputs.

Null values are implicitly converted to an empty string. 

If all the arguments are null, an empty string of type varchar(1) is returned. 

Examples for CONCAT( ) function:


USE AdventureWorks2012
--Concatenating using plus symbol "+" 
SELECT JobTitle + '|' + HireDate FROM  [HumanResources].[Employee]
GO



Msg 402, Level 16, State 1, Line 1
The data types nvarchar and date are incompatible in the add operator.

SQL Server 2012 - OFFSET and FETCH (Enhancement in ORDER BY clause)

Sqlserver 2012 introduced OFFSET and FETCH clause to limit the number of rows returned by a query,after sorting the rows using ORDER BY clause.

 OFFSET clause sets how many rows needs to be skipped  
 FETCH NEXT n ROWS ONLY, displays the next n records

Note :  OFFSET and FETCH clause can be used only with ORDER BY clause

Examples for understanding  OFFSET  & FETCH clause 

USE AdventureWorks2012

--Example 1: - using only
OFFSET  with  ORDER BY clause
--In below example,after sorting rows in ascending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID
     OFFSET 1 ROW
--Example 2 - In below example,after sorting rows in descending order,first row is skipped.

SELECT BusinessEntityID,
       NationalIDNumber,
       JobTitle,
       MaritalStatus,
       Gender,
       BirthDate,
       HireDate
FROM  [HumanResources].[Employee]
ORDER BY BusinessEntityID DESC
     OFFSET 1 ROW
 
--Example 3 - In below example,after sorting rows ,first row is skipped & next 10 rows are fetched.   

SQL Server 2012 - WITH RESULT SETS

What is WITH RESULT SETS  in SQL Server 2012 ?

                            WITH RESULT SETS  provides flexibility in getting output of stored procedure in two ways :

                              1.) Column names returned as the result of stored procedure execution can be changed
                              2.) Data type of columns returned as the result of stored procedure execution can be changed


                             WITH RESULT SETS can be used only at the time of executing stored procedure.

Examples for understanding  WITH RESULT SETS :

USE AdventureWorks2012

--Example 1 

CREATE PROCEDURE With_Resultset
--Procedure to get top 5 married employee details
AS
BEGIN

        SELECT TOP 5 
               BusinessEntityID,
               NationalIDNumber,
               JobTitle,
               Gender,
               HireDate 
        FROM [HumanResources].[Employee] WHERE MaritalStatus = 'M'

END

--  actual column names returned by stored procedure are changed using WITH RESULT SETS

EXECUTE With_Resultset
WITH RESULT SETS
  (
      (
               BusinessID    INT,
               NationalID    NVARCHAR(20),
               Designation   NVARCHAR(100),
               Sex           NCHAR(10),
               DateOfJoining DATE
      ) 
  ) 
GO







--Example 2 - Only compatible data type conversion is possible

-- In below example - column NationalID data type NVARCHAR(20) is converted to INT

SQL Server - Remove XML tags from XML document and combine values of XML nodes into single paragraph

Example -  Remove XML tags from XML document and convert values of XML nodes into single paragraph  


USE AdventureWorks2012

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS  "RES")
UPDATE [AdventureWorks2012].[HumanResources].[JobCandidate]
SET Resume.modify('
  insert(<?xml-stylesheet href="Resume.xsl" type="text/xsl"?>)
  before(/RES:Resume)[1]')
WHERE JobcandidateID  IS NOT NULL;

Try the below query,copy the XML document and paste it into notepad and save it as .xhtml as shown in below image:

SELECT Resume FROM [AdventureWorks2012].[HumanResources].[JobCandidate]



Now open the saved file ,you will notice as shown in below image :


SQL Server - Database Read - Only

After attaching sample database - AdventureWorks2012,I noticed the database in Read-Only state as shown in below image :



SQL Server Integration Services - DelayValidation property



Package and individual tasks are validated during design phase itself,to delay the validations till execution phase ,there is an option under Properties - > DelayValidation

We can set DelayValidation property to True/False at package/individual tasks level

By default , DelayValidation property  will be set to False



Example for DelayValidation property:

In this example,

1.)we are going to create a  Table(named “Test_DelayValidation”) and insert  some records using Execute SQL Task


2.)In the Data Flow Task, data from  Table - “Test_DelayValidation”  is moved to flatfile using OLE DB Source  & FlatFile Destination


SQL Server Integration services - Rename and move files from source folder to destination folder

In this post, I am going to explain about renaming & moving processed files from active folder to archive folder using Foreach Loop Container and File System Task


Step 1:


Under Toolbox -> Control Flow Items, drag&drop Foreach Loop Container into Control Flow tab as shown in below image






Step 2:

Double-click  Foreach Loop Container, under Collection tab do the changes as shown in below image:

SQL Server Reporting Services - Example for Subreport and Drill-down report

In this post,let us see simple example for subreport & drill-down report


What is Subreport in SSRS?

Subreport is nothing but child report created to display the data 's based on data displayed by parent report.



/*Sample Data*/

CREATE DATABASE TECH_ORG

USE TECH_ORG
GO

CREATE TABLE Employee(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
DEPTID INT,
DEPT_Name VARCHAR(30))

INSERT INTO Employee SELECT 1,'Sathya','Programmer',1,'Database'
INSERT INTO Employee SELECT 2,'Sathish','Senior Programmer',1,'Database'
INSERT INTO Employee SELECT 3,'Praveen','Programmer',2,'Java'
INSERT INTO Employee SELECT 4,'Arun','Senior Programmer',2,'Java'
INSERT INTO Employee SELECT 5,'David','Programmer',3,'Flex'
INSERT INTO Employee SELECT 6,'Mike','Senior Programmer',3,'Flex'
INSERT INTO Employee SELECT 7,'Sunny','Programmer',1,'Database'
INSERT INTO Employee SELECT 8,'Hanshika','Programmer',1,'Database'



CREATE TABLE Employee_PersonalDetails(
EMPID INT,
EMP_Name VARCHAR(30),
Designation VARCHAR(30),
Gender VARCHAR(10),
Age SMALLINT,
Location VARCHAR(30))

INSERT INTO Employee_PersonalDetails SELECT 1,'Sathya','Programmer','Male',23,'Chennai'
INSERT INTO Employee_PersonalDetails SELECT 2,'Sathish','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 3,'Praveen','Programmer','Male',26,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 4,'Arun','Senior Programmer','Male',28,'Pune'
INSERT INTO Employee_PersonalDetails SELECT 5,'David','Programmer','Male',30,'Delhi'
INSERT INTO Employee_PersonalDetails SELECT 6,'Mike','Senior Programmer','Male',29,'Banglore'
INSERT INTO Employee_PersonalDetails SELECT 7,'Sunny','Programmer','Female',22,'Punjab'
INSERT INTO Employee_PersonalDetails SELECT 8,'Hanshika','Programmer','Female',21,'Kolkata'

/*Sample Data*/






If you are new  for creating SSRS report & not familiar with creating datasources and datasets,

Step 1: Create ParentReport - ParentReport.rdl,with dataset using below query as shown in below image:

SQL Server 2008 - Table and Database Designers


In this post,i am going to explain about,how to automatically generate scripts ,when a table is created/altered through Design option(New Table...) in SSMS ,

& how to automatically prevent table definition changes,if you try to alter the table through
 Design option in SSMS

In SSMS 2008,Goto   Tools -> Options...





  


















Tools -> Options -> Table and Database Designers -> Auto generate change scripts

Look at the below image,


If you check this option - "Auto generate change scripts" - and then if you try to create /alter

table through Design option(New Table...) in SSMS,it will automatically prompt for saving the table definition scripts.

























 Example:Auto generate change scripts

Under Object Explorer,expand database and then right-click Tables - > New Table...
as shown in below image.











 

















Create a new table and then try to close the designer query window ,a screen will pop-up as shown in below image.



































Tools -> Options -> Table and Database Designers -> Prevent saving changes that require table re-creation


If you check this option - "Prevent saving changes that require table re-creation" - and then if you try to alter the existing

table definition through Design option in SSMS,it will automatically prevent from altering the table definition as shown in below image.




SQL Server - DBCC PAGE and DBCC IND

DBCC IND – used to know the page details of tables & indexes.

Syntax:
DBCC IND ( {dbname}, {table_name},{index_id} )

Description of columns displayed from DBCC IND

PageFID
File ID of Page

PagePID
Page ID

IAMFID,IAMPID
File ID&Page ID -> IAM mapping


ObjectID
 object ID(Table)

IndexID
Clustered index (IndexID = 1),Non clustered index (IndexID = 2)

PartitionNumber
 Number of Partition which holds data & index pages

PartitionID
ID of Partition which holds data & index pages

iam_chain_type
In-row data,Row-Overflow data

PageType
1=Data page,2=Index page,3&4=Text page,10=IAM

IndexLevel
0=leaf level

NextPageFID,NextPagePID,PrevPageFID,PrevPagePID
Next & Previous Page ID 's & File ID 's of a page




 

DBCC PAGE – used to see the contents of  a Page.

Syntax:
DBCC  PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )


DBCC PAGE & DBCC IND both are undocumented commands.

Few examples for DBCC PAGE & DBCC IND: