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

  

unable to connect to the remote server reporting services 2008

Hi All,
I wonder why reporting services keeps coming up with new errors every time I start it..anyways I got this error when I started the reporting services 2008 .
"unable to connect to the remote server reporting services 2008"

The fix for this is to set the Secure connection level to 2 in the rsreportserver.config file found at
C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Difference between Union and Union All in SQL

The main difference between UNION ALL and UNION is that, UNION only selects distinct values, while UNION ALL selects all values (including duplicates).

sp_linkedserver in sql

Hi All,
The below command is used for linking sql servers

EXEC sp_addlinkedserver @server = 'ppvs00982'

Adding matrix to SSRS reports

Hi All,
The below link gave me steps to add a matrix type report which includes the subtotals. I used to generate report weekwise with subtotals and totals

http://www.sqlservercentral.com/articles/Reporting+Services+%28SSRS%29/63415/

Create Drill Down reports in SSRS

Hi All,
I found the below link useful in creating the drill down reports in SSRS

http://www.youtube.com/watch?v=W6gk9VqnGMs

Macro to upload excel file to sharepoint

Hi All,
Okay only 30 minutes to log out..within that time was able to come up with a macro which will save an excel sheet to the sharepoint folder. Find below the macro


ActiveWorkbook.SaveAs Filename:= _
        "http://xxxxxxxxxxx.com/teams/analytical/Shared Documents/Test/Sharepoint1.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

If you want the excel sheet to be saved with the sheet name then below is the macro for it


Sub savesheet()
Dim FSN As String
FSN = ActiveSheet.Name & ".xlsm"
    ActiveWorkbook.SaveAs Filename:= _
        "\\texxxxxxxxxxxxxxxxx\teams\analytical\Shared Documents\Test" & FSN _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 End Sub

Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2005 or 2008

Hi All,
Today I encountered the below error when I tried to log on to SQL management studio database engine.

"Error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake" occurs when connecting to remote SQL Server databases on SQL Server 2005 or 2008"


This issue was easily resolved when I enabled the Named Pipe in the SQL configuration manager and then restarted the SQL services

Resize SSRS report page

Hi All,
When I deployed my First SSRS report, did not like the look of it. At first the reports came up in many pages and the window size was so small. I changed the size of Interactive size and Page size. Set all these values to 444 (445 should be the max) and bingo! I got all data in 1 page.

However the report manager still pointed out the old data..I realized that I have to upload the file again and them open the frame in new window. Copy this URL to your webpage from which you access the links\

You may also change the size by dragging the cells in the 'Design' mode in visual studio

An error occurred during client rendering.An error has occurred during report processing.Cannot create a connection to data source 'AW'.Login failed for user '

Hi All,
While configuring report manager I followed the below steps
Add New folder called 'Test'. Add new Data Source. Make sure that you fill the connection string. Upload new file which is your report file with an extension of .rdl.
When I was running this I got the below error message

An error occurred during client rendering.An error has occurred during report processing.Cannot create a connection to data source 'AW'.Login failed for user '


This error message indicates that the credentials applied is wrong..I played with varios options for the credentials and finally 'Windows Integrated Security' did the trick

You may try different credentials to make it work..Just wanted to let you know guys that this error message is related to credentials

The remote name could not be resolved - SSRS

Hi All,
Well today I guess things happened cos SSRS wanted me to learn some of its features....When I tried to run reports today, I got the error "The remote name could not be resolved". When I browsed for answers for this..The best solution was to change the computer name in reportconfig (The default location for this file on my development machine is C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager.)

When I checked for computer name, I noticed that the computer name was same but the domain name was different. Now since my computer is on the companies network, i,e domain name is set by the company..I did not have an option to change it...So I had to set SSL for this domain..
My server has vista on it..to set the SSL certificate..I opened IIS window>SSL certificate> Create Self signed Certificate> Specified test name as 'TestCert'> Click okay. Now SSL has picked the most recent domain name of the computer automaticaly.

Come back to reporting server, reconfigure the new SSL. While doing this it will certify the new SSL.

After this is done, I encountered the below errors


The request failed with HTTP status 401: Unauthorized.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
The remote name could not be resolved

This was resolved by following the below step
Set the "Service Account" in the SSRS report configuation tp "Network Service"
And then follow the below steps

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Right-click Lsa, point to New, and then click DWORD Value.
4. Type DisableLoopbackCheck, and then press ENTER.
5. Right-click DisableLoopbackCheck, and then click Modify.
6. In the Value data box, type 1, and then click OK.
7. Quit Registry Editor, and then restart your computer.




Bingo..my reporting manager was back....

Macro for TextToColumns

Hi All,
I have an excel sheet in which the row B contains consolidated data. The macro to seperate the data in this column is as given below..Ex data - Request:AD:Account:Activate:



Sub Macro1()
     '
     ' Macro1 Macro
    Columns("C:C").Select
    Selection.ClearContents
    Columns("B:B").Select
    Selection.Copy
    Columns("C:C").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo _
    :=Array(1, 1)
    Columns("C:C").Select
    Range("C2").Select
    Selection.Value = "L1"
    Columns("D:D").Select
    Range("D2").Select
    Selection.Value = "L2"
    Columns("E:E").Select
    Range("E2").Select
    Selection.Value = "L3"
    Columns("F:F").Select
    Range("F2").Select
    Selection.Value = "L4"
    Columns("G:G").Select
    Range("G2").Select
    Selection.Value = "L5"
End Sub

SSRS 2008 report manager was blank with no folders

Hi All,
Today I worked for 5 hours to resolve the issue. I had earlier worked on SSRS 2005 and I knew how the report manager would look. But in Vista when I installed 2008, I could see the report manager as below

Am happy to find the solution for this. Install IIS in Vista..I had trouble installing IIS..it gave me an error message ‘not able to install all features’..so i installed iis60rkt.exe from internet first and then ran the file and tried to install IIS from control panel again..This time I succeeded..I then clicked on the second link in the ‘Report manager’ URL with the IE or Firefox in administrator mode..Bingo..it worked

Note..I also refreshed the ‘Report manager URL’ by retying the same name in the Virtual directory and clicking on ‘Apply’

Macro to remove duplicates in an excel sheet and retain the latest or the last entry

Hi All,
The below macro will delete the duplicate entry in an excel sheet and it will only retain the latest or last entry made


Sub deleteDuplicate(Book2 As String)
   
    Dim cRow As Integer
    Dim cRow2 As Integer
    Dim MNOT As Integer
   
    Dim cCol As Integer
    Dim foundDuplicate As Boolean
 For MNOT = 1 To 500
    cRow = 2
    Do While IsEmpty(Worksheets(Book2).Cells(cRow, 1)) = False
        cRow2 = cRow + 1
        Do While IsEmpty(Worksheets(Book2).Cells(cRow2, 1)) = False
            foundDuplicate = True
            For cCol = 1 To 4
                If Worksheets(Book2).Cells(cRow, cCol).Value <> Worksheets(Book2).Cells(cRow2, cCol).Value Then
                    foundDuplicate = False
                    Exit For
                End If
            Next
            If foundDuplicate = True Then
                Worksheets(Book2).Rows(cRow).Delete xlShiftUp
            Else
                cRow2 = cRow2 + 1
            End If
        Loop
        cRow = cRow + 1
    Loop
 Next
End Sub
Sub test()
    deleteDuplicate "Sheet1"
End Sub

Macro to merge excel sheets in a sharepoint folder

This summary is not available. Please click here to view the post.