Results 1 to 9 of 9
  1. #1
    Join Date
    May 2002
    Posts
    4

    Unanswered: Collates - CP437 - ADO - Visual Basic

    Hello, I am workink with a Database that has the Collate SQL_Latin1_General_CP437_CI_AS (Instance and Database-SQL2000), this convert the Database in a OEM Server.
    I Can save Characters From 0 to 256 ASCII code CP 437 very well on the database (I import this Data with an DTS) but when i retrive the information with Visual Basic 6.0 with ADO some character > 127 are changed.
    Did you know how I can fix It ?
    I know that the problem is that Windows is ANSI and my SQL Server OEM, but .. a dont´t know how yo fix it.
    Thanks I sorry because my poor english.

    Walter
    Argentina

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easy answer is to do the transfer using Unicode (16 bit characters). This allows any character that Windows recognizes to pass through without problem.

    If you've already got the file that was produced from the table using the 437 collation and want to import it into a server with a different collation, then things get a bit tricky, but they can still be done.

    -PatP

  3. #3
    Join Date
    May 2002
    Posts
    4
    The problem is that I must work with the Hexadecimal code of the character, because I need to send this code (Hexa of ASCII) to a Printer (Special Printer).
    All the program is around this printer and I need manipulate ASCII codes.
    Thanks you

    Walter

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Take the data from the table using the 437 collation in a Unicode (16 bit) text file. This format allows the easy transportation of any character that Windows supports, regardless of the character's collation or character set.

    The Unicode format will preserve the exact character, although sometimes moving a character from one 8 bit character set to another will cause the hex value of the character to change even though it preserves the glyph (the printed form) of the character exactly. You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

    And Pat, it's not collation, it's a code page, and with BCP there are no "tricks"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A code page is definitely one way to implement part of a collation. I see a collation as a lot more complex and flexible than a code page.

    I find it interesting that you suggested one of what I consider the tricks for using BCP (specifying a code page) and noted that you feel that BCP has no tricks in the same posting. I have a hard time understanding that apparent contradiction.

    -PatP

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How can it be a contradiction if this is part of the syntax?

    Quote Originally Posted by pat phelan
    I see a collation as a lot more complex and flexible than a code page.
    One has nothing to do with the other, so do tell, - how do you see them? In blue?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    May 2002
    Posts
    4
    Gracias a Todos, pude resolver el problema.
    En el string de conexion con el servidor SQL Server 2000 hay un parametro que se puede indicar para que al momento de recuperar los caracteres, el servidor no los modifique. Este parametro se llama Auto Translate.
    Al poner Auto Translate = No los valores Hexadecimales de los caracteres que se guardan en la base permanecen intactos. Probe de Generar una Tabla con 2 columnas, la primera tiene un valor entero de 0 a 255 y la segunda es un caracter en el cual puse un codigo ASCII de 0 a 255, al tomarlo desde Visual Basic muestro el valor entero de 0 a 255, el caracter y luego con una funcion de visual muestro el valor Hexadecimal del caracter y vi muy felizmente que en los 255 casos permanecia igual.

    >You can replace DTS transformation task with Win32 task and call a batch that contains BCP. In the BCP call you can include -C <code_page>.

    Con respecto al DST, yo no tengo problemas con el DTS ya que al importar un archivo de texto, el mismo se importa exactamente con los c̣digos exadecimales que este tiene.

    >You need to decide if it is more important to preserve the hex value or the glyph for your purposes.

    Para mi es mucho mas importante preservar el codigo Hexadecimal porque este codigo es un caracter de control para la impresora, independientemente de como se vea visualmente en pantalla, la impresora interpreta el codigo Hexadecimal.

    Now, I will try to translate all the text but.. its very hard.
    Thanks to all of you, I can fix the problem.
    There is a parameter In the String of conexion with the SQL Server 2000 that indicate that don`t change the Hexadecimal code when you retrieve the characters from the server. This parameters is call "Auto Translate"

    I don`t have problem with the DTS. The DTS imports Ok the characters from the ASCII file.

    For me, it`s more important the Hex value that the glymp value because the printer interpretes the Hex value, not the Glyp.

    Sorry because my poor english, I can´t translate all exactly but i think that the idea is the same.
    Thanks !!!

    Walter

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on how you've described your needs, my first choice would be to use BCP, using the -c trick that rdjabarov suggested earlier for both export and import. The -c parameter allows you to control exactly which Windows code page is used for the file, and as long as the code page/collation mapping operation supports the charcters you need going both ways, you should be fine.

    My second choice would be to extract the data using a view, converting the column to VARBINARY to preserve the hex values exactly. This usually means that you need to import the output of that view into a scratch table, then convert the hexadecimal value back to a character value inside of SQL Server.

    If you can extract the data directly from one server to another, using a linked server, that would be my next suggestion.

    -PatP

Posting Permissions

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