I'm trying to BCP from a csv file to a table, both have 134 columns. The error message coming back from BCP "SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '1'."
By reducing the number of columns in both csv file and table to around 20 column , the bcp call will work and the data will load into the table.Here's a sample that shows the problem
The command line I'm using to call bcp:-
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" em.dbo.mytable1 in c:\temp\_rrs\datafile.csv -S SQL_SERVER_NAME -U username -P password -t "|" -c -F 2
The version of BCP this is using :- 8.00.194 on SQL Server 2000.
Sample data in datafile.csv
| 1|Position|BBPLC|20060927|ALL|RDPBB092701.DAT||ALL |
| 1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|1 |
| 2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2|2 |
| 3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3|3 |
Creation script for table
if exists (select * from dbo.sysobjects where id = object_id(N'[mytable1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [mytable1]
GO
CREATE TABLE [mytable1] (
[1][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[2][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[3][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[4][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[5][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[7][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[9][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[10][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[11][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[12][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[13][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[14][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[15][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[16][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[17][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[18][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[19][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[20][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[21][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[22][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[23][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[24][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[25][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[26][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[27][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[28][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[29][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[30][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[31][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[32][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[33][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[34][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[35][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[36][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[37][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[38][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[39][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[40][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[41][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[42][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[43][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[44][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[45][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[46][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[47][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[48][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[49][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[50][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[51][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[52][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[53][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[54][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[55][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[56][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[57][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[58][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[59][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[60][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[61][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[62][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[63][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[64][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[65][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[66][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[67][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[68][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[69][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[70][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[71][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[72][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[73][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[74][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[75][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[76][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[77][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[78][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[79][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[80][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[81][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[82][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[83][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[84][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[85][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[86][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[87][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[88][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[89][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[90][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[91][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[92][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[93][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[94][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[95][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[96][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[97][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[98][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[99][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[100][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[101][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[102][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[103][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[104][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[105][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[106][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[107][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[108][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[109][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[110][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[111][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[112][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[113][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[114][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[115][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[116][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[117][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[118][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[119][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[120][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[121][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[122][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[123][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[124][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[125][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[126][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[127][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[128][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[129][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[130][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[131][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[132][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[133][varchar](6) COLLATE Latin1_General_CI_AS NULL ,
[134][varchar](6) COLLATE Latin1_General_CI_AS NULL
) ON [em_Group1]
GO
Any pointers on this would be very much appreciated
Regards
RichardS71
Eureka!
Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table.
没有评论:
发表评论