Tuesday, February 14, 2012

Character Conversion

Hi,

I'm using an ETL tool called DecisionStream.

On a particular SQL Server, when it performs an insert, the degree character is being converted to a stacked bar:

source data: 1332 NS 4 Tall 32 oz
after insert: 1332 NS 4 Tall 32 oz

This works fine on my laptop with SQL Server, but for some reason, on the SQL Server at work, this behaviour occurs.

Any input would be great.

thanks,
d.you could use the ascii and char() functions to check the value of the character on each computer.
then check the differences between the server and your laptop's regional settings in control panel > regional settings.
also check the character set\code page that SQL is loaded with in both instances, there should be a discrepancy in one of those two locations.
if not check for gremlins.

example
select ASCII('')

select Char(176)

select ASCII('|')

select Char(124)

Books Online {String Functions}|||I'd also check the column collations, and verify that the data path (including any temp files, network filters, etc) all support Unicode. I've been bitten more than once by character set conversions.

-PatP|||just made a huge oversite...the delivery where the character conversion is happening is not INSERTing (as I originally thought)...it is actually performing a BCP delivery...

Perhaps this stuff would make sense to one of you guys, as I don't know this utility very well.

TARGET TABLE DDL:

CREATE TABLE "dbo"."F_conv"
(
"col1" VARCHAR(21) NOT NULL
)
;

The .fmt file DecisionStream creates:

8.0
1
1 SQLCHAR 0 30 "\r\n" 1 col1 ""

the .dat file

1332 NS 4 Tall 32 oz

and the command:

bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"

thoughts? Tweaks to that fmt?|||Strictly as a test, try using:CREATE TABLE "dbo"."F_conv"
(
"col1" NVARCHAR(21) NOT NULL
)
;-PatP|||changing it to NVARCHAR delivered:

1332 NS 4░ Tall 32 oz|||can anyone successfully load via BCP the character:

actually...i'm going to start a new thread for this...

No comments:

Post a Comment