April 17, 2012 at 5:21 am
I have a column that I'm looking to extract but am having issues! The column is stored as type ntext and contains an RTF document so looks something like this:
{\rtf1\ansi\ansicpg1252\uc1\deff0{\fonttbl {\f0\fnil\fcharset0\fprq2 Arial;} {\f1\fswiss\fcharset0\fprq2 Arial;} {\f2\froman\fcharset2\fprq2 Symbol;}} {\colortbl;\red0\green0\blue0;\red255\green255\blue255;} {\stylesheet{\s0\itap0owidctlpar\f0\fs24 [Normal];}{\*\cs10\additive Default Paragraph Font;}} {\*\generator TX_RTF32 15.0.530.502;} \deftab1134\paperw11909\paperh16834\margl1138\margt1138\margr1138\margb1138\widowctrl\formshade\sectd \headery720\footery720\pgwsxn11909\pghsxn16834\marglsxn1134\margtsxn1134\margrsxn1134\margbsxn1134\pard\itap0owidctlpar\plain\f1\fs20 Bob Nudd 28/11/2011 11:19:55\par\par Spoke to client\par Have no business for us at the moment\par Call back in 2 months\par }
But I'm looking to extract this back into (rtf or txt I don't really mind) I've tried using BCP which has had success in extracting the documents but they end up exactly the same as the column but with spaces between each character rather than as I'd expect (example above would end up reading something like:
Bob Nudd 28/11/2011 11:19:55
Spoke to client
Have no business for us at the moment
Call back in 2 months
The BCP extract that I'm using (which is extracting) is as follows:
set nocount on;
Declare @sql varchar(1000);
declare @noteid int;
declare xx1 cursor for select nic.NotebookItemId from NotebookItemContent nic
inner join NotebookLinks nl on nl.NotebookItemId = nic.NotebookItemId
inner join NotebookItems ni on ni.NotebookItemId = nic.NotebookItemId
where nl.clientid = 1235074
AND ni.NotebookTypeId = 56;
open xx1;
fetch xx1 into @noteid;
while (@@fetch_status = 0)
begin
set @sql = 'BCP "SELECT memo FROM Monarch_Pronet_ITOIL.dbo.notebookitemcontent where notebookitemid=' + cast(@noteid as varchar) +
'" QUERYOUT \\bhamws475\docs\' + cast(@noteid as varchar) + '.rtf -T -f \\bhamws475\docs\bcp.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql
fetch xx1 into @noteid;
end;
close xx1;
deallocate xx1;
Can anyone point me in the right direction?
April 17, 2012 at 8:08 am
I don't really know the particulars, but ntext I think is a unicode format and it really does take two bytes to store each character (most of the time, occasionally it needs more apparently), maybe in your export you could cast that column to a non unicode varchar format before exporting it? When I looked at an rtf file (saved from wordpad), it certainly didn't look like unicode to me.
Just throwing that out there as a guess.
April 17, 2012 at 8:42 am
I tried casting it to varbinary - not sure if that's what you mean...? But I still got the same result.
Screenshot of the extracted document (if it helps!) http://i.imgur.com/kGMYO.png
April 17, 2012 at 8:53 am
If at all possible you should not use ntext. It deprecated and the performance is horrendous. Instead you should use nvarchar(max).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2012 at 8:55 am
Yeah, I've read up on the differences and why nvarchar is the way forward, however this is in a product we didn't develop (but can play around with) so we can't change the structure of the tables lest we break the client!
April 17, 2012 at 9:01 am
I would expect varbinary to act the same as ntext. The reason I would try casting to varchar is to get it to convert to one byte per character (or pretty much just regular ascii).
April 17, 2012 at 9:03 am
That works a charm - thank you very much for your help!
April 17, 2012 at 9:13 am
awesome, glad you got it working!
November 10, 2015 at 2:09 pm
Great ideas! I tested to bcp query out my rtf data, it works very well after convert rtf data as varchar type into a physical table. 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply