Hello and thank you for your help. I must write non-tabular information to a file. I have tried a few methods that invoke various stored procedures. The following SQL statement tests this and I don't know how to make it work. I have evaluated and set permission for the MSSQLSERVER user on the directory. Would someone help me please?

-- Declarations
DECLARE @FileSystem INT;
DECLARE @FileHandle INT;
DECLARE @ForWriting INT;
DECLARE @CreateIfNeeded INT;
DECLARE @TextStream INT;

-- Set the file path
SET @FilePath = 'C:\Users\crmcw\AppData\Local';
SET @ForWriting = 2; -- Constant for write mode
SET @CreateIfNeeded = 8; -- Constant to create the file if it doesn't exist

-- Create the FileSystemObject
EXEC sp_OACreate 'Scripting.FileSystemObject', @FileSystem OUT;

-- Open the file using the OpenTextFile method
EXEC sp_OAMethod @FileSystem, 'OpenTextFile', @FileHandle OUT, @FilePath, @ForWriting, @CreateIfNeeded;

-- Check if the file handle was successfully obtained
IF @FileHandle <> 0
    -- File handle obtained, proceed with writing to the file
    SET @TextToWrite = 'This is the text to write to the file.';

    -- Write the text to the file
    EXEC sp_OAMethod @FileHandle, 'WriteLine', NULL, @TextToWrite;

    -- Close the file
    EXEC sp_OAMethod @FileHandle, 'Close';

    -- Clean up the COM objects
    EXEC sp_OADestroy @FileHandle;
    EXEC sp_OADestroy @FileSystem;

    PRINT 'File created and written successfully.';
    -- File handle not obtained, there was an error
    PRINT 'Failed to create or open the file.';
  • What happens when you run this code? Do you see an error? Jul 2 at 6:38
  • Do yourself a favour and don't use sp_OA procedures, they are ancient and full of bugs and are difficult to use correctly. Instead use a proper scripting language like Powershell (you can even create a SQL Agent job written in Powershell). This whole procedure is a single line of Powershell. Jul 2 at 10:39
  • Thank you Martin. I did indeed. I set the EXEC to a variable and evaluated it thus: Jul 2 at 19:39
  • Thank you @Charlieface. I haven't used MS SQL for about 30 years. This is very helpful. Jul 2 at 19:46
  • In Powershell you could do $TestToWrite | Out-Content -FilePath $FilePath; As a side note: I see two problems with your code. The file path is actually a directory. You can't write to a directory, you need an actual file path to write to. And SQL Server is unlikely to have the correct permissions to write to a user's private folder, as it runs under a separate service account. Jul 2 at 19:54


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.