Todd Carrico

This is some code I use for removing duplicate rows from a table based on Primary Keys.  It could be easily modified for other scenarios.  The end result is a clean table.

CREATE TABLE #Dups(
    PkUniquevalue INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
    cnt INT NOT NULL,
    BannerID INT
)

INSERT INTO #Dups ( cnt, BannerID )
SELECT
    COUNT(*),
    BannerID
FROM dbo.Banner
GROUP BY BannerID
HAVING COUNT(*) > 1

IF @@ROWCOUNT > 0
BEGIN

    DECLARE @PkUniquevalue INT
    DECLARE @RowsToKill INT 
    DECLARE @BannerID int 

    SET @PkUniquevalue = 0 

    WHILE (1=1) 
    BEGIN 

        SELECT TOP 1 
            @RowsToKill = cnt - 1, 
            @BannerID = BannerID, 
            @PkUniquevalue = PkUniquevalue 
        FROM #Dups 

        IF @@ROWCOUNT > 0 
        BEGIN 
            SET ROWCOUNT @RowsToKill 
            DELETE FROM dbo.Banner
            WHERE BannerID = @BannerID 
            SET ROWCOUNT 0 
        END 
        ELSE 
        BEGIN 
            BREAK 
        END 
        DELETE FROM #Dups WHERE PkUniquevalue = @PkUniquevalue 

    END    
END

I recently had an issue automating the creation of SQL Agent Jobs.  I need to set the Log File properties of the job steps, and was setting the path to a local share on the SQL Server.  No problem you say:

'\\' + @@SERVERNAME + '\Logs' 

should do the trick.

The problem came when I moved the code to a named instance of SQL Server.  @@SERVERNAME returns "<ServerName>\<InstanceName>".  So I briefly thought about parsing @@SERVERNAME, and figured there had to be a better way.  As it turns out, there is; it is called SERVERPROPERTY.

'\\' + CONVERT(NVARCHAR(128), SERVERPROPERTY('MachineName')) + '\Logs'

SERVERPROPERTY returns a sql_variant, so you might have to cast it into something you can use without type conversion issues.

The information you can get with SERVERPROPERTY is interesting:

• Collation
• Edition
• Engine Edition
• InstanceName
• IsClustered
• IsFullTextInstalled
• IsIntegratedSecurityOnly
• IsSingleUser
• IsSyncWithBackup
• LicenseType
• MachineName
• NumLicenses
• ProcessID
• ProductVersion
• ProductLevel
• ServerName