I've "text file" stored in a column of a table in SQL Server database. How do I retrieve the text file from the database.
asked Sep 21, 2011 at 08:50 AM in Default
If I understand you right, you have a table in which there is a column that is either TEXT, NTEXT, VARCHAR(MAX) or NVARCHAR(MAX). You want to save the value in every row to a separate file.
The obvious way of doing this is to use a PowerShell script, especially if you wish to save on the 'client' rather than the Server.
If you need to extract the text as files on the server, as part of an archiving perhaps, or to do a file-based process, there are a variety of different ways. I'd recommend using PowerShell, again, but it depends on the circumstances, and the systems that you are most familiar with.
If you need to do it in TSQL via BCP, then that's possible. If each row represents a file, then I think you'll have to get each 'text file' value from the table and then execute this procedure to save it to disk, supplying the file path for each row. Here is a general procedure to write out a VARCHAR(MAX) to disk as a file. (taken from my article [The TSQL of text files] . I suspect that there is a much cleaner way of doing this in your case since the text file is already a row in a permanent table, and my routine is for a variable; but I haven't the time to write it (and test it!).
----------------------------------------------------------------- IF OBJECT_ID (N'dbo.spSaveTextToFile') IS NOT NULL DROP PROCEDURE dbo.spSaveTextToFile GO CREATE PROCEDURE spSaveTextToFile @TheString VARCHAR(MAX), @Filename VARCHAR(255), @Unicode INT=0 AS SET NOCOUNT ON DECLARE @MySpecialTempTable VARCHAR(255) DECLARE @Command NVARCHAR(4000) DECLARE @RESULT INT
answered Sep 23, 2011 at 12:26 AM
The following BCP utility worked for the issue I posted earlier. The only issue pending is getting the timestamp from BillDt column. For formatting purpose we're inserting @@@ on all header records but what we noticed is that the BillDt (timestamp) is not displayed properly (see below the results from bcp utility). So, How do I get the BillDt in the correct format?
C:>bcp "SELECT TOP 5 '@@@', [BillDt], [FileName], [FileExt], [BillImage] FROM [Eclips].[dbo].[tblBill] WHERE BillType in ('ITBP2007', 'CURRENT_MONTH_DEMAND' )" queryout C:\Temp\eClips\eClipsdata.dat -N -S SQLSERVER01\TEST -TResults from BCP utility: @ @ @ ?ž H 9 9 9 9 - 9 9 9 9 - 9 9 _ 2 0 1 0 - 1 2 - 0 1 _ I T B P 2 0 0 7 . h t m H T M öu
@ @ @ !ž H 9 9 9 9 - 9 9 9 9 - 9 9 _ 2 0 1 0 - 1 1 - 0 1 _ I T B P 2 0 0 7 . t x t T X T u
@ @ @ å J 0 0 3 0 - 0 6 7 3 - 0 1 _ C U R R E N T _ M O N T H _ D E M A N D . T X T T X T :[
answered Sep 28, 2011 at 04:23 PM