June 29, 2018 at 9:33 am
Hi Everyone,
Let me start by listing our Sql Server Versions:
Production Server:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Development Server:
Microsoft SQL Server 2014 - 12.0.2269.0 (X64)
Jun 10 2015 03:35:45
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 17134: )
Yes, definitely not very current but getting there! haha 🙂
I ran into an issue last night that has caused me some confusion. We receive our telephony cost-rates from several vendors and while processing one vendor we use bulk insert. We processed the file and all loaded fine and then our rater process started creating a lot of matching errors. It turns out that the column being matched to was the very last column in the data file we receive and that every row contained a CR (CHAR(13)) as the very last character in the column.
I tried using several different row terminators during development and ended up using 0x0a to load the file without errors. In development (2014) the CR is not present, however, it is there in Production (2012). Further testing revealed that the use of \n in production (2012) loaded the file fine without the CR but would not load properly in 2014, it threw an error about the row being too long. I also tried the \r and \r\n and both would not work in either environment. Have also tried 0x0d and 0x0a0d and 0x0a0d and also failure encountered with those.
My concern here is that if and when we move to a newer version of Sql Server, some of the existing code using Bulk Insert will fail and there are a lot of load routines that we have. I am also confused why Sql 2012 is picking up the CR but Sql 2014 does not.
If anyone has any suggestions or advice, would greatly appreciate hearing it!
Thanks,
Pat
June 29, 2018 at 12:04 pm
Out of curiosity, have you looked at the files using a hex editor?
June 29, 2018 at 12:23 pm
Yes, that I did. The Row Terminator is a hex 0A.
BTW, I am trying to load the same file in both environments.
Thanks!
Pat
June 29, 2018 at 12:31 pm
That is a Unix file terminated only be <lf> character. Have you tried only using that as the row delimiter?
June 29, 2018 at 12:35 pm
Yes, I can get the file to load in both systems by specifying 0x0a as the row terminator, the issue is that in Sql 2012 it includes the CR and in sql 2014 it does not. Not sure why the difference...
June 29, 2018 at 12:36 pm
patspatio1 - Friday, June 29, 2018 12:35 PMYes, I can get the file to load in both systems by specifying 0x0a as the row terminator, the issue is that in Sql 2012 it includes the CR and in sql 2014 it does not. Not sure why the difference...
So the files are not identical between systems?
June 29, 2018 at 12:37 pm
Sounds like the file was converted to a DOS file when moved between environments.
June 29, 2018 at 12:40 pm
The file is exactly the same on both systems. It's downloaded from the vendor as a csv file, and we test it in dev & test environment, and when successful we transferred the file (via a copy between the 2 servers) and run the bulk insert, that's when the issue occurs.
June 29, 2018 at 12:45 pm
patspatio1 - Friday, June 29, 2018 12:40 PMThe file is exactly the same on both systems. It's downloaded from the vendor as a csv file, and we test it in dev & test environment, and when successful we transferred the file (via a copy between the 2 servers) and run the bulk insert, that's when the issue occurs.
If one has a <cr> and the other doesn't, that tells me that they aren't identical.
Unfortunately we can't verify that for ourselves and have to rely on you to look at the files in both environments with a hex editor.
June 29, 2018 at 12:50 pm
That's what I am trying to say. The same file, verified both on each system, the row terminator in each file is 0x0a using hex editor. Give me a few minutes and I will printscreen each so you can see what I'm saying... I will have to name the file some generic so its not too easily identifiable....
But in summation, I have the same file, in two different systems, both have each row terminated with a 0A In Sql 2012 it brings in the cr, in sql 2014 it doesn't.
June 29, 2018 at 12:59 pm
Ok, well, I have to admit defeat here! When I did look at the file on the sql 2012 side, you are absolutely correct. It now contains the 0d0a for the row terminator and not just the 0a like how the file comes to us with. So now I need to figure out how its getting converted when we transfer the file from server to server.
Thank you for you suggestions and your time!
Pat
June 29, 2018 at 1:12 pm
My pleasure.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply