Why does SQLSERVER Express not recognize accented words when creating a bank using sqlcmd?

Asked

Viewed 346 times

0

Problem

In some databases(SQLSERVER) I am often needing to popular the tables with some data, which usually comes from a file .txt or .xlsx, I need to do this as quickly as possible.

The first thing I thought about doing that would help me, was to copy the data from these files to excel, save them as .csv and import into the bank banco de dados -> tarefas -> importar arquivo simples, but this way the letters that come with accent are not recognized by SQLSERVER and are replaced by ?.

The second thing I thought about doing was copying the data from the files .txt or .xlsx, go in tabela_do_banco -> Editar 200 linhas superiores and paste there. In this way letters containing accent are recognized and displayed without be replaced.

However, the problem in doing this second way is the following: after everything is ready I need to create the bank using the command sqlcmd -E -S .\sqlexpress -i "%CD%\script.sql" and when I do this the data that were previously populated without any character being replaced are replaced :(.

Doubts

  • Why SQLSERVER Express does not recognize accented words when creating bank using sqlcmd ?
  • What could I do for SQLSERVER Express to recognize accents (á, à, ã, â, c) ?
sql-server-express

  • I even installed the latest version of Sql Server 2019 and created the database with collate Latin1_General_100_CI_AI_SC_UTF8 and the same thing happens: if I run the creation script of the bank and run it by the SSMS itself the bank is created normally without replacing the characters, but if I use the command of the sqlcmd to execute the script file that contains the creation of the database; it creates the bank with the collate that I defined but replaces the characters that have accent by other strange.

1 answer

0


Resolution for the first problem

To change the encode of the archive *.csv was enough:

  • Open it as a notepad
  • Select the option Salvar como
  • Change the encoding to UTF-8(In a combo-box that is next to the save button)
  • Save the file and overwrite in case save with the same name;

Resolution for the second problem

In charge sqlcmd just add the flag -f 65001. Where -f specifies the codepage and 65001 represents the UTF-8

OBS: I generated sql script with encoding UTF-8 preset and tried to rotate the sqlcmd without passing the flag -f, but the encoding was not maintained.

Browser other questions tagged sql-server-express

You are not signed in. Login or sign up in order to post.