Monday, November 16, 2009

How to remove Duplicate Records using SQL Server 2005

By using a Common Table Expression (CTE) in SQL Server 2005 we can easily remove duplicate records from a table, even when the table without a primary key.

The windowing function named DENSE_RANK is used to group the records together based on the Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close] and Volume fields, and assign them with a value randomly. This means that if I have two records with the exact same Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close] and Volume values, the first record will be ranked as 1, the second as 2, and so on.

;WITH DupliRows(Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume, Ranking)

AS

(

SELECT

Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume,

Ranking = DENSE_RANK() OVER(PARTITION BY Symbol, MarketType,Trading_Date , Trading_Day,[Open],High,Low,[Close],Volume ORDER BY NEWID() ASC)

FROM dbo.tbl_Futures ---The Table from where duplicate records will be remove
)

DELETE FROM DupliRows
--Select * from DupliRows
WHERE Ranking >1


Because a CTE acts as a virtual table, We able to process data modification statements against it, and the underlying table will be affected. In this case, We can remove any record from the DupliRows that is ranked higher than 1. This will remove all duplicate records.

Saturday, November 7, 2009

How to find a particular row from a table (SQL Query)

Declare cursor on table ,after that open the cusor and
using "fetch" keyword to retrive any row ,remove the cursor explictly because it is stored in the memory for this use dellocate keyword


Declare fetch_row insensitive scroll cursor for select *
from tbl1 [for{readonly}]

open fetch_row

Fetch absolute 5 from fetch_row

Deallocate fetch_row


It will display 5th row of the tbl1

Friday, November 6, 2009

How to Access the Windows File from SQL Server

Problem
Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory. What are some approaches to do so? I need some flexibility to capture the file names and general information for subsequent processing. What are the native SQL Server options as well as the custom options that are available?

Solution
Depending on the exact needs dictates the command(s) that need to be issued from SQL Server 2000 or 2005. Below outlines some options to access the Windows file system related information with both native and custom code. In general the following commands exist:

Extended Stored Procedure - xp_cmdshell - Ability to execute any DOS command line code.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_cmdshell 'dir c:\'
GO

Extended Stored Procedure - xp_fixeddrives - Ability to capture the free drive space in megabytes.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_fixeddrives
GO

Extended Stored Procedure - xp_subdirs - Ability to capture the sub directories, but not files.
(Platform = SQL Server 2000 and SQL Server 2005)
EXEC master.dbo.xp_subdirs 'c:\'
GO

Custom Code - snippet - Ability to capture the file names in a temporary table with xp_cmdshell.
(Platform = SQL Server 2000 and SQL Server 2005)

SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000)
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)

-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL
)

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = ''
SELECT @FilePath = 'C:\Progra~1\Micros~2\MSSQL.1\MSSQL\Log\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1

-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%
%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList

-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table
-- ********************************************************************************

-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList

SET NOCOUNT OFF
GO

Stored Procedure to delete files from a folder

When your only way of managing SQL Server is through Enterprise Manager or Query Analyzer, you will have to do a lot of tasks through command shell. If you find yourself routinely conduct some activities using xp_cmdshell, it is a good idea to wrap those routines into a stored procedure. This example takes an input parameter for directory name and delete all files within that directory. The directory name can be UNC path. I put some comments within this procedure so it should be easy to follow.

CREATE proc usp_DeleteFileInFolder @FolderName varchar(150) as
--Created by Haidong Ji
SET NOCOUNT ON

declare @DOSCommand varchar(150)
--Check whether the user supply \ in the directory name
if not (right(@FolderName, 1) = '\')
set @FolderName = @FolderName + '\'
--Delete all files within this folder. Note: del *.* only deletes files,
--not folders. Q is the quite switch so DOS will not ask for confirmation
set @DOSCommand = 'del /Q ' + '"' + @FolderName + '*.*' + '"'
print @DOSCommand
exec master..xp_cmdshell @DOSCommand
GO



For Delete a particular file : xp_cmdshell 'del c:\testfile.txt'
For More Info : http://www.sqlservercentral.com/articles/Administration/usingxp_cmdshell/1250/

Wednesday, November 4, 2009

What is the meaning of: COLLATE SQL_Latin1_General_CP1_CI_AS

Just to avoid confusion between Windows Code Page and SQL Server CP
For ex.,
The SQL Server 7.0 code page for a default installation is SQL_Latin1_General_CP1_CI_AS. For SQL Server 2000 default code page is Latin1_General_CI_AS.

Microsoft made this change to reduce the scope of differing results that the incompatible SQL Server and Windows code pages cause.

CP1 - Codepage 1

CI - Case Insensitive

AS - Accent Sensitive.

Lets say you are running a stored procedure (SP) that performed a cross database joins on our SQL Server 2000 box. This particular procedure was processing records from a table in one database (database A), that was originally created on our SQL Server 2000 box, while also processing records from a table in another database (database B), that was also on our SQL Server 2000 box, but was created by restoring a SQL Server 7.0 database backup. The SQL Server 7 machine had a different character set and sort order than our SQL Server 2000 database. The SELECT statement the SP was running was joining records from the table in database A with the table in database B on a varchar key field. Can you imagine what happened? The SP failed with a "collation error".

SQL Server 2000 finally solved the problem of taking database backups from servers with different character set / sort order and restoring them, without getting a collation error. Before SQL Server 2000, backups from one server could only be restored on a different server if the same character set and sort order where used by both servers. With SQL Server 2000 you are able to set the collating sequence at the server, database and even the column level. Now that you can have multiple collating sequences on a single database server, there are a few things you should understand about mixing collations.

When you have character data stored with different collation sequences, you need to be aware that comparing columns becomes more involved. Columns with the same collating sequences can be compared or joined without any additional considerations. Although when you compare columns that have different collating sequences it require some special coding considerations.