1

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

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
  • 125
  • 2
  • 9

1 Answers1

1

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
squillman
  • 37,883
  • 12
  • 92
  • 146
  • 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 07 '12 at 18:17
  • It fails on the line where it tries to create/open the text file. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 07 '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 07 '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 07 '12 at 18:36
  • Specifically it returns -2146828218. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 07 '12 at 18:41
  • Ok, then you'll need to research what that error code means in the context of the Scripting.FileSystemObject class and the OpenTextFile method on that class. Again, it's likely to be a permissions issue or a file existence issue. The problem is definitely not related to a deprecated feature in SQL Server 2008R2, though. That nasty error code is definitely a COM error code which means your calls to the sp_OA* procs are happening. – squillman Feb 07 '12 at 19:23
  • Do you have any idea where I can get more information on these error messages? I have tried google and msdn with no luck. – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 07 '12 at 20:05
  • I think it is an unauthorized access error. Is it possible SQL Server isn't getting administrator permissions? How would I solve this? – zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz Feb 07 '12 at 20:31
  • Yes, it's possible. COM errors are notoriously evil to troubleshoot. There are different accounts that are used depending on what context the statements are run as (interactive, via a SQL Agent job step, etc) – squillman Feb 07 '12 at 20:39
  • The error in question is pretty easy to find, convert `-2146828218` from Dec to Hex (using calc.exe in Programmer's mode) and you get `FFFFFFFF800A0046` in `Qword` mode, which is the default. Take `800A0046` (or change to `Dword` mode) and after googling it you'll get `Permission Denied`. – mprill Dec 24 '12 at 20:56