I have a script that populates some tables in a database. The script was
generated from Management Studio and includes some fields that are stored
"encrypted" by the application that created them.

When the scripts are executed via Management Studio the values are loaded
correctly. When they are executed via OSQL or using an ADO connection to the
database, reading each line of the text file and executing at each "GO" the
value inserted into the field is different and the application fails to
properly decode the data in the field.

I was not surprised to run into problems using the ADO connect and the
..OpenTextFile method of the Scripting.FileSystemObject, but was surprised
that OSQL produced different results than Management Studio.

I'd like to automate loading the data using a "setup" script instead of
having to manually start Management Studio, open the file then execute it.

This might not appear correctly, but here is what I see if the data is
imported through Management Studio: Özðf�ñ\Ë2 and here is what I see if I
import it using OSQL or the scripted solution: +z=f-±\-2

Any ideas here how I might work around this?

dj7934 (dj7934 (AT) discussions (DOT) microsoft.com) writes:
When you use ADO, you will need to parse out the GO yourself. This is
handled by general query tools such OSQL etc. But you have maybe realised
that already.

ANSI/OEM conversion raises its dreaded again. Here is the story: on a
Windows machine, there are three characters sets in play: 1) The OEM code
pages, which is the 8-bit used by the command-line windows. 2) The
ANSI code page which is the 8-bit code page used by Windows application.
3) Unicode, which is 21-bit, and fits all characters in the world.

OSQL is a command-line tool and assumes that it reads file in the OEM
code page. But the 8-bit character set of SQL Server is typically an
ANSI code page. Thus a conversion takes place when data is sent to
SQL Server. But if the data was in fact ANSI data, it gets distorted.

There is no way to tell OSQL that an 8-bit file is another code page
than the OEM code page. However, you can save the scripts from Mgmt
Studio as Unicode file, and OSQL will detect that the file is Unicode
and act accordingly.

If you are on SQL 2005 or later, you can use SQLCMD instead; SQLCMD has
the -f option which permits you to specify the code page.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx