Create/Delete file using SQL Server 2008

Question

I have an Excel file in C:\testing.xlsx. I am exporting data from my database table to Excel using [OPENROWSET (Transact-SQL)]1 method. Now I want to

1. clear or delete all rows in my Excel file by executing some query in SQL Server
2. delete or create new Excel file in specified path (ie C:\) from SQL Server

Can I do this? If, please suggest me how to do that?

Answer 1

I have got one very useful link which i have not tried. But, its a great stuff by author...

Answer 2

Not sure about modifying the excel file (have you tried deleting rows?). As far as modifying files on the filesystem... if you enable xp_cmdshell you can run any command line commands on the system (like copy or delete).

Answer 3

You might want to use simple SSIS package to do that. Just create a new project in Business Intelligence Development Studio (VS2008) - it goes along with yur SQL Server installation and very easy to start.