Releasing a database build consistently via isql

Question

I am releasing a database build to SQL Server 2000 via a batch file using isql. The batch file is used so multiple files are released consistently to different SQL Servers (development, test, live).

The SQL Server uses ANSI code page 1252 (from sp_helpsort) but isql is an OEM client using code page 437. This means that all extended characters (with ASCII code > 128) are converted when the scripts are run, leading to inconsistent results when characters like “£” are included in the script. Differences are explained in this Microsoft knowledgebase article.

Possible solutions are: -

• Save the script using Unicode and use osql.
• Turn off the AutoAnsiToOem setting using the SQL Server Client Network Utility (that writes a registry key).

Both these options rely on various people doing things consistently. All have to select the same code page option when saving a file OR all people performing the builds have to have the same option set for AutoAnsiToOem.

Is there a way to force the use of a code page either in the SQL script OR in the batch file that calls it, so that the build is always released consistently, regardless of how the file is saved or the various settings of whoever performs the release?

Answer 1

isql is obsolete. It isn't included in SQL Server 2005 or later, because it uses the DB-Library connections, which are also obsolete. For the reasons why, and the effects this has, see Connecting Early Version Clients to SQL Server 2000.

osql uses ODBC connections to connect to SQL Server. For completeness, this has been supplemented by sqlcmd in SQL Server 2005, which uses OLE DB with the SQL Native Client provider.

Answer 2

Further to the accepted answer, I have tested using sqlcmd against a SQL Server 2000 database and it works. You obviously have to run sqlcmd from a machine with the SQL Server 2005 client tools installed.