Search This Blog

Deleting Duplicate Data from NText Column in SQL

Deleting Duplicate Data from NText Column in SQL

Using Row_Number function we can delete the duplicate Data easily but when your column Data type in NText then you have to cast the Ntext data type to nvarchar first then we can delete the data.

Below we will create a sample table with duplicate data and will show how to delete duplicate data when column type is NText.

--table with duplicate row
CREATE TABLE #employee (ID INT PRIMARY KEY IDENTITY(1,1), Name NTEXT); 

INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Tom'); 
INSERT INTO #employee (Name) VALUES ('Hary'); 
INSERT INTO #employee (Name) VALUES ('Hary'); 

SELECT * FROM #employee; 


If you see the Output the Table has Name -Tom 3 times and Hary 2 times

Now we will use Row_Number function to delete the Duplicate Records.

--CTE to delete duplicate data 
WITH CTE AS ( 
SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS DupRow 
FROM #employee 

--select * from cte
DELETE FROM CTE WHERE DupRow > 1; 

Above query will throw an error

Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

So in order to delete data from Ntext column we have to cast or convert the Ntext column to nvarchar


--CTE to delete duplicate data having NText column
WITH CTE AS ( 
SELECT ROW_NUMBER() OVER(PARTITION BY CAST(Name AS NVARCHAR(4000)) ORDER BY id) AS DupRow,* 
FROM #employee 

DELETE FROM CTE WHERE DupRow > 1;

SELECT * FROM #employee;

Output

ID Name
1 Tom
4 Hary

Recursive CTE in SQL with Example

Recursive CTE in SQL with Example

A recursive CTE is defined by at least two queries (more are possible)—at least one query known as the anchor member and at least one query known as the recursive member.
The two queries are joined using Union all where the anchor member will run only once and recursive member will run repeatedly until it will returns empty result set.

Recursive CTE mostly used when returning a hierarchical list of employees, starting with the highest ranking employee in a company.

Below is the Example of Recursive CTE.


-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


if you see above table the table has Empname Empid with there Manager ID

Case I to display Manager Name of all the Employee with there the Title the Top Manager is Ken is the CEO of company who doesn't have any manager


--Below is the query displaying Employee and there Managers Names

select m.FirstName,m.Title ,m.EmployeeID,e.ManagerID,E.FirstName as mgrname,E.Title as MgtTitle  from MyEmployees E right  join MyEmployees M
on  m.managerid= e.EmployeeID 

Output

FirstName Title EmployeeID ManagerID mgrname MgtTitle
Ken Chief Executive Officer 1 NULL NULL NULL
David Marketing Manager 16 1 Brian Vice President of Sales
Mary Marketing Specialist 23 273 David Marketing Manager
Brian Vice President of Sales 273 NULL Ken Chief Executive Officer
Stephen North American Sales Manager 274 1 Brian Vice President of Sales
Michael Sales Representative 275 273 Stephen North American Sales Manager
Linda Sales Representative 276 273 Stephen North American Sales Manager
Syed Pacific Sales Manager 285 1 Brian Vice President of Sales
Lynn Sales Representative 286 273 Syed Pacific Sales Manager

Case II -Now we want to show Hierarchy of employees, starting with the highest ranking employee which is Ken then Brain along with there level 

So here we will use Recursive CTE.


If you see the above screenshot the Employee are in Hierachy along with with there manager Name

So below is the Recursive CTE to achieve Hierarchy of Employee.

with cte (firstname,title,EmployeeID,ManagerID,lvl)
as
(
--anchor member returning head of company
select firstname,title,EmployeeID,ManagerID,0 as lvl from MyEmployees where ManagerID is null
union all
--recursive member (will do recursion until reached to last level)
select e.firstname,e.Title,e.EmployeeID,e.ManagerID, lvl+1 from MyEmployees e   join cte on 
cte.EmployeeID =e .ManagerID )
select e.FirstName as ManagerName, M.* from cte M left join MyEmployees E 
on m.ManagerID =e.EmployeeID  

Output

ManagerName firstname title EmployeeID ManagerID lvl
NULL Ken Chief Executive Officer 1 NULL 0
Ken Brian Vice President of Sales 273 1 1
Brian David Marketing Manager 16 273 2
Brian Stephen North American Sales Manager 274 273 2
Brian Syed Pacific Sales Manager 285 273 2
Syed Lynn Sales Representative 286 285 3
Stephen Michael Sales Representative 275 274 3
Stephen Linda Sales Representative 276 274 3
David Mary Marketing Specialist 23 16 3

NOTE:By default SQL Server restricts the number of times that the recursive member can be invoked to 100. The code will fail upon the 101st invocation of the recursive member. You can change the default maximum recursion limit by specifying the hint OPTION(MAXRECURSION n) at the end of the outer query, where n is an integer.





Issue with Power Pivot-An error occurred while processing table 'Query'. The current operation was cancelled because another operation in the transaction failed.

Issue with Power Pivot-An error occurred while processing table 'Query'. The current operation was cancelled because another operation in the transaction failed.


Issue :OLE DB or ODBC error: Dec  1 2012 12:00AM; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

Issue Description:While Working with Excel Pivot Table The complex SQL query is working fine in SQL Management but when running the Same query in excel power pivot it was  giving the error.

The excel is validating and running  the query without any issue. but when saving the query throwing below error. 

OLE DB or ODBC error.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.


Reason:The Issue comes When working with SQL Complex queries and you are using an insert statement in your sql code.


Fix/Solution-Set Nocount On above the Insert Statement in the sql Code 

In my case i was inserting some records in temp table so added Set Nocount On above the Insert statement and No count Off after finishing Insert statement has fixed the issue.

--Below is the insert  Part of my SQL
WHILE @currct <= @Mct
       BEGIN
SET NOCOUNT ON
              INSERT INTO @MyTempTable1 (datetime, [ Role], Work)
                     SELECT DATEADD(MM, @currct, @FD) AS datetime
                           ,ru.[ Role]
                           , 0 AS Work
                           FROM Project AS ru
                         
              SET @currct = @currct + 1
              SET @CurDate = DATEADD (mm, 1, @CurDate)
 SET NOCOUNT Off
       END
  
   Adding Nocount on will fix the issue.

NOTE: If again  you will get the same error set NOCOunt on above your main Select query.

Add Row number to Select Query in SQL

You can add a row Number to sql Select query using Row Number Function 

Below is the table script with Example

CREATE TABLE #SQL
(
[Numbers] varchar(40)
)
INSERT INTO #SQL VALUES('One');
INSERT INTO #SQL VALUES('Two');
INSERT INTO #SQL VALUES('Three');
INSERT INTO #SQL VALUES('Four');
INSERT INTO #SQL VALUES('Five')

Case 1st -Showing Row Number in Select Query in SQL


select *,ROW_NUMBER()over( order by (select 1)) as Row_Num from #SQL

Output

Numbers Row_Num
One    1
Two    2
Three 3
Four 4
Five 5

Case 2nd Adding a Row Number column in Table

Just add an identity field column in the  table

alter table #sql
add Row_Num int Identity(1,1)

Select * from #SQL

Output

Numbers Row_Num
One    1
Two    2
Three 3
Four 4
Five 5

Opening SSRS drill through Report in a new Tab or Window

Opening SSRS drill through Report in a new Tab or Window


In order to Open a SSRS Drill through Report in a New Tab or window we have to use Javascript.

Case first-Report in new Tab without fields or  Parameters
Opening Drill down Report in a new tab without parameters and fields

First you have to Select the Drill through values textbox
Properties
Text box Properties 
Action
Write below javascript code
Inside javascript function add URL of the drill through Report

 ="javascript:void(window.open('http://abc.com/Reports/Testing/Drilldowntest.rdl ','_blank'))"
 
   This will open your Report in a new tab
 
   Case second-Passing Fields and opening in new Tab
 
   When you are Passing the Fields values ie (Text box values to the drill through Report)
   --Replace Report Name and with your Report Name and field Name with your drill through Report Parameter Name
 
    ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
 ','_blank')"

 DrilldownTest.rdl-Change to YOur REport Name
 Low-Replace it with Your Report drill through parameter Name
 Low.Value-Replace it your field Name

 Case Third Passing Fields as well as Parameters with IIF conditions and opening report in a new tab

  ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
&High="&Fields!High.Value &"
&Project=" &IIf(Parameters!Project.Value="","A",Parameters!Project.Value) & "
 ','_blank')"

  DrilldownTest.rdl-Change to YOur REport Name
 Low-Replace it with Your Report drill through parameter Name
 Low.Value-Replace it your field Name
 Same with High
 Project-Replace it with your Child or drill through Report Paramter Name
 Project.value-Project is your Main Report Parameter Replace it with your Main Report Parameter.value


 Note the above all javascript is to open Report in new tab if you want to open report in new window name the window and adjust the Properties as below

  ="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?"
&Globals!ReportFolder &"/Drilldowntest.rdl
&Low="&Fields!Low.Value &"
','RUWindow','left=370,top=180,width=940,height=300,status=no,menubar=no,scrollbars=no,location=yes'" & ")"

Rename failed for Database 'Testing_new'.

Rename failed for Database 'Testing_new'. 


Issue -Rename failed for Database 'Testing_new'
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)
Issue Description-While Renaming the Database in SQL using GUI get the above Issue

Solution-
To fix the above Issue
-Right click Database
-Properties
-Options
-Restrict Access
-Set to Single User
click ok

Once Done Rename the database 
by Right click
Rename
change the name to new Name-Testing_New
Once done 

Follow the same above process
-Right click Database
-Properties
-Options
-Restrict Access
-Set it back to Multi User
click ok

NOTE: we can also rename a database by  Running SP_RenameDB system Stored Proc.

--first parameter old db name second parameter new db name
exec SP_renamedb 'TestingNew' ,'Testing_New'