1

I am using BCP as follows,

bcp "SELECT * FROM [dbo].[GpsPoint] where id in (551043, 551044, 551045)" -n

This works 100%. I have omitted the rest of it because it is just server, user, password settings etc.

If I change the query to this,

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

With a case statement the BCP import gives this,

Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid date format
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

The contactNo field is an INT NOT NULL in both source and destination, so I cannot see why translating the value using case then casting it to INT is not working?

My BCP import looks like this (minus the server, user, password etc),

bcp GpsPoint IN "c:\temp\GPSTracking\gpspoint.dat" -n

UPDATE:

If I change the query to this it works,

SELECT id, queueid, 8022 as contactNo, recordtime, hardwareid, easting, northing FROM [dbo].[GpsPoint]

So it is definitely the case statement. And the issue does not resolve if I use ELSE 0 in the case statement. On my actual query I have a where clause (the where clause on the very first query above) which selects only 3 records as a proof of concept, and they all have 8022 as the value. The ELSE 0 never gets used if I add it and I still get the same error.

  • I would also recommend using a view. You can inspect it, make sure it works before using with bcp. – wBob Jul 27 '16 at 7:51
  • I could try a view, but it will likely be very slow as I am dealing with a very large number of records, but that is a different story. – peter Jul 27 '16 at 21:21
  • I might just scale it back in complexity and make it select 1234 as ContactNo where contactNo = 8022 and generate a whole bunch of statements like that, each one does a single translation, and union them, or run them separately. – peter Jul 27 '16 at 21:31
2

As you mentioned the contactNo is INT NOT NULL, in your CASE statement currently you are handling only for the value 8022 and not for the remaining values. If you add the ELSE 0 in the case statement it will solve the issue. It will select 0 for the entries other than contactNo = 8022

So the working code will be:

SELECT id, queueid, CAST((CASE contactNo WHEN 8022 THEN 1234 ELSE 0 END) AS INT) as contactNo, recordtime, hardwareid, easting, northing 
FROM [dbo].[GpsPoint]

More explanation:

CASE contactNo WHEN 8022 THEN 1234 END this statement will return 1234 for the contactNo is 8022 and for the remaining entries it return only NULL.
So the CAST(NULL AS INT) also returns NULL only.
So inserting the NULL value into the NOT NULL value causing the error.

  • Thanks, but this is not the problem. I am selecting a subset of data where all the values are 8022 as a proof of concept, only 3 records (look at the very first query above, you can see the where clause I am using to select only 3 records). I tried it just in case, but it didn't help. – peter Jul 27 '16 at 21:20
  • Try adding a where clause to select only records WHERE contactNo = 8022 – CoveGeek Aug 1 '16 at 5:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.