I need to prepare a stored procedure which export as many files as many tables I have in schema, but files need to be written on local machine on specific path e.g. C:\Temp\ not on SQL Server destination. When file exists it should be overwritten, if not simply created. I need also read from local files: with parameters and template, which will be used in procedure. I know how to do everything except the method of read/write files.
I found some ideas for export to files:
- xp_cmdshell executing bcp command I could only write files on Sql Server 2019 machine.
- I found also sp_configure 'Ole Automation Procedures', 1;
DECLARE @OLE INT DECLARE @FileID INT EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, 'D:\data\sqltotext.text', 8, 1 EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, 'Today is wonderful day' EXECUTE sp_OADestroy @FileID EXECUTE sp_OADestroy @OLE GO
Do you know easier way to achieve that?