Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to run sqlcmd.exe in order to setup a new database from command line. I am using SQL SERVER Express 2012 on Windows 7 64 bits.

Here's the command I use:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log 

And here's a pieceof the sql file creation script:

    CREATE DATABASE aqualogy 
    COLLATE Modern_Spanish_CI_AS
    WITH TRUSTWORTHY ON, DB_CHAINING ON;
    GO
    use aqualogy
    GO
    CREATE TABLE [dbo].[BaseLayers] (
    [Code] nchar(100) NOT NULL ,
    [Geometry] nvarchar(MAX) NOT NULL ,
    [IsActive] bit NOT NULL DEFAULT ((1)) 
    )

    EXEC sp_updateextendedproperty @name = N'MS_Description', @value = N'Capas de     cartografía base de la aplicaicón. Consideramos en Galia Móvil la cartografía(...)'
, @level0type = 'SCHEMA', @level0name = N'dbo'
, @level1type = 'TABLE', @level1name = N'BaseLayers'

Well, please check that there are some accents on the words; which is the table's description . The database is created with no problems. 'Collate' is understood by the script, as you can see in the attached screenshot. Despite of this, accents are not properly shown when examining the table.Collation problem

I'd really appreciate any help. Thank you very much.

[Edit]: Hi all. Changing the SQL file encoding using Notepad++ worked fine! Thank you very much for you help: I learned something interesting with this problem!

enter image description here

share|improve this question

migrated from stackoverflow.com Apr 20 '13 at 17:53

This question came from our site for professional and enthusiast programmers.

1  
What is the encoding of your SQL script file? –  Pondlife Apr 19 '13 at 14:58
    
I'd go with @Pondlife's idea. Grab a text editor (like Notepad++), open the .sql file there, go to Encoding and see that your file is not in ANSI (or some other unsupported encoding). If it is, choose to convert the file to some thing else. I'd say UTF8 should do it. Let us know how it went. –  Marian Apr 21 '13 at 9:16

3 Answers 3

up vote 2 down vote accepted

As from the comments, the problem is not exactly with the table or the way SQLCMD imports the special characters. Usually, problematic imports are related to the format of the script itself.

Management Studio itself offers the option of saving with a specific encoding, which should solve the problem in the future. When saving a file for the first time (or use save as) you should click on the small arrow near the Save button, to use the option Save with encoding.

enter image description here

By default it saves the file in Western European (1252). Whenever I have any special characters I use UTF8 (though maybe some other restrictive encoding would fit) because it's usually the fastest fix.

enter image description here

I'm not sure (from the pic) that you're using SSMS, so please make sure that your own editor has the option of saving the file in a different encoding. If not, converting the file in a smart editor (like you've already tried in Notepad++) usually works. Though that might not work if you're converting from a wide encoding to a narrower one and then back to a wide one (eg: from Unicode to ANSI and back to Unicode).

share|improve this answer
    
Hi Marian. Thank you again. I used the 'Data Transfer' feature from Navicat, because I like the way it generates the sql script (quick, mdf-file independent, and since now always had worked for me). I checked some parameters in the 'Data Transfer' feature at Navicat, but I didn't find any way to change output file encoding. I'll have to do some extra work using Notepad++ to change encoding, but it's not a big deal. –  Oskytar Apr 22 '13 at 7:34
    
Well, no problem. Though, usually, thanks on this site are delivered using the Upvote feature (the up arrow near the answer) if the answer is useful, or, if it solves your problem completely, even using the Mark as the answer (the Checkmark near the answer) :-). See more here, in the FAQ. –  Marian Apr 22 '13 at 7:35
    
I correct my last comment: IT IS possible to choose file output encoding when executing 'Data Transfer' with Navicat. –  Oskytar Apr 23 '13 at 8:42
    
Cool, so now you will be able to bypass the manual conversion step. That's even better. –  Marian Apr 23 '13 at 9:04

Another option, one which I've just learned, comes from the sqlcmd documentation. You need to set the codepage for sqlcmd to match that of the file encoding. In the case of UTF-8, the codepage is 65001 so you'd want:

SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log -f 65001

share|improve this answer

This sort of thing is very tricky because so much is done without telling you.

The first thing I would do is use sqlcmd to display the string. If it displays correctly in the cmd.exe window, that's one useful fact. Next, I'd select the row converting the string to varbinary to see what bytes are actually there. I think cartografía will show up as 0x636172746f67726166c3ad61, where the accented "i" is represented by the bytes c3ad, which is the UTF-8 encoding for that character. It's no good having UTF-8 in a Modern Spanish column (Windows 1252). The byte value in Windows 1252 for that character is 237 decimal (hex ED).

If the column holds misencoded data, then the error lies in how it was inserted. Perhaps removing the leading N in the string constants -- N'string' tells SQL Server to generate a Unicode string, but plain 'string' indicates the characters use the client's encoding -- would insert Modern Spanish instead of Unicode.

If the column holds correctly encoded data, then I would say you found a bug in the GUI display.

If you can't get sqlcmd to insert the data correctly (leading N or not), then you want to complain to Microsoft. When you do, being able to show the bytes as stored in the column -- using convert(colname as varbinary) -- will be critical to explaining what's going wrong.

share|improve this answer
    
Hi James, thank you very much for your detailed answer. I did some tests, and Sqlcmd.exe is not showing right string. It is clearly a problem about how data is inserted. –  Oskytar Apr 21 '13 at 9:17
    
Using Sql Profiler, I debugged what sentences were send to SQL SERVER when using the SQl Server GUI client. INSERT INTO [ElementType] ([Code], [Name], [Description], [GeometryType], [stringId], [CapturedGeometryType]) VALUES ('ESTACION ', 'Estación', 'Estación', 'Multipoint', NULL, 'Point'); worked FINE, so all the accents were properly inserted!. That's the same 'insert sentence' that appears in my sql script. Should I specify the character code in the sql script file? –  Oskytar Apr 21 '13 at 9:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.