SQL SERVER - STORED PROCEDURES - HOW TO WRITE TO A FILE

One of the coolest features of SQL Server coding is the ability to write to a text file. You can actually write to any ASCII based flat file format, like plain text (.TXT). comma separated values (.CSV) also known less officially as comma separated files, tab delimited and even special import files for QuickBooks (.IIF).
 
In order to do this you will need to turn on a few features in SQL Server that are initially turned off:
 
xp_cmdshell
 
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
 
Ole Automation
 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
reconfigure with override;
GO
 

 
The video below will go into a lot more detail than this article. However, you need to understand that even if you run the two previous scripts (and you must be an administrator), and even if you are referencing a real location on the server, sometimes this writing to a file still won't work. It can be frustrating. If this happens to you it is possible that you do not have OS (operating system) permissions on the folder. Permissions will always burn you.
 
If so, try adding the user "Everyone" and then give the "Everyone" account write permissions. That usually takes care of any issues in WIndows.
 
If you find this video helpful, please leave a comment below!
 



SQL SERVER COURSE - 95% OFF FOR OUR READERS

For those of you that would like to broaden your understanding of SQL Server, or if you work with someone who needs a little (or a lot of) help :), We are offering a huge discount to one of our most popular online courses. It is the lead off course which discusses tables - what to do and not do. It is very instructional, practical and straightforward.

Normally this course is $199.00, but for a limited time it is available through this offer for only $3.00!   Click the link below to get started:

SQL Server FAST TRACK - Course 101 - Tables