deleting duplicates

WITH CTE (sales_transaction_id, DuplicateCount)


AS

(

SELECT sales_transaction_id,

ROW_NUMBER() OVER(PARTITION BY sales_transaction_id ORDER BY sales_transaction_id) AS DuplicateCount

FROM fda_rpt..stg_rpt_location

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

Performance Tuning in SQL Server 2008

The query analyzer's estimated execution plan and suggestion for missing indexes is excellent. Bang on advise! :)

How to Extract Filename from Path

In SQL Server,
SELECT


REVERSE(
SUBSTRING(
REVERSE(FullFilePath),
0,
CHARINDEX('\', REVERSE(FullFilePath),0)
)
)
FROM FileSystem

In SSIS, use REPLACE (FullFilePath, Directory) to retrieve just the file name.

Setting up Netezza OLEDB Linked server in Sql server

Open Server Objects, Linked Servers, and Providers. Right-click on the NZOLEDB provider and choose Properties. Turn on the "Allow Inprocess" option. No, this is neither as secure nor as crash resistent as not turning it on. However, working is better than not working, and it doesn't work if it's not in process.

Thanks to http://weblogs.sqlteam.com/chrism/archive/2008/11/12/Netezza-and-OLE-DB.aspx

How to see recently executed queries in SQL Server?

Here is the query which we used to find out the recently executed queries along with the date and time at which they were executed






SELECT



DMExQryStats.last_execution_time AS [Executed At],



DMExSQLTxt.text AS [Query]



FROM



sys.dm_exec_query_stats AS DMExQryStats



CROSS APPLY



sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt



ORDER BY



DMExQryStats.last_execution_time DESC

Dropping multiple tables in 1 statement

I wanted to drop tables that begin with a certain prefix. I couldnt find it, but, as an alternative, found that we can drop multiple tables in a single command in SQL Server

Syntax:

drop table ,,

SSIS Parent package configurations

A good read --

http://consultingblogs.emc.com/jamiethomson/archive/2008/08/29/ssis-parent-package-configurations-yay-or-nay.aspx