Cyrillic charcters in my sql script does not appear in DB

[mmaslenkova]

Hello,
I have script that inserts initial data in my database. Although the script contains cyrillic characters the file is unicode. When running this script with advanced installer cyrillic strings are empty and the columns in the database remain empty also - I traced the execution of scripts in SQL profiler and the value of cyrillic strings is: N' '. When opening the script file directly in the SQL Management Studio and executing it - everything is ok, but I need to run it through the installer .

Regards: Mila

[canhuth]

Hi Mila,

maybe you can run an external program to perform the necessary steps? For example in case of MySQL you could temporarily install mysql.exe, the interactive/scripting shell for MySQL, and run it more or less like... "cmd.exe /c mysql.exe ... <initialdb.sql".



With best regards

Clemens Anhuth

[Cosmin]

Hi,

I'm not sure why you are encountering this behavior. Is the SQL script file encoded as Unicode, UCS2LE or UCS2BE? Also, are you using an Unicode ODBC driver?
If these conditions are met and the problem persists, please send us a sample SQL script which shows this behavior so we can investigate it.

Regards,
Cosmin

[mmaslenkova]

Hello Cosmin,

My script file is with Unicode - Codepage 1200 encoding. We decompiled the msi file with Wix tool and we noticed that cyrillic strings in "AI_SQLScript" custom table are empty - you can see the N'''s :

Code: Select all

<CustomTable Id="AI_SQLScript">
........
   <Row>
        <Data Column="ScriptName"><![CDATA[Initial Data3]]></Data>
        <Data Column="ConnectionName"><![CDATA[FMCDataConnection]]></Data>
        <Data Column="Condition"><![CDATA[1]]></Data>
        <Data Column="Separator"><![CDATA[GO]]></Data>
        <Data Column="ScriptText"><![CDATA[
SET XACT_ABORT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
BEGIN TRANSACTION
DECLARE @error INT
DECLARE @ptrBinary varbinary(16)
ALTER TABLE [\[]dbo[\]].[\[]CITY[\]] NOCHECK CONSTRAINT [\[]FK_CITY_COUNTRY[\]]
ALTER TABLE [\[]dbo[\]].[\[]REGION[\]] NOCHECK CONSTRAINT [\[]FK_REGION_COUNTRY[\]]
ALTER TABLE [\[]dbo[\]].[\[]CITY[\]] NOCHECK CONSTRAINT [\[]FK_CITY_REGION[\]]
ALTER TABLE [\[]dbo[\]].[\[]ADDRESS[\]] NOCHECK CONSTRAINT [\[]FK_ADDRESS_CITY[\]]
ALTER TABLE [\[]dbo[\]].[\[]MUNICIPALITY[\]] NOCHECK CONSTRAINT [\[]FK_MUNICIPALITY_CITY[\]]
ALTER TABLE [\[]dbo[\]].[\[]ADDRESS[\]] NOCHECK CONSTRAINT [\[]FK_ADDRESS_MUNICIPALITY[\]]
INSERT INTO [\[]dbo[\]].[\[]COUNTRY[\]] ([\[]GUID[\]], [\[]COUNTRY_NAME[\]], [\[]CODE[\]], [\[]CREATE_USER[\]], [\[]CREATE_DATETIME[\]], [\[]CREATE_MODULE[\]], [\[]UPDATE_USER[\]], [\[]UPDATE_DATETIME[\]], [\[]UPDATE_MODULE[\]]) VALUES('081f3f22-9565-4469-98ac-b58d4f86c9df', N'', N'BG', N'dbo', '2003-06-30 11:52:17.327', N'Initialization', N'dbo', '2003-06-30 11:52:17.327', null)
INSERT INTO [\[]dbo[\]].[\[]REGION[\]] ([\[]GUID[\]], [\[]REGION_NAME[\]], [\[]COUNTRY_GUID[\]], [\[]CREATE_USER[\]], [\[]CREATE_DATETIME[\]], [\[]CREATE_MODULE[\]], [\[]UPDATE_USER[\]], [\[]UPDATE_DATETIME[\]], [\[]UPDATE_MODULE[\]]) VALUES('bb98fa09-fe86-4484-bb8c-02701b609c2e', N'', '081f3f22-9565-4469-98ac-b58d4f86c9df', N'dbo', '2003-06-30 11:52:17.653', N'Initialization', N'dbo', '2003-06-30 11:52:17.653', null)
INSERT INTO [\[]dbo[\]].[\[]REGION[\]] ([\[]GUID[\]], [\[]REGION_NAME[\]], [\[]COUNTRY_GUID[\]], [\[]CREATE_USER[\]], [\[]CREATE_DATETIME[\]], [\[]CREATE_MODULE[\]], [\[]UPDATE_USER[\]], [\[]UPDATE_DATETIME[\]], [\[]UPDATE_MODULE[\]]) VALUES('4adff19e-4251-462e-b35a-1f371dcc889d', N'', '081f3f22-9565-4469-98ac-b58d4f86c9df', N'dbo', '2003-06-30 11:52:17.437', N'Initialization', N'dbo', '2003-06-30 11:52:17.437', null)
INSERT INTO [\[]dbo[\]].[\[]REGION[\]] ([\[]GUID[\]], [\[]REGION_NAME[\]], [\[]COUNTRY_GUID[\]], [\[]CREATE_USER[\]], [\[]CREATE_DATETIME[\]], [\[]CREATE_MODULE[\]], [\[]UPDATE_USER[\]], [\[]UPDATE_DATETIME[\]], [\[]UPDATE_MODULE[\]]) VALUES('9563b6c1-00a8-4f54-a47c-2563b69f4e64', N' ', '081f3f22-9565-4469-98ac-b58d4f86c9df', N'dbo', '2003-06-30 11:52:17.670', N'Initialization', N'dbo', '2003-06-30 11:52:17.670', null)
INSERT INTO [\[]dbo[\]].[\[]REGION[\]] ([\[]GUID[\]], [\[]REGION_NAME[\]], [\[]COUNTRY_GUID[\]], [\[]CREATE_USER[\]], [\[]CREATE_DATETIME[\]], [\[]CREATE_MODULE[\]], [\[]UPDATE_USER[\]], [\[]UPDATE_DATETIME[\]], [\[]UPDATE_MODULE[\]]) VALUES('71899c70-f2fd-4edc-9fde-2e3e4964b9bc', N'', '081f3f22-9565-4469-98ac-b58d4f86c9df', N'dbo', '2003-06-30 11:52:17.500', N'Initialization', N'dbo', '2003-06-30 11:52:17.500', null)

By opening the msi with Orca we also noticed that the Code page of the package is 1252.
My script file is related to the whole database structure and it won't work just by itself, but you can use any insert script that fills some columns with cyrillic characters. I will attach sample file.

Regards: Mila

[Cosmin]

Hi,

We decompiled the msi file with Wix tool and we noticed that cyrillic strings in "AI_SQLScript" custom table are empty - you can see the N'''s :

Please note that Wix is not a debugging tool for MSI packages. Also, Wix will show the exact same thing as Orca: an empty space. This is because the codepage of the MSI doesn't match with the codepage of the SQL Script.

In order to test the script you need to install the package and see what the script does. Another way to test it is to build your package in a Cyrillic language (for example Russian) and see what is written in the MSI with the Cyrillic codepage .

Regards,
Cosmin

[mmaslenkova]

Hi,

It works with the package for Bulgarian language. But what language should we use so the package codepage to be Unicode. Imagine we need a package with English user interface language but still there are cyrillic data in our db scripts. We achieved this with the Bulgarian package because there is no dictionary for the language and it uses the default one - English, yet to me it is better if the package is with Unicode codepage.

Regards: Mila

[Cosmin]

Hi,

Please note that the language and codepage of the package doesn't matter for the script. The codepage only makes Orca show the Cyrillic characters, it does not determine if the characters are in the script or not. Therefore, you can create the MSI in any language you want with any codepage as long as the SQL Script file uses Unicode encoding.

Note that only the encoding of the SQL Script file matters, not the way it is shown in Orca (or another tool). In order to test the script please install the package. The Bulgarian package was only a test to show that the SQL Script is inserted correctly in the MSI.

Basically, the codepage for English cannot show Cyrillic characters, therefore it shows blanks. This doesn't mean that the characters are not in the Script.

Regards,
Cosmin

[mmaslenkova]

They are obviously not because after execution of the script (installing the package) they do not appear in the database also. They are inserted as blank strings So it's not just "visual" problem.

Regards: Mila

[Cosmin]

Hi Mila,

I apologize, I misunderstood the explanation of our SQL expert. Basically, an SQL Script is added correctly in the MSI. However, when Windows Installer runs the script, it uses the codepage of the language of the installation. If this codepage doesn't support the characters, the install process will be able to interpret them.

For example, if you have a multilingual package which includes the English and Russian languages, there are two possible scenarios:
- the installation runs in English and the codepage doesn't support the characters (they are interpreted as blanks)
- the installation runs in Russian and the codepage supports the characters (they are interpreted correctly)

Unfortunately this behavior is caused by the way Windows Installer manages the entries in the MSI. However, I added on our TODO list a feature which will allow you to run SQL Scripts installed by the package. This way the Script will not be inserted into MSI tables and it will not depend on the codepage of the installation.

Until this feature is implemented a solution would be to use sqlcmd.exe in a "Property Source" custom action. First, you need to create a search which finds "sqlcmd.exe" on the target machine. The property of this search can then be used in the custom action which runs the script.

Regards,
Cosmin

[mmaslenkova]

Thank you! We are now using our Bulgarian package so it's fine at this stage. I'll try sqlcmd.exe solution also, but it's good that you have it in your TODO list.
Anyway it's nice to work with your product and communicate with you!

Regards: Mila