[MSSQL] Copy & Delete File on Server Side

sp_configure 'xp_cmdshell', '1'
go
reconfigure

exec master.dbo.xp_cmdshell 'copy drive:\folder\filename.ext drive:\folder\filename.ext'

exec master.dbo.xp_cmdshell 'del drive:\folder\fiilename.ext'

Tested on MSSQL 2005

Reference:
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-tools/4237/Script-to-delete-backup-files-which-are-7-days-old
http://www.mssqltips.com/sqlservertip/1020/enabling-xpcmdshell-in-sql-server-2005/

[MSSQL] Import Excel Using Query

Below example how to import row data directly using queries. 

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Tested on MSSQL 2005.

Reference: http://support.microsoft.com/kb/321686

[MSSQL] Export to Excel using query

Below example how to export row data directly using queries.

1. Enable Ad Hoc Distributed Queries.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

2. Create new excel sheet and define the columns names exactly the same as the select statements below.

Then
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;', 'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact

Tested on MSSQL 2005.

Resource: http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/