| Author |
Topic |
|
oneilp
Starting Member
7 Posts |
 Posted - 2004-01-06 : 11:42:53
|
| We have a SQL Server 2000 front end for collecting inquiries from our customers and these records are then exported as text to a flat file database that process them. The form basically has name, address, phone, country, etc... info. Every now and then, a Carriage Return is inserted after the street address field. I use Query Analyzer and dump the results to text and it is evident that there is a hard break in the record. We've looked at the code and there is nothing in it that would cause this. There is no white space, no crlf appended to the form data, nothing at all. This carriage return causes our secondary system to crash and that's a bad thing. Have you ever seen this before? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 12:10:02
|
| Could the end user be adding the carriage return?Tara |
 |
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 12:11:05
|
| if they are, I don't know how they're doing it. I can't get it to happen when I try |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 12:14:07
|
| Run SQL Profiler to see what is being sent. Also, QA could be wrapping the text so there appears to be a carriage return when one doesn't exist. To verify this, dump the results to a text file using bcp or DTS. Do not just look at the result set in QA. Export the data to a file using the tools mentioned. Then view the file in notepad or whatever with word wrap turned off.Tara |
|
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 12:17:59
|
| I dumped to EditPad and you can see the Paragragh marker right at the break point. I did that earlier today. This thing has got me stumped. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 12:23:49
|
| How did you do the dump though? You need to use an export utility like bcp or DTS.If it shows the carriage return after the export and dump, I would suggest running SQL Profiler to capture the data being sent. You could also post your code here for us to see if there are any problems.Tara |
|
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2004-01-06 : 12:28:34
|
| A quick work around might be to strip the returns as you do the export (if you can...)Putting replace(column_name,char(13) + char(10),'') into the select statement should rip the returns out... |
|
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 12:29:15
|
| oh, ok I was just using the "results in text" opetion in QA. I'll try a DTS |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2004-01-06 : 14:00:00
|
quote:
if they are, I don't know how they're doing it. I can't get it to happen when I try
How about pressing Ctrl+Enter? |
|
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 18:36:49
|
| no, Ctrl+Enter doesn't do it either |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 18:39:11
|
| What did the dump show? I bet it doesn't show the carriage return. I think that Query Analyzer is showing it even though it doesn't exist.Tara |
|
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 18:45:08
|
| I think you're right. I didn't have permissions to run Query Profiler so I'm not sure what that'll show. It's weird because it's only happened about a dozen times over the last few weeks and that form has been submitted at least a thousand times in that period. |
|
|
|
oneilp
Starting Member
7 Posts |
Posted - 2004-01-06 : 18:48:40
|
| I know it's not being done maliciously because the data is good. The people have been contacted. |
|
|
|
|
line breaks being inserted somehow