write ntext column data to a file

Question

In a table, i have ntext column. Data stored in this is html body . when i use the sqlcmd to write it to a text file, i get only part of the html body written into the text file. Anyway i can write the whole html body that is stored in the table.

select EmailBody from table where id ='100' -- will return one emailBody ( data contains html tags etc )

Answer 1

Hello, as I understand you need to modify a html string and replace values, then save a file with an .html extension in some directory of the file system.

Natively sql is not recommended for this task, I recommend you do this with a console application which runs either with a scheduled windows task or is running as a windows service.

Otherwise you will continue with some problems of incomplete text strings etc.

The other option would be that you save it as files in filetable that you can later access it through the windows file explorer

Answer 2

Hi @VenkataVarada ,

You can use bcp.exe for what you need.

SQL

/*  
-T tells BCP to use Windows Authentication to connect to SQL Server. It will operate as the user who's running the command.  
-U <login_id> -P <password> tells BCP to use SQL Server account while connecting to SQL Server.  
*/  
DECLARE @SQLCmd VARCHAR(8000)  
   , @outputFileName VARCHAR(256) = 'e:\Temp\SampleXMLOutput.xml'  
   , @bcp VARCHAR(256) = 'c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe'  
   , @SQL VARCHAR(2048) = 'SET NOCOUNT ON; select EmailBody from table where id =''100'';'  
   , @tcpPort VARCHAR(10) = '1433'  
   , @WindowsAuth BIT = 1   -- 1 is for Windows Authentication, 0 is for SQL Server Authentication  
   , @loginID VARCHAR(100) = 'loginID'  
   , @password VARCHAR(100) = 'password'  
   , @serverName VARCHAR(100) = 'SPACESHIP';  
  
-- older versions of SQL Server  
-- /B "WindowTitle" parameters produce output in the SSSMS !!!  
SET @SQLCmd = 'START /B "WindowTitle" "' + @bcp + '"'   
            + ' "' + @SQL + '"'   
            + ' queryout "' + @outputFileName + '"'  
            --+ IIF(@WindowsAuth = 1, ' -T', ' -U '+ @loginID + ' -P ' + @password)  
            + CASE WHEN @WindowsAuth = 1 THEN ' -T'  
               ELSE ' -U '+ @loginID + ' -P ' + @password  
               END  
            + ' -x -c -C 1252 -a 32768'  
            + ' -S "' + @serverName + ',' + @tcpPort + '"';  
  
-- just to see it  
SELECT @SQLCmd AS [Command to execute];  
  
-- create file on the file system  
EXECUTE master.sys.xp_cmdshell @SQLCmd;  

Answer 3

Hi @VenkataVarada ,

Welcome to Microsoft Q&A!
Plese see this MS doc: https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15
Note:

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.