Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: BCP special character issue.

    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.
    Last edited by dstachon; 12-08-04 at 19:23. Reason: incorrect information.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

    Code:
    example
    select ASCII('')
    
    select Char(176)
    
    select ASCII('|')
    
    select Char(124)
    Books Online {String Functions}
    Last edited by Ruprect; 12-08-04 at 15:48.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    latest development...

    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:

    Code:
    CREATE TABLE "dbo"."F_conv"
    	(
    	"col1" VARCHAR(21) NOT NULL
    	)
    ;
    The .fmt file DecisionStream creates:

    Code:
    8.0
    1
    1	SQLCHAR	0	30	"\r\n"	1	col1	""
    the .dat file

    Code:
    1332 NS 4 Tall 32 oz
    and the command:
    Code:
    bcp "MY_DB"."dbo"."F_conv" in "mydata.dat" 
    -q -S<server> -Usa -Psa -f"myformat.fmt"
    thoughts? Tweaks to that fmt?
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Strictly as a test, try using:
    Code:
    CREATE TABLE "dbo"."F_conv"
    	(
    	"col1" NVARCHAR(21) NOT NULL
    	)
    ;
    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    that's cool...

    changing it to NVARCHAR delivered:

    1332 NS 4░ Tall 32 oz
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  7. #7
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    this has to be a bug...

    can anyone successfully load via BCP the character:

    actually....i'm going to start a new thread for this...
    Last edited by dstachon; 12-08-04 at 21:17. Reason: correction
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •