Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I used to write to text files from SQL Server using the code listed below:

DECLARE @FS INT         --File System Object 
DECLARE @OLEResult INT  --Result message/code
DECLARE @FileID INT     --Pointer to file

--Create file system object (OLE Object)    
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject.Failed'

-----OPEN FILE-----
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'OpenTextFile.Failed'

It appears this is no longer supported in sql server 2008 r2. How should I export to text files in sql server 2008 r2?

Link claiming this is no longer supported:

share|improve this question

What that article is referring to as deprecated is SQL DMO (having been replaced with SQL SMO beginning with SQL Server 2005). However, your code does not use SQL DMO objects so you're really barking up the wrong tree. The post you link to just coincidentally uses the sp_OA* procs to create instances of SQL DMO objects.

You need to turn the Ole Automation Procedures option on in your SQL instance. As a security precaution it's turned off by default. This option is required in order to to use the sp_OA* procs.

This should do it for you.

sp_configure 'show advanced options',1


sp_configure 'Ole Automation Procedures',1

share|improve this answer
You are correct about the article referring to the DMO. I made sure these options were set and I still can't open/create the text file to write to. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 7 '12 at 18:17
It fails on the line where it tries to create/open the text file. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 7 '12 at 18:17
What's the error that you're seeing? Most likely it's a permissions problem, or the files in question don't exist. – squillman Feb 7 '12 at 18:27
When I execute the line to create or open the file if everything goes ok @OLEResult should equal 0, but it is being set to a negative number. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 7 '12 at 18:36
Specifically it returns -2146828218. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 7 '12 at 18:41

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.