Problems running SQL scripts outside Management Studio   

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?

date: Mon, 29 Jun 2009 13:22:09 -0700   author:   dj7934

Re: Problems running SQL scripts outside Management Studio   

dj7934 (dj7934@discussions.microsoft.com) writes:
> 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. 

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.
 
> 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
 
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@sommarskog.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/prodinfo/previousversions/books.mspx

date: Mon, 29 Jun 2009 14:20:40 -0700   author:   Erland Sommarskog