ANSI/OEM conversion during SQL Execution

[GiorgioG]

Is there a way to specify that isql be used instead of osql when running a SQL Server step? Inserting data which have ® symbols results in « symbols being inserted when run by OSQL (which is what VBP uses internally I presume.) Running ISQL and unchecking ANSI/OEM conversion option in client network utility resolves the issue - but I'd much rather be able to flip a setting somewhere in VPB than rewrite all the steps to use ISQL. Thanks.

-Giorgio

[kevina]

I did some testing and this is indeed an issue.

For now the workaround is to write your sql to a utf-16 file, and submit it from this file instead of from a string (stored inside the step).

Attached is a simple build demonstrating this workaround.

Kevin

[GiorgioG]

Originally posted by kevina
I did some testing and this is indeed an issue.

For now the workaround is to write your sql to a utf-16 file, and submit it from this file instead of from a string (stored inside the step).

Attached is a simple build demonstrating this workaround.

Kevin

Thanks - unfortunately my sql code is coming from an input file - so I'd have to write out the contents of that file to a utf-16 file then run the sql command against that. It seems impractical/inefficient/slow for larger files. I'll just write new steps to execute the sql using isql until this is fixed.

[kevina]

I have done more testing of this, and have found the following results:

Both isql and osql will do this conversion improperly if the 'Automatic ANSI to OEM conversion' flag is checked in the SQL Server Client Network Utility.

For some reason, osql always does the conversion (even with the setting unchecked). So (as you previously stated), the only way to load these characters (from an ANSI file) is to invoke isql instead.

We will update the sql server action to allow the user to specify either isql or osql in a future release of Visual Build Pro.

In the meantime, another option (since you are inputting your sql from file anyway), would be to simply save your input files as UNICODE. Then you could continue to use the Sql Server action and load your data properly (and you don't have to uncheck the conversion setting in Client Network Utility). Just a thought.

Kevin