0

I want to export the everyday data to a csv file and delete the records.

I have seen the standard maintenance plan has option to archive bak file only.

Is there any way I write script and attach to maintenance plan to do my tasks? (Per table)

  1. Export the records to CSV file (csv file name with current time)
  2. Delete all the records from live DB.

Thanks.

Karesh A
  • 1,731
  • 3
  • 22
  • 47

2 Answers2

1

Use OPENROWSET to export data through tsql and put this code inside your job :

BEGIN TRANSACTION;

DECLARE @FileName AS VARCHAR(250)= 'Data-'+
       SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), SYSDATETIME(), 121), ' ', ''), ':', '-'), '.', '-')+'.xls';

INSERT  INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database='+@FileName+';', 'SELECT * FROM [SheetName$]')
SELECT  *
FROM    [YourTable];
WHERE < ... conditions ... >

DELETE  FROM [YourTable];
WHERE < ... conditions ... >

COMMIT;

I hope to be helpful :)

Ali Adlavaran
  • 3,697
  • 2
  • 23
  • 47
  • how to name the excel file as current time ? like the bak named with timing. – Karesh A Aug 30 '17 at 04:07
  • use this `SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), SYSDATETIME(), 121), ' ', ''), ':', '-'), '.', '-');` – Ali Adlavaran Aug 30 '17 at 04:13
  • where this select will go ? Database=D:\Data.xls;' here ? Sorry. – Karesh A Aug 30 '17 at 04:18
  • Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". Do you know any idea? – Karesh A Aug 30 '17 at 05:27
  • my db server is MS Sql server 2016 developer version. – Karesh A Aug 30 '17 at 05:32
  • There is no `Microsoft.Jet.OLEDB.4.0 driver` installed. take a look at here https://stackoverflow.com/questions/36987636/cannot-create-an-instance-of-ole-db-provider-microsoft-jet-oledb-4-0-for-linked and here https://www.sqlservercentral.com/Forums/Topic564076-17-1.aspx and here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/05258d18-d7e5-411d-b591-d98ba6674448/cannot-create-an-instance-of-ole-db-provider-ibmdadb2-for-linked-server?forum=sqldataaccess – Ali Adlavaran Aug 30 '17 at 05:33
  • Seems like new file wont't be created everytime ? I need to have file before insert ? Error Message "Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object,check your network connection or contact the server administrator.". – Karesh A Aug 30 '17 at 06:14
  • INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\DATA.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM Table1 – Karesh A Aug 30 '17 at 06:15
0

How about creating a windows service in a server side language like C# and have it run daily to retrieve the data, write it to a csv file and then delete the data?

Anthony McGrath
  • 792
  • 5
  • 7