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.
bcp
. – wBob Jul 27 '16 at 7:51