BCP and Code Page Fun

SQL Server Best Practices
SQL Server Best Practices
Quick, what’s the fastest and easiest way to get data out of a SQL Server table?

Time’s up. It’s BCP.

If you aren’t familiar with BCP it is a command line utility to bulk copy data out of, and in to tables. It has been around for donkeys years (technical term), and while it doesn’t have the frills of SSIS it is fast, and lightweight. DBAs have been using it for years, but it may not be something that you’ve heard of.

Recently, while BCPing some data between servers I ran into an interesting problem which caused the data imported to be different than that exported. How does that happen? Code pages…

What is a code page?

A code page is a method of encoding a character set. There are many different code pages, but you are probably most familiar (if you work with SQL Server in the US) with code page 1252. This is the code page that SQL uses with the default install and collation settings, you may know it as SQL_Latin1_General_CP1_CI_AS.

Installation collation breakdown
  • SQL_Latin1_General – The language to which the sorting rules are applied
  • CP1 – Code page (CP1 = 1252)
  • CI – Case sensitivity, in this case Case Insensitive
  • AS – Accent Sensitivity, in this case Accent Sensitive

TechNet and the SQL Server documentation have a list of all the potential installation collations.

Continue reading on SirSQL.net.

Continue reading on SirSQL.net.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *