Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f8512bec-915c-44a2-ba9d-e679f98ba313

share|improve this question
add comment

1 Answer

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
GO

reconfigure
GO

sp_configure 'Ole Automation Procedures',1
GO

reconfigure
GO
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
show 5 more comments

Your Answer

 
discard

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.