Delete duplicate rows and keep unique rows in sql

Hi All,
I never understood the concept of cursor until today when I used to delete duplicate rows and keep unique rows in sql


DECLARE @A int
DECLARE @B int
DECLARE @C int
DECLARE @COUNTA int
DECLARE @COUNTB int
DECLARE @COUNTC int

DECLARE CUR_DELETE CURSOR FOR
SELECT A, B, C, COUNT(A), COUNT(B), COUNT(C) FROM G GROUP BY A, B, C HAVING COUNT(A) > 1 And COUNT(B) > 1 And COUNT(C) > 1

OPEN CUR_DELETE

FETCH NEXT FROM CUR_DELETE INTO @A, @B, @C, @COUNTA , @COUNTB, @COUNTC
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@COUNTA -1) FROM g WHERE A = @A
DELETE TOP(@COUNTB -1) FROM g WHERE A = @B
DELETE TOP(@COUNTC -1) FROM g WHERE A = @C

FETCH NEXT FROM CUR_DELETE INTO @A, @B, @C, @COUNTA , @COUNTB, @COUNTC
END

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE


Enjoy!

Cannot define PRIMARY KEY constraint on nullable column in table

Hi All,
I used the below to alter table attributes



To add primary key
ALTER TABLE CSAT
ADD PRIMARY KEY ([Sl No])

While doing so, I got the below error
Cannot define PRIMARY KEY constraint on nullable column in table

So I used the below

ALTER TABLE CSAT ALTER COLUMN [Sl No] float NOT NULL


Viola

Hi All,
Tip in SSAS
To get the calculated field in SSAS, use 'float' as datatye in your SQL management studio

Hi All,
Today I ran a simple query to pull maximum value from three columns in a table and it is as below


select max(Name) As Name
from
(
Select MAX(A) as name from G Union All Select MAX(B)as name from G  Union All Select MAX(C)as name from G
) S

Enjoy!

New role assignment ti view SSRS reports

Hi All,
Today I gave access to my reports to my manager by adding the below

 <domain>\<account>.

under security tab of my main folder and make sure the reports in the folder have the same security type

Month does not come in order in SSRS tablix report

Hi All,
When I was trying to get a sparkine report for monthwise data using the matrix report in report builder 3.0 and SSRS....i would not get the right order....i,e instead of Aug, Sep, Oct and Nov....I would get Sep, Nov, Oct and Aug.
After 2 days of struggle for this issue I finally found a link which gave a solution for this and the solution is listed below

In textbox which binds to Date fireld, I changed its expression to =MonthName(Month(Fields!QDate.Value), False). Now, Month name is displayed in table.  Also I changed group's group and sort expression to =Month(Fields!QDate.Value).

The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded.

Hi All,
I realised that getting sparkline in Visual studio 2008 is not so easy. So I downloaded report builder 3.0 and run the table with sparkline. I was happy that I was able to run it.
But when I tried to save this report and access it through visual studio 2008 it would give me the below error.

"The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded."

To resolve this issue..I followed the below steps
1. Open another running report xml code in visual studio and copy the first 2 lines ..I had the below


<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

I later got the error message as stated below

"Deserialization failed: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' has invalid child element 'ReportSections' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'. List of possible elements expected: 'Description, Author, AutoRefresh, DataSources, DataSets, Body, ReportParameters, Code, Width, Page, EmbeddedImages, Language, CodeModules, Classes, CustomProperties, Variables, DeferVariableEvaluation, ConsumeContainerWhitespace, DataTransform, DataSchema, DataElementName, DataElementStyle' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as well as any element in namespace '##other'. Line 4, position 4."

For this deleted the lines that contained 

<ReportSections>

    <ReportSection>

   and </ReportSections>

    </ReportSection>

Bingo!

I was able to open the reports made in report builder 3.0 in Visual studio 2008