August 29, 2003 at 8:49 am
I've never used OLE Automation in SQL Server before, and Books Online appears to be minimal help (maybe I'm not looking in the right place). However, I ran across some code on this site that I have been able to simplify/adapt so that it writes (i.e., appends) to a text file. Please see code below.
Questions:
- What do @FS and @file do in the code?
- OpenTextFile appears to open the file for append. How I can open a file for overwrite instead?
- In addition to OpenTextFile and WriteLine, what other parameters are valid for sp_OAMethod?
- Is there some useful documentation for OLE Automation? (Unfortunately, I am not a VB programmer--I need something that deals just with a SQL Server interface that I can use in a stored procedure or a .sql script)
Jon
----------------------------------------------
quote:
declare@Return_CDEint-- Return code
,@fsint
,@fileint
,@OutputFile_NMEvarchar(100)-- Output file name
,@PrintLine_DSCvarchar(1000)-- Generic print string
set @OutputFile_NME = 'C:\jps.txt'
set @PrintLine_DSC = 'Test line 4'
-- Create an instance of the OLE object
exec @Return_CDE = sp_OACreate 'Scripting.FileSystemObject', @FS output
select @Return_CDE as '@Return_CDE', @FS as '@fs'
-- Open the file
exec @Return_CDE = sp_OAMethod @FS, 'OpenTextFile', @file output, @OutputFile_NME, 8, 1
select @Return_CDE as '@Return_CDE', @file as '@file'
-- Write to the file
exec @Return_CDE = sp_OAMethod @file, 'WriteLine', Null, @PrintLine_DSC
-- Destroy the created OLE objects
exec @Return_CDE = sp_OADestroy @file
exec @Return_CDE = sp_OADestroy @FS
Edited by - shew01 on 08/29/2003 08:49:30 AM
August 31, 2003 at 12:37 am
Hi there
One item to watch out for. The return values from the method call (sp_OAMethod) are restricted to 255 characters, so take care with returning large xml blobs for example.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 1, 2003 at 7:50 am
Download the scripting documentation at http://www.microsoft.com/scripting for documentation on the scripting.filesystemobject.
September 2, 2003 at 3:18 am
Hello!
In the script th @FS INT var will contain the OLE handle to a 'Scripting.FileSystemObject' object. (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/fsooriScriptingRun-TimeReference.asp).
@FS will contain the OLE handle to an open file (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsmthopentextfile.asp)
Try the method CreateTextFile (with overwrite) instead (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/jsmthcreatetextfile.asp).
Regards, Hans!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply