Getting osql to take a script in codepage 1252

I have a table that stores ISO-8859-1 data. The default collation for my
database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
"osql" that populates the table with default data. The script works for
normal ASCII character data, but not for the full ISO-8859-1 character set.
If I could specify that the script was in codepage 1252, all would be well.
The script is as follows:
-- drop any previous version of the table
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
'U')
DROP TABLE Salutations
GO
-- create the new table
CREATE TABLE Salutations (
Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
Pronunciation VARCHAR (20) NOT NULL,
Pattern VARCHAR (100) NOT NULL,
UIRepresentation VARCHAR (20) NULL,
UISortOrder INTEGER NULL
)
GO
-- populate the table
-- Salutation Pronunciation Pattern
UIRepresentation UISortOrder
INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
'Mr.', 1)
INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
NULL, NULL)
INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
'Mrs.', 2)
INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
'Ms.', 3)
INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
'Miss', 4)
INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
'Prof.', 6)
INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
+', NULL, NULL)
-- Spanish salutations
INSERT INTO Salutations VALUES ('sr', 'seor', '^sr\.? +',
'Sr.', 7)
INSERT INTO Salutations VALUES ('seor', 'seor', '^seor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senor', 'seor', '^senor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('sra', 'seora', '^sra\.? +',
'Sra.', 8)
INSERT INTO Salutations VALUES ('seora', 'seora', '^seora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senora', 'seora', '^senora +',
NULL, NULL)
INSERT INTO Salutations VALUES ('srta', 'seorita', '^srta\.? +',
'Srta.', 9)
INSERT INTO Salutations VALUES ('seorita', 'seorita', '^seorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('senorita', 'seorita', '^senorita +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
NULL, NULL)
INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
'Profa.', 10)
INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
+', NULL, NULL)
INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
'Dra.', 11)
INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
NULL, NULL)
-- the same as in English
--INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
'Dr.', 5)
--INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
NULL, NULL)
--INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
+', 'Prof.', 6)
GO
When I run the script, all of the entries that use the character (ANSI
character 0xE1) end up being stored using the character (ANSI character
0xB1). Note that if I copy and paste the script into Query Analyzer and run
it from there, it runs as expected.
I've tried creating an ODBC datasource with the "Perform translation for
character data" option unchecked and using that data source via the -D
command line switch to osql, but it made no difference. I am running against
SQL Server 2000 SP3a.
What else can I try?
Rick
rgenter "at" silverlink.com
save the file as OEM in Query Analyzer.
regards,
Mark Baekdal
www.dbghost.com
Living and breathing database change management for SQL Server
"Rick Genter" wrote:

> I have a table that stores ISO-8859-1 data. The default collation for my
> database is SQL_Latin1_General_CP1_CI_AS. I have a script that I pass the
> "osql" that populates the table with default data. The script works for
> normal ASCII character data, but not for the full ISO-8859-1 character set.
> If I could specify that the script was in codepage 1252, all would be well.
> The script is as follows:
> -- drop any previous version of the table
> IF EXISTS (SELECT name FROM sysobjects WHERE name = 'Salutations' AND type =
> 'U')
> DROP TABLE Salutations
> GO
>
> -- create the new table
> CREATE TABLE Salutations (
> Salutation VARCHAR (10) PRIMARY KEY NOT NULL,
> Pronunciation VARCHAR (20) NOT NULL,
> Pattern VARCHAR (100) NOT NULL,
> UIRepresentation VARCHAR (20) NULL,
> UISortOrder INTEGER NULL
> )
> GO
>
> -- populate the table
> -- Salutation Pronunciation Pattern
> UIRepresentation UISortOrder
> INSERT INTO Salutations VALUES ('mr', 'mister', '^mr\.? +',
> 'Mr.', 1)
> INSERT INTO Salutations VALUES ('mister', 'mister', '^mister +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('mrs', 'missus', '^mrs\.? +',
> 'Mrs.', 2)
> INSERT INTO Salutations VALUES ('ms', 'miz', '^ms\.? +',
> 'Ms.', 3)
> INSERT INTO Salutations VALUES ('miss', 'miss', '^miss +',
> 'Miss', 4)
> INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.? +',
> 'Prof.', 6)
> INSERT INTO Salutations VALUES ('professor', 'professor', '^professor
> +', NULL, NULL)
> -- Spanish salutations
> INSERT INTO Salutations VALUES ('sr', 'se?or', '^sr\.? +',
> 'Sr.', 7)
> INSERT INTO Salutations VALUES ('se?or', 'se?or', '^se?or +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senor', 'se?or', '^senor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('sra', 'se?ora', '^sra\.? +',
> 'Sra.', 8)
> INSERT INTO Salutations VALUES ('se?ora', 'se?ora', '^se?ora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senora', 'se?ora', '^senora +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('srta', 'se?orita', '^srta\.? +',
> 'Srta.', 9)
> INSERT INTO Salutations VALUES ('se?orita', 'se?orita', '^se?orita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('senorita', 'se?orita', '^senorita +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profesor', 'profesor', '^profesor +',
> NULL, NULL)
> INSERT INTO Salutations VALUES ('profa', 'profesora', '^profa\.? +',
> 'Profa.', 10)
> INSERT INTO Salutations VALUES ('profesora', 'profesora', '^profesora
> +', NULL, NULL)
> INSERT INTO Salutations VALUES ('dra', 'doctora', '^dra\.? +',
> 'Dra.', 11)
> INSERT INTO Salutations VALUES ('doctora', 'doctora', '^doctora +',
> NULL, NULL)
> -- the same as in English
> --INSERT INTO Salutations VALUES ('dr', 'doctor', '^dr\.? +',
> 'Dr.', 5)
> --INSERT INTO Salutations VALUES ('doctor', 'doctor', '^doctor +',
> NULL, NULL)
> --INSERT INTO Salutations VALUES ('prof', 'professor', '^prof\.?
> +', 'Prof.', 6)
> GO
>
> When I run the script, all of the entries that use the ? character (ANSI
> character 0xE1) end up being stored using the character ± (ANSI character
> 0xB1). Note that if I copy and paste the script into Query Analyzer and run
> it from there, it runs as expected.
> I've tried creating an ODBC datasource with the "Perform translation for
> character data" option unchecked and using that data source via the -D
> command line switch to osql, but it made no difference. I am running against
> SQL Server 2000 SP3a.
> What else can I try?
> Rick
> --
> rgenter "at" silverlink.com
>
>
|||Mark,
Thanks. That did the trick.
Rick
rgenter "at" silverlink.com
"mark baekdal" <markbaekdal@.discussions.microsoft.com> wrote in message
news:3CEC828C-E8EB-41C8-99DE-705C52B62ED1@.microsoft.com...[vbcol=seagreen]
> save the file as OEM in Query Analyzer.
> regards,
> Mark Baekdal
> www.dbghost.com
> Living and breathing database change management for SQL Server
> "Rick Genter" wrote:
my[vbcol=seagreen]
the[vbcol=seagreen]
set.[vbcol=seagreen]
well.[vbcol=seagreen]
type =[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^professor[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^profesora[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
+',[vbcol=seagreen]
'^prof\.?[vbcol=seagreen]
character[vbcol=seagreen]
run[vbcol=seagreen]
against[vbcol=seagreen]