Have a look in BOL for 'collations'.
You can use ALTER DATABASE to change the database collation, which will then
apply to any future textbased columns created in that database.
If you already have a table with some text columns in it, you can use ALTER
TABLE to change the collation.
BTW, the only collation you can't change directly is the server one but
rebuilding the master database will allow this to be achieved indirectly.
(recommended sql server 2000 replication book: