Locale problem??

Results 1 to 4 of 4

Thread: Locale problem??

  1. #1
    Join Date
    Dec 1969

    Default Locale problem??

    We&#039;re pulling data from our flat file MIS system and inserting it into a SQL 2000 db. We&#039;re having some strange results with one of the fields.<BR><BR>The field in question is a varchar field. The data pulled from the MIS system is made up of values like: £355.11, £42.36 etc. It&#039;s being inserted into the db with the £ signs replaced by a ú character. We&#039;re assuming this is because of the locale being set incorrectly somewhere (we&#039;re in the UK) but everywhere we check has English (UK) or similar.<BR><BR>Has anyone had a similar problem? Does anyone know how to get round this?<BR><BR>Cheers in advance,<BR>Pete.

  2. #2
    Join Date
    Dec 1969

    Default Feels more like codepage problem...

    But *how* are you pulling the info from the flat file???<BR><BR>Doing it all in SQL Server? Or using ASP or similar?<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Feels more like codepage problem...

    For some reason, the vendors have decided to use MySQL to pull the data from the text file and then call iSQL to insert it into SQL Server.<BR><BR>They&#039;re using this script (sorry it&#039;s a little long, I&#039;ve marked the place where the insert occurs with !!!!!!!!!!!!!!!!!!!!! and I&#039;ve removed our auth details):<BR><BR>@ECHO OFF<BR>ECHO $$$ JRSXRPTJ: Extract Data for DataMart and load it to DataMart<BR>set CYBORGDRIVE=F:<BR>set CYBORGHOME=F:CYBORGCYBORG5.1<BR><BR>rem Change to correct drive<BR>rem DAC 29-04-2003<BR>%CYBORGDRIVE%<BR><BR>call %CYBORGHOME%
    unsmfsetup.bat<BR>set COBSW=-+S5<BR><BR>SET SQLSRVR=SERVER<BR>set DB-NAME=DATABASE<BR>set DB-OWNER=OWNER<BR>set DB-USER=USER<BR>set DB-PASSWORD=PW<BR>set ISQL-PATH=c:Program FilesMicrosoft SQL Server80 oolsinn<BR>set BCP-PATH=c:Program FilesMicrosoft SQL Server80 oolsinn<BR>set LOGDIR="F:Error Logs%CURRDATE%"<BR><BR>if /I "%1" == "" goto :Extract<BR>if /I "%1" == "Extract" goto :Extract<BR>if /I "%1" == "Sort" goto :Sort<BR>if /I "%1" == "Split" goto :Split<BR>if /I "%1" == "Truncate" goto :Truncate<BR>if /I "%1" == "Load" goto :Load<BR>echo *<BR>echo *********************************************<BR>e cho *** "%1" is an invalid Step Restart Name; ***<BR>echo *** - Job has been terminated !!! ***<BR>echo *********************************************<BR>e cho *<BR>goto :End<BR><BR>:Extract<BR>TITLE --- Extracting Data ---<BR><BR>REM ************************************************** <BR>ECHO STEP 1 - EXTRACT DATA<BR><BR>echo P REPORTJ00100 CS-SCR 2 &#062; %CYBORGHOME%params
    sxrpt.04<BR>echo P REPORTJ00100 REPORT RSXRPT &#062;&#062; %CYBORGHOME%params
    sxrpt.04<BR>echo P REPORTJ00100 CS-SCR 2 &#062;&#062; %CYBORGHOME%params
    sxrpt.04<BR>echo P REPORTJ00100 RS@PMM RSXRPT &#062;&#062; %CYBORGHOME%params
    sxrpt.04<BR>set FILE01=%CYBORGHOME%datafile01<BR>set FILE02=%CYBORGHOME%datafile02<BR>set FILE03=%LOGDIR%
    sxrpt.03<BR>set FILE04=%CYBORGHOME%params
    sxrpt.04<BR>set FILE10=%CYBORGHOME%sql
    sxrpt.10<BR>set FILE15=%CYBORGHOME%work
    sxrpt.15<BR>set FILE36=%CYBORGHOME% xt
    sxrpt.36<BR>%CYBORGHOME%progcbsvb<BR><BR>:Sort<BR> TITLE --- Sorting Data ---<BR><BR>REM ************************************************** <BR>ECHO STEP 2 - SORT DATA<BR>REM ************************************************** <BR>if exist %CYBORGHOME% xt
    splitr.36 del %CYBORGHOME% xt
    splitr.36<BR>mfsort Use %CYBORGHOME% xt
    sxrpt.36 Record (V,5,1024) Org LS Give %CYBORGHOME% xt
    splitr.36 Sort Fields (5, 19, ch, a)<BR>REM ************************************************** <BR>REM if exist %CYBORGHOME% xt
    sxrpt.36 del %CYBORGHOME% xt
    sxrpt.36<BR>REM ************************************************** <BR>REM if running out of disk space, delete the REM<BR>REM statement above. rsxrpt.36 is not deleted here<BR>REM for restart purposes.<BR>REM ************************************************** <BR><BR>:Split<BR>TITLE --- Splitting Data ---<BR><BR>REM ************************************************** <BR>ECHO STEP 3 - SPLIT DATA<BR>REM ************************************************** <BR>if exist %CYBORGHOME% xtcf*. del %CYBORGHOME% xtcf*.<BR>if exist %CYBORGHOME% xtdf*. del %CYBORGHOME% xtdf*.<BR>if exist %CYBORGHOME% xtd_*. del %CYBORGHOME% xtd_*.<BR>if exist %CYBORGHOME% xtm_*. del %CYBORGHOME% xtm_*.<BR>SET FILE03=%LOGDIR%
    splitr.03<BR>SET FILE04=%CYBORGHOME%params
    splitr.04<BR>SET FILE36=%CYBORGHOME% xt
    splitr.36 del %CYBORGHOME% xt
    splitr.36<BR><BR>:Truncate<BR>TITLE --- Truncating Data ---<BR><BR>REM ************************************************** <BR>echo STEP 4 - TRUNCATE DATAMART TABLES<BR>REM ************************************************** <BR>if exist %CYBORGHOME%logrs
    sxrpt.log del %CYBORGHOME%logrs
    sxrpt.log<BR>"%ISQL-PATH%isql" -U%DB-USER% -P%DB-PASSWORD% -n -d%DB-NAME% -i%CYBORGHOME%sql
    sxrpt.10 -o%CYBORGHOME%logrs
    sxrpt.log !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!<BR><BR>:Load<B R>TITLE --- Loading Data ---<BR><BR>REM ************************************************** <BR>echo STEP 5 - LOAD DATAMART TABLES<BR>REM ************************************************** <BR>if exist %CYBORGHOME%logrscf*.log del %CYBORGHOME%logrscf*.log<BR>if exist %CYBORGHOME%logrsdf*.log del %CYBORGHOME%logrsdf*.log<BR>if exist %CYBORGHOME%logrsd_*.log del %CYBORGHOME%logrsd_*.log<BR>if exist %CYBORGHOME%logrsm_*.log del %CYBORGHOME%logrsm_*.log<BR>if exist %CYBORGHOME%cperrcf*.bad del %CYBORGHOME%cperrcf*.bad<BR>if exist %CYBORGHOME%cperrdf*.bad del %CYBORGHOME%cperrdf*.bad<BR>if exist %CYBORGHOME%cperrd_*.bad del %CYBORGHOME%cperrd_*.bad<BR>if exist %CYBORGHOME%cperrm_*.bad del %CYBORGHOME%cperrm_*.bad<BR><BR>REM ----------------------------------------------------------------<BR>REM - Verify that DM Table Definition File exist<BR>REM ----------------------------------------------------------------<BR><BR>if exist %CYBORGHOME% xtcfvrs00 goto :BCP<BR>echo *<BR>echo ************************************************** *************<BR>echo *** BCP process cannot be initiated: ***<BR>echo *** [ Cannot locate DM Table Definition File (CFVRS00) ] ***<BR>echo *** Job has been terminated !!! ***<BR>echo ************************************************** *************<BR>echo *<BR>goto :End<BR><BR>:BCP<BR>REM ---------------------------------------------------------------------------------------------------------------------------------<BR>REM - Initialize Bulk Copy Command<BR>REM ---------------------------------------------------------------------------------------------------------------------------------<BR><BR>set BCP_CMD="%BCP-PATH%bcp" %DB-NAME%.%DB-OWNER%.%%j in %CYBORGHOME% xt%%i -f%CYBORGHOME%fmt%%i.fmt -o%CYBORGHOME%logrs%%i.log -e%CYBORGHOME%cperr%%i.bad -U%DB-USER% -P%DB-PASSWORD%<BR><BR>REM ----------------------------------------------------------------<BR>REM - Execute Bulk Copy Command for each valid table<BR>REM ----------------------------------------------------------------<BR><BR>for /f "eol=# delims=, tokens=1,2" %%i in ( %CYBORGHOME% xtcfvrs00 ) do if exist %CYBORGHOME% xt%%i %BCP_CMD%<BR><BR>REM ----------------------------------------------------------------<BR>REM - Clean up the text files after loading the data<BR>REM ----------------------------------------------------------------<BR><BR>if exist %CYBORGHOME% xtcf*. del %CYBORGHOME% xtcf*.<BR>if exist %CYBORGHOME% xtdf*. del %CYBORGHOME% xtdf*.<BR>if exist %CYBORGHOME% xtd_*. del %CYBORGHOME% xtd_*.<BR>if exist %CYBORGHOME% xtm_*. del %CYBORGHOME% xtm_*.<BR><BR>:End<BR>TITLE --- JRSXRPT completed ---<BR>echo $$$ JRSXRPT has completed $$$<BR><BR><BR>We&#039;ve confirmed that the format is correct right the way up until the insert occurs. When the insert is executed using iSQL, all the £ signs are swapped for ú symbols - chr(163). The field which this is being entered into is just a varchar, so we assumed sql server was expecting a different ascii value for £ than it was being given. <BR><BR>We&#039;re currently trying iSQLW (declaring ANSI with a -F flag) but it doesn&#039;t look to be making any difference.<BR><BR>Any help you can provide would be appreciated.<BR><BR>Cheers.

  4. #4
    Join Date
    Dec 1969

    Default Solved it! Thanks, Bill.

    It was the call to bcp - you were right, for some reason it was defaulting to an incorrect code page (even though it was pulling the data from the same system as it was inserting it to). We set -CRAW to tell bcp not to make any changes. <BR><BR>A day and a half to find a 5 character string! LOL! <BR><BR>Cheers for the input, Bill - you put us on the right track.

Posting Permissions

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