Could not install the custom actions
Learn how to fix the error “Could not install the custom actions” message Continue reading Could not install the custom actions
Javascript to Toggle Items
There are times when you will want to hide an item on a web page and activate the item only when the user clicks on an element. You can use a small bit of Javascript to display or hide the … Continue reading Javascript to Toggle Items
View All Permissions for Objects in SQL Server 2008
There are times when you need to audit user permissions to ensure that things aren’t getting inserted, over-written, or deleted. I looked around and found this script which uses several sys objects, which gives us most of the information we … Continue reading View All Permissions for Objects in SQL Server 2008
Overwrite files in a DTS using ActiveX
Several of my DTS jobs require processing a file and then archiving the file. As we all know, you can use ActiveX to Copy or Move a file. Problem is, sometimes we need to reprocess or reuse a file name. … Continue reading Overwrite files in a DTS using ActiveX
Delete old DTS packages in SQL Server 2008
Learn how to delete DTS packges using TSQL Continue reading Delete old DTS packages in SQL Server 2008
Using ActiveX to set query dynamically in SQL Server
Learn how to use ActiveX to dynamically change a query. Continue reading Using ActiveX to set query dynamically in SQL Server
Common Table Expressions (CTE) in SQL Server 2005 and Up
Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs at TechRepublic. Here meat of the article a sample:
|
1 2 3 4 5 6 7 8 9 10 11 |
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking) AS (SELECT Product , SaleDate , SalePrice , Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) FROM SalesHistory ) DELETE FROM SalesCTE WHERE Ranking > 1 |
Very easy and handy! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure there is a combination that will help your current situation. Please note, this is for SQL Server 2005 and above. Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up