Browsing Tag
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
Learn how to delete DTS packges using TSQL Continue reading Delete old DTS packages in SQL Server 2008
Learn how to use ActiveX to dynamically change a query. Continue reading Using ActiveX to set query dynamically in SQL Server
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: ;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 … Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up
Now, you can use ActiveX to check the age of a file before proceeding. The code assumes that you have already assigned the file location elsewhere in the DTS Package Continue reading Using ActiveX to check the age of a file in SQL Server
From time to time, you will need to dynamically set a variable in the middle of your DTS packages. The following code shows you how easy it is to build a variable dynamically. The variable will be filename which contains … Continue reading Using ActiveX to Dynamically set a Variable in an SQL Server DTS Package
Here are a few useful code fragments that I use all the time. You see too many Rows Affected Messages. First, NOCOUNT. If you don’t want SQL Server to return the Row Count (20 row(s) affected) after you execute a query, here is the code: — Turn off the rows affected message SET NOCOUNT ON Once you have executed your queries, you can enable the row count again SET NOCOUNT OFF ANSI Warnings interrupt your inserts Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command. SET ANSI_WARNINGS OFF If there … Continue reading Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server
