This requires that the “Ole Automation Procedures” option be enabled. See Setting and Restoring Configuration Settings for doing this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- declare variables DECLARE @oADODB INT, @FileName VARCHAR(200), @VarToSaveToFile VARCHAR(MAX); SET @FileName = 'C:\Temp\MyOutputFile.sql'; EXECUTE sys.xp_create_subdir 'C:\Temp'; -- make directory -- establish an ADODB stream EXECUTE sp_OACreate 'ADODB.Stream', @oADODB OUTPUT; EXECUTE sp_OASetProperty @oADODB, 'Type', 2; EXECUTE sp_OAMethod @oADODB, 'Open'; EXECUTE sp_OAMethod @oADODB, 'WriteText', NULL, 'Text to save to file;'; EXECUTE sp_OAMethod @oADODB, 'WriteText', NULL, @VarToSaveToFile; EXECUTE sp_OAMethod @oADODB, 'SaveToFile', NULL, @FileName, 2; EXECUTE sp_OAMethod @oADODB, 'Close'; EXECUTE sp_OADestroy @oADODB; |
