SQL Server – Finding duplicate records

Finding rows on a table is straight forward when filtering on columns, but what if you want to find duplicate data.

Let’s say I have a table that represents parents and children. The key to the table is the parent guid which is unique. Each row also has a child guid key that unique to the child, but is not unique in the sense that the child could have accidentally been added to the table twice.

Let’s look at the table:

SELECT * FROM parent_child

1

As you can see, David Doe was entered twice. How do you narrow your query to group like rows?

SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME

The SQL statement is selecting those columns that would be unique among duplicated rows. These columns are then added to a ‘GROUP BY’ clause to group like rows together. As you can see above, David Doe is in the table twice. We want to see David Doe once. The results are as follows:

3

But now you only want to return rows that are duplicate only. This is where the ‘HAVING’ clause comes in to play. By adding HAVING COUNT(*) > 1, only rows that are duplicated more than once will return in the result set.

SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME
HAVING COUNT(*) > 1

2

Now we see only the duplicate records, but you can take it a step further by saying show me the duplicates of the duplicate records. By placing the latter SQL code into a temporary table, you can then select the temporary table and join the original table to it where the child guid’s are equal as follows:

DECLARE @ParentChild TABLE
 (child_guid uniqueidentifier, FIRSTNAME varchar(50), LASTNAME varchar(50))
INSERT INTO @ParentChild(child_guid, FIRSTNAME, LASTNAME)
SELECT CHILD_GUID, FIRSTNAME, LASTNAME
FROM parent_child
GROUP BY CHILD_GUID, FIRSTNAME,LASTNAME
HAVING COUNT(*) > 1

SELECT parent_child.PARENT_GUID, temptable.child_guid, 
       temptable.FIRSTNAME, temptable.LASTNAME
FROM @ParentChild temptable
LEFT OUTER JOIN parent_child
     on parent_child.CHILD_GUID = temptable.child_guid

The results are as follows:

11

WordPress – Stop trackback spam

In Settings/Discussion, uncheck the box “

This will stop trackback for all future trackback spam.

To modify your older posts, you need to change the ping_status to “closed” on each row of your wp_posts table in your word press MySql database.

Here is how:

Log on to your cPanel and bring up phpMyAdmin.
Select your wordpress database.
Select your wp_posts table.

Execute the following SQL statement:

UPDATE wp_posts SET ping_status="closed";

Network – How to find out what is listening or using a port

I was trying to install software but it failed because the install stated that port 80 was in use. It didn’t say what was using it. I needed to find out so I could shut the application down in order to install my software. Come to find out, both skype and IIS were using the port.

In order to determine what was using the port, I brought up a command box and typed the following:

netstat -aon | findstr 0.0:80

-a = Display all connections and listening ports.
-n = Displays addresses and port numbers in numerical form.
-0 = Displays the owning process ID associated with each connection. (This is used  to locate the PID column in the processes tab of the task manager to find the process.
findstr = States I am only interested in port 80

The results?

TCP 0.0.0.0:80    0.0.0.0:0    LISTENING 3328

3328 is the PID. I then open the task manager, went to the processes tab, searched for PID = 3328 and stopped the process.

I then proceeded to successfully install my software.

Active Reports 6 – Enable Script Debugging and force a breakpoint using Debug.Assert(False) in a sub report

In order to debug active reports in the Visual Studio IDE solution, you need to set EnableScriptDebugging = True.

In this example, I want to debug a sub report. When creating a sub report dynamically in the main report that calls the sub report, after creating the ActiveReport object for the sub report,

 Dim _SubReport As New DataDynamics.ActiveReports.ActiveReport

set debugging to true like this:

_SubReport.EnableScriptDebugging = True

In Visual Studio, you can’t set breakpoints at design time inside a report, so if you are trying to debug a sub report knee deep in your main report, you would need to set a break point in your harness that calls the report:

mReport.Run()

You then would have to step through each line of code by hitting F8 until you enter the sub report you want to debug. But that could take forever!

There is faster way to this.

In your sub report, place the following line of code at the top of your script:

Debug.Assert(False)

When you run the main report (And you can remove the breakpoint on your mReport.Run() statement), this line of code ‘Debug.Assert(False)’ will cause an exception error. A dialog box will present itself. You must click RETRY.

After you click RETRY, the IDE will enter your sub report highlighted ‘green’ the statement ‘Debug.Assert(False)’  as an exception. Hit F8 once, and the next line of code is highlighted ‘yellow’ and you are now debugging.

At this time, you can set your breakpoints and walk through the code.