nvarchar formatting?

Question

nvarchar formatting?

Barbara-167570

SSCrazy

Points: 2352
More actions
March 2, 2005 at 12:37 pm

#88143

Hello,
I'm stumped on this one. We have a nvarchar column (5000) that gets populated by end users with their resumes. Is there a way to keep the formatting of what they have entered? For example if they have bullets in their resumes?
Any help or suggestions are greatly appreciated. Or if anyone can point me in the right direction as to where I can find informatin on this topic.
We are running SQL7+SP3 ( but hopefully updating to 2000 soon)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

sellertools Old Hand Points: 365 More actions · Answer 1

what about saving their resumes as files on the server and storing a link or the path to that file in the DB? seems like it would answer your question and take a certain type of load off of the DB.

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 2

Thanks for responding.

I neglected to mention that we are running merge replication so I don't know if your suggestion will work. Would this be my only option?

Thanks again

sellertools Old Hand Points: 365 More actions · Answer 3

I dont have an answer to that unfortunatly. I wonder, how is the data getting into the column in the first place? When it comes out, is it all one long string of text?

sellertools Old Hand Points: 365 More actions · Answer 4

actually, I am not sure that it is your only option is what I meant to say.

based on what you added, you would (if you were to implement a file saving\linking process), need to either replicate the physical files themselves to the other servers.

another option, going back to the saving a physical file idea, is that you store the files on a single fileserver or webserver, and then store the URL link or UNC path to those files in the SQL Servers. Then, when the records are replicated, and the Resume for a person is requested, it is retrieved from the common source, regardless of how many subscribers you had in your mix.

Hope that makes sense.

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 5

The data is being entered via a CF form as a text area.

When the data is returned it is one long string of text, and characters get replaced. For example if there are values that SQL cannot interprets it returns them as question marks(?), or example certain bullets would be returned this way. We try and strip out all ASCII characters that it cannot handle but it still comes back jumbled.

Thanks again,

Barbara

sellertools Old Hand Points: 365 More actions · Answer 6

okay,

can you intercept this resume data and save it to a textfile? It is possible that the problem isnt so much in SQL as it is in the way the data is being received in the first place. Also, can you post you insert statement, or is it a Proc that you call to intially add the resume text in the first place.

Peter Wouters SSC Rookie Points: 28 More actions · Answer 7

Hi,

I think the problem is situated in the CF code on the webpage , quotes are probably missing.

Regards,

Peter

athurgar SSCrazy Points: 2816 More actions · Answer 8

Be aware the an nvarchar of 5000 actualluy uses 1000 bytes and exceeds the max row size - you may hit truncation issues in very long files

Barbara-167570 SSCrazy Points: 2352 More actions · Answer 9

Thank you all for your help.

Here is a sample of how the data looks when it is retrieved:

o Manager and Assistant Manager of Consultant Accounting

 Calculated/tracked engagement performance

 Conducted sensitivity/scenario analyses

It seems strange because not all records come back with characters like that. It appears if people use astricks * the come through fine.

If anyone has any additional comments I'm all ears.

Again, than you all for your help

Wayne West SSC-Insane Points: 22586 More actions · Answer 10

Definitely don't use nvarchar if you can use varchar, the "N" means it's unicode, storing two bytes for every byte of data. You need the N series of text datatypes if you work with foreign text input.

And I'd lean towards storing the resumes as external files, you could probably simulate replication through copying them through either DTS or a batch job but then you lose any text searching capability.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Peter Laube SSC Veteran Points: 214 More actions · Answer 11

The string "" is the code for a tilde "~" character. If you display this string in an html page, the browser shows a tilde. Are you html encoding before storing the data?

nlcapit SSC Rookie Points: 41 More actions · Answer 12

Are you using a cfqueryparam to insert the data into your table? That would probably help.

Are you converting the data from the form using HTMLEditFormat or HTMLCodeFormat? That would convert characters to their HTML equivalents and remove end of line characters.

M Chabot SSCrazy Points: 2511 More actions · Answer 13

You might want to check out this page to verify that you are actually using Unicode http://mysecretbase.com/ColdFusion_and_Unicode.cfm

Also, storing a resume in a nvarchar column might be a tight fit. You can try ntext for more space.

You also might want to research collations in the SQL Server manual. It may provide you with some key insight.

Good luck.

Mona-150988 Newbie Points: 1 More actions · Answer 14

your pbm if from the web page, adn sending the data..

u shout set the pagelangue and the Charset.