| Author |
Topic |
|
rongrzywacz
Yak Posting Veteran
57 Posts |
 Posted - 2002-01-07 : 12:22:25
|
| How do I determine what the default collation is for an instance of SQL server 2000. I am having problems bulk loading a text file into a table, and believe that it is related to a collation issue.How do I determine what the db/server was set up to use??Thanks for any help.RGRon Grzywacz |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-07 : 12:27:45
|
| Look up SERVERPROPERTY function in BOL. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 12:30:50
|
| and also databasepropertyex ('dbname','collation')==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
rongrzywacz
Yak Posting Veteran
57 Posts |
Posted - 2002-01-07 : 12:31:39
|
| Thanks for the quick response! Ok I see that it is set to SQL_Latin1_General_CP1_CI_ASHow do I find out what it was in my 7.0 server and how do I change it in 2000. Ron Grzywacz |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 12:50:29
|
| in 2000 you can specify a collation for an individual database (or column).To change them use alter database / table with the collate keyword.To change the default I guess you would have to rebuild the master database.Or you could create another instance.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
izaltsman
A custom title
1139 Posts |
|
|
rongrzywacz
Yak Posting Veteran
57 Posts |
Posted - 2002-01-08 : 13:30:11
|
| The collation setting seem to be set correctly on my new 2000 server, but we are still having a problem bulk iserting data into a nvarchar field. It appears to work ok if the field is defined as varchar. But it gives me a truncation error if defined as nvarchar.Thanks for the assistance,RG Ron Grzywacz |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-08 : 13:47:36
|
| Is it possible the total row length is over 8060 bytes? Could you post your table definition?Edited by - izaltsman on 01/08/2002 13:48:42 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-08 : 15:22:03
|
quote:
It appears to work ok if the field is defined as varchar. But it gives me a truncation error if defined as nvarchar.
Remember also that nvarchar has half the potential capacity as varchar. In other words, the max size of nvarchar is 4000 whereas varchar is 8000. That's because in unicode (n____ fields) it takes 2 bytes per character to store the info.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
rongrzywacz
Yak Posting Veteran
57 Posts |
Posted - 2002-01-08 : 16:12:23
|
| There are only about 40 fields in the table, with a rowlenght of about 400 bytes, so it's not too long. The thing that is driveing me crazy is that the same bulk load statement, with the same data and table works in Sql Server 7.0Thanks much for all you input.Ron Grzywacz |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-03-17 : 10:53:40
|
| Hi there,refer the follwing link :http://www.microsoft.com/sql/techinfo/tips/administration/changingcodepage.aspI have a collation problem and have found that it has been cauised by a Clean install of SQL 2K, followed by attaching db's from SQL 7. Thing is, the SQL_LATIN1_CP1_CI_AS collation that they refer to iss the default SQL7 collation, near as I can see - so I struggle to see how they justify classifying it as "legacy, self-defined code page" that they want to move away from. Also, the SQL 2K installation doesn't offer this collation in install! I'm going to have to re-uninstall SQL2K (3rd time today) then install SQL 7, then upgrade ! *sigh*Hope this helps you in some way ...*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here!Edited by - wanderer on 03/17/2003 10:54:29 |
|
|
|
|
SQL 2000 default Collation