Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2013-04-11 : 11:37:54
|
| Hi,On our usual production server, we have a read in task that uses the .net BulkCopyManager class to read delineated text files and push them into sql. It's always worked without a problem.So I was pretty surprised when we built a new server and tried to run the same job against the same data to find that it choked on a particular file which had loaded fine before. The error was converting a string into a date column.On investigation it turned out that the problem was that this particular file had its dates in British format (DD/MM/YYY) whereas the other files were in universal format (YYYY-MM-DD).I am assuming that I've neglected to switch on some option or feature that allows SQL server to recognise the UK format as valid. But I can't find it. Can anyone point me in the right direction? Or, if I'm wrong, suggest what on earth else the problem is?Cheers,Matt |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-11 : 12:35:22
|
| I suspect it has to do with your language setting. Take a look at this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=1828321. Find your current language: You can use "SELECT @@LANGUAGE".2. Find the date format setting for it. Use "select * from sys.syslanguages where name = (select @@LANGUAGE)" You probably will see mdy.If that is the case, language setting is the problem.Now, how you would solve that - if you can change the language setting on your server (it is in the advanced tab of the server properties dialog) that would fix the problem. But that may not be something that you want to do. Even if you do that, users already created may retaint their default language. In any case be very careful if you choose to go that route.- you could change your processes to import the data a string to a staging table and then use a query where specify dmy format explicitly to move it to your destination table.- you may be able to use a format file for the bulk copy; i don't know enough details to say anything useful. |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2013-04-12 : 04:02:13
|
| Thanks. I had a good old mess about with the languages on SQL Server to no avail.However it looks as though it could be a .net language error. If you run BULK INSERT against the server directly, it reads date formats as you'd expect dependent on the language settings. I had assumed that since the error was being thrown on bulk insert that it was a sql problem but, of course, .net has it's own BulkCopy class which will run on its own thread with its own language settings.Going to play with that now, and see if it fixes - will report here if it does. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-05-10 : 18:59:32
|
| You can SET DATEFORMAT at the session level if that would help. Per BOL, "SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE."=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
|
|
|
|
|
|
New instance refusing british date format