1

We have been using SQL Server on Microsoft Windows Server 2003 SP2, and are now attempting to transfer across to a new server, running 2008 R2. One of our clients has a seperate jobs database which creates text files that are updated via FTP to a folder on our server 3 times daily, to then be imported into a corresponding series of tables in our database. Here is the old code for the import:

Delete
From Client.dbo.jobs
Go

BULK INSERT CarltonRR.dbo.jobs
FROM 'D:\folder\clientDatabaseUpload\jobs.txt' 
WITH 
( 
    DATAFILETYPE='char',
    CODEPAGE = '65001',
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\|\n'
)
Go

After the initial errors, and subsequent searching, I removed the 'CODEPAGE = '65001', line because of the issues mentioned in this documentation, that 2008 R2 does not support UTF-8, however the database would automatically convert to UTF-16. This resulted in problems displaying some characters (£ for example) which the old system handles fine. The Data Type for the field(s) that are not displaying properly is varchar(50)

Is there a change that needs to be made to the SQL queries from 2003 to 2008 R2 that would allow the special characters in the .txt files to be displayed in the database?

Edit: The Data Type for the field(s) in question is nvarchar(50), not varchar(50) Edit 2: If it helps, the listed sign in replacement of the ' £ ' sign is ' ┬ú '

scott_R
  • 43
  • 4
  • check out this thread http://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server – sofl Nov 07 '13 at 15:51
  • @sofl So to clarify with this thread, I will need to convert to UTF-16 **before** I import it? – scott_R Nov 07 '13 at 16:14

0 Answers0