≡ Menu

Deleting Duplicate Rows in a SQL Server Table Using a CTE

Finding duplicate rows in a table is quite simple. Deleting only the duplicates – as opposed to deleting all rows that have duplicates – is a bit more complicated.

I have a table variable called “@Readings” with the following schema:

DECLARE @Readings TABLE
(
	TagName VARCHAR(100),
	ScanTime DATETIME,
	TagValue NUMERIC(10,3)
)

First, I load the table with data from another database on a linked server called “OtherDB”:

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT DISTINCT tag,[time],value
	FROM 
	OPENQUERY(OtherDB,'select tag, time, value from archive.comp where (tag LIKE ''%test%'') AND time = DATE(''*'') ')

Then, I load that same data with some updates, both to the ScanTime column, and then to the TagValue column – always holding the TagName column constant.

DECLARE @DateTimeStamp DATETIME
SET @DateTimeStamp = GETDATE()

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT TagName,@DateTimeStamp,TagValue FROM @Readings

INSERT INTO @Readings(TagName,ScanTime,TagValue)
SELECT TagName,ScanTime,12345 FROM @Readings

To see what rows duplicate the TagName column alone, this query will suffice:

SELECT TagName FROM @PI_Readings GROUP BY TagName HAVING ( COUNT(*) > 1 )

By adding column names, you can look where the duplication is the TagName and the ScanTime:

SELECT TagName, ScanTime FROM @PI_Readings GROUP BY TagName, ScanTime HAVING ( COUNT(*) > 1 )

(If I were to add also the TagValue column, I would get zero rows returned, since there are currently no duplicates where all three columns are identical.)

So, what if you wanted to delete only the duplicates where TagName and ScanTime matched, irrespective of the TagValue?

The easiest way I have found is to use what is called a Common Table Expression, or CTE.

To use a CTE to delete these duplicates let’s first construct it to only show the duplicates:

WITH CTE AS
(
	SELECT TagName, ScanTime, TagValue,
	RN = ROW_NUMBER()OVER(PARTITION BY TagName, ScanTime ORDER BY TagName, ScanTime)
	FROM @Readings
)
SELECT * FROM CTE WHERE RN > 1

To now delete the offending rows, change the last line from a SELECT statement to a DELETE statement:

CTE example

When creating the CTE statement, make sure there is a semicolon terminating the previous statement. This is not usually required in SQL Server, but it is in this case.

Another good example of this is on Stack Overflow.

While I do like SQL Fiddle, it seems that the DELETE function does not work on CTEs there, though SELECT statements do.

Using Dependency Walker to Find Missing Dependencies

I was notified that a little-used application within my organization had begun displaying an error instead of allowing the users to log in via Citrix.

ActiveX control error

This ActiveX control, which was integral to the operation of the application I was servicing, had come from a very old version of Crystal Reports. Apparently, someone had recently installed a full version of Crystal Reports on this server and it had deleted the older OCX file.

I found a copy of the OCX file, copied it to the C:\Windows\SysWOW64 folder (as it was a 32-bit control) and attempted to run Regsvr32 on it to register the file.

This was the result:

Regsvr32 error

Apparently the installation of Crystal Reports also deleted some files that the ActiveX control needed to be registered! Fortunately, I found a free program called Dependency Walker that can show missing dependencies on DLLs, OCXs, and some other file types.

Installation and running of the program is very straightforward. When running it on a copy of the OCX file on my desktop, this was the result:

Dependency Walker example

As you can see from the circled area, two dependencies were missing: crpe32.dll and olepro32.dll. Olepro32.dll did exist in the SysWOW64 folder, so I copied it to the desktop, when running again, only crpe32.dll was missing. This file did not exist in SysWOW64, but I found it in the Crystal Reports installation folder. By copying crpe32.dll to SysWOW64, I was able to register the OCX file there, and my application began working again!

Moving a Legacy Citrix-Based Application to a Current Version of Windows

In my last post, the problem I experienced was with an old application (which I’ll call “App #1”) written in PowerBuilder 6.5, and accessed via Citrix.

Today, a similar type of application (“App #2”) is the source of the problem, and the fact that it’s being moved to the same new server made the cause of the problem even more difficult to discern.

Both applications connect to the same Microsoft SQL Server instance. After moving App #1, the problem was connecting to an Oracle Database via ODBC. Connecting to SQL Server worked fine.

However, App #2, while ostensibly identical in its method of connecting to the database, threw an error when trying to connect via the new Citrix server.

error dialog box

This is the same error given if one enters an incorrect username and password, so it’s not the most helpful notification to receive. Also, this application has no error log, and does not send errors to the Windows Event Viewer. App #2 is basically a black box.

I decided to check whether or not XP Compatibility mode would affect how App #2 ran on the new server. Though App #1 was unaffected by this, something had to be different and this should be easy enough to check.

Because the application ran from a network drive rather than locally on the Citrix server, checking the Properties on the executable was not fruitful. The entire Compatibility tab was grayed out.

Compatibility tab on Properties dialog box for app on network drive

By copying the application executable to the server, I was then able to set the Compatibility Mode selector for Windows XP SP 3. The application ran perfectly! This did not completely solve my problem though – this app was run by Citrix via batch file rather than an app shortcut.

I figured there must be a way to set the Compatibility Mode within the batch before executing via start.exe.

After digging a bit, I did find several options for doing this. Though the method preferred by many involved adding registry keys, I chose the simplest route.

Adding this line to the batch file just before the call to the application executable solved my problem!

set __COMPAT_LAYER=WinXP

User Cannot Access an ODBC System DSN

In moving a Citrix-based application from one server to another, I discovered in testing that users who were not in the local Windows Administrators group on the Citrix server were no longer able to connect to an ODBC System Data Source Name (DSN) when presented with the connection dialog box.

Though a box similar to the one below (except populated with Data Sources) did appear with all expected Data Sources present, the users received an error when selecting a Data Source and clicking OK.

ODBC Data Source Administrator

Though permissions for ODBC Data Sources can be set using Group Policy, when that is done, a user would not have been able to even see the list in the Data Source Administrator.

I discovered that the NTFS permissions on the folder containing the Oracle ODBC driver (in this case, C:\oracle\product\11.2.0\client_1) had been changed not to inherit from the parent folder. As a result, the local Users group was absent from the Access Control List.

By reapplying the inheritance to the folder in question and its subfolders, I was able to restore access to the ODBC System DSN to the application users.