BCP interpreting semicolons as carriage returns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

BCP interpreting semicolons as carriage returns

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-17 : 09:56:41

Does anyone know how to turn off the interpretation of semicolons as carriage returns when using the Bulk Copy Program to pull variables into a text file? Maybe it can't be done, if so, is there a way to pull table to a flat file using another method?

Note, I've tried using a CONVERT(variablename,';','semicolon'), but it looks like the conversion happens after BCP has already treated them as carriage returns.


bcp "SELECT * FROM PREDEFINED_Functions" queryout "\\216.178.126.63\Shared\PREDEFINED_Functions2.txt" -c -Uuserid -Sserver -Ppasswd -t','

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-17 : 10:01:58

bcp treats semi colons as crlf's?
Can you give an example.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-17 : 16:50:18

Sure... Here's one:


bcp "select TOP 10 * from WEBQA_state..PREDEFINED_Functions where descriptionshort = 'Region Where Company is Traded (Japanese)'" queryout "seethosesemis.txt" -c -Uuserid -Sservername -Ppassword

In the table there is a column called formula VARCHAR(MAX) containing this long character value:


int exchange;  string_array result;    exchange  = (int)PEXCHN();    if(ISNA(exchange)) then {  result = STRING_NA;  }  else {  result = LOOKUP_SJIS("QAI:GLOBAL_EXCHANGE",exchange,"REGIONJ");  }  return result

Here's how BCP pulls it:



|5|	6265		P_REGION_TRADED_J			Region Where Company is Traded (Japanese)	Region Where Company is Traded (Japanese)	0	1	1	1	0	int exchange;
string_array result;

exchange  = (int)PEXCHN();

if(ISNA(exchange)) then {
result = STRING_NA;
}
else {
result = LOOKUP_SJIS("QAI:GLOBAL_EXCHANGE",exchange,"REGIONJ");
}
return result	int exchange;
string_array result;

exchange  = (int)PEXCHN();

if(ISNA(exchange)) then {
result = STRING_NA;
}
else {
result = LOOKUP_SJIS("QAI:GLOBAL_EXCHANGE",exchange,"REGIONJ");
}
return result	1001		2004-04-29 17:15:00	2	0	2		-1	0	1	0	1	-1

It looks like it's treating the } as a carriage return as well.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-17 : 17:13:09

How are you looking at the result?
When I do this and look at it in notepad it's fine.

create table x (s varchar(max))

insert x
select 'int exchange; string_array result; exchange = (int)PEXCHN(); if(ISNA(exchange)) then { result = STRING_NA; } else { result = LOOKUP_SJIS("QAI:GLOBAL_EXCHANGE",exchange,"REGIONJ"); } return result'

master..xp_cmdshell 'bcp "select TOP 10 * from tempdb..x" queryout "c:\seethosesemis.txt" -c -T'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 01:27:58

It wont cosider ; as carriage return until you have carriage return as part of the string. Probably you copied the string from somewhere and used that which may contain carriage return

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-18 : 15:39:07

Bingo! Thanks Madhivian. I used REPLACE(VAR1,CHAR(13),' ') in my SELECT and the problem is gone. Thanks!!

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-04-18 : 15:40:32

Sorry for the misspelling : )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources