BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?
Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:

> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?
|||-n did the trrick, thanks.
"bpdee" wrote:
[vbcol=seagreen]
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> -n did the trrick, thanks.
> "bpdee" wrote:

BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:

> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?|||-n did the trrick, thanks.
"bpdee" wrote:
[vbcol=seagreen]
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbser
v%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
>|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
[vbcol=seagreen]
> -n did the trrick, thanks.
> "bpdee" wrote:
>

BCP Unexpected EOF

BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
data has ntext columns that contain XML code. Any ideas?Hi Jeffrey,
Try using -n -N -k -E -o parameters for bcp.
Example:
bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
-n -N -k -E -o <tablename>.out
I hope this helps.
Thanks,
bdee
"Jeffrey K. Ericson" wrote:
> BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> data has ntext columns that contain XML code. Any ideas?|||-n did the trrick, thanks.
"bpdee" wrote:
> Hi Jeffrey,
> Try using -n -N -k -E -o parameters for bcp.
> Example:
> bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> -n -N -k -E -o <tablename>.out
> I hope this helps.
> Thanks,
> bdee
> "Jeffrey K. Ericson" wrote:
> > BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> > data has ntext columns that contain XML code. Any ideas?|||You're welcome and I'm glad that I was able to help you.
"Jeffrey K. Ericson" wrote:
> -n did the trrick, thanks.
> "bpdee" wrote:
> > Hi Jeffrey,
> >
> > Try using -n -N -k -E -o parameters for bcp.
> >
> > Example:
> > bcp %dbname%..<tablename> in Yourfiledata.dat -U%dbuid% -P%dbpwd% -S%dbserv%
> > -n -N -k -E -o <tablename>.out
> >
> > I hope this helps.
> >
> > Thanks,
> > bdee
> >
> > "Jeffrey K. Ericson" wrote:
> >
> > > BCP out works dandy. BCP in fails on unexpected EOF encountered. The BCP
> > > data has ntext columns that contain XML code. Any ideas?

BCP Cannot open database requested in login

I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?
Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:

> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>

BCP Cannot open database requested in login

I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot op
en database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:

> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first porti
on
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>

BCP Cannot open database requested in login

I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>

BCP - Skip the first column in a BCP opperation

How can I skip the first column in a BCP operation. My data file does not contain the data for the first column. From the sample below I want to skip the column named GCRecord.

Table schema [StopOrderCode]

[GCRecord] int NULL,
[Id] int NOT NULL,
[EmployerPayCode] nvarchar(100) NULL,
[EmployerName] nvarchar(100) NULL,
[EnglishEmployerName] nvarchar(100) NULL,
[AfrikaansEmployerName] nvarchar(100) NULL,
[IsGovernmentCode] bit NULL

Format file

9.0
6
1 SQLINT "" 4 "\t" 2 Id ""
2 SQLNCHAR "" 200 "\t" 3 EmployerPayCode ""
3 SQLNCHAR "" 200 "\t" 4 EmployerName Latin1_General_CI_AS
4 SQLNCHAR "" 200 "\t" 5 EnglishEmployerName Latin1_General_CI_AS
5 SQLNCHAR "" 200 "\t" 6 AfrikaansEmployerName Latin1_General_CI_AS
6 SQLBIT "" 1 "\r\n" 7 IsGovernmentCode

Data sample (Tab deliminated)

Id EmployerPayCode EmployerName EnglishEmployerName AfrikaansEmployerName IsGovernmentCode
676 9271 Abakor Bpk Abakor Bpk Abakor Bpk 0
837 9639 Aberdare Telecom Division Aberdare Telecom Division Aberdare Telecom Division 0

BCP statement

bcp DBName.dbo.StopOrderCode in ".\Table Data\StopOrderCode.txt" -S DBServer\InstanceName -T -f ".\Table Data\StopOrderCode.fmt" -C ACP -b 1000 -F 2

I am using sql 2005.

Thanks in advance

When the datafile contains more columns than the table,

bcp into a 'Staging' table that maps to the data file, and then copy appropriate columns to the final table.

When the table has more columns than the datafile,

CREATE a VIEW that maps the data to the table, and bcp into the VIEW.

Bcp

I need to import a CSV file (strings are in quotations) into SQL server using BCP. Some of the fields contain quotations whereas some of them do not. When i try to import of file such as this an error regarding truncation appears and I am not able to import the data.
When I try to import the same file without quotations around the fields it imports correctly. If you have help regarding this it would be greatly appreciated.

"XXX","20040326080100",1040,"121732","100203223","0040227324003"

or

XXX,20040326080100,1040,121732,100203223,004022732 4003

Thanks
DrahosCan they be tab delimited?

The quotes are a pain|||No, actually I receive those delimited files with quoted strings from somewhere.|||Let's see your bcp command?

Did you try and DTS it in?|||I am using this command:

bcp "Received_Data.dbo.XXX" in "Received_CSV.csv" -ffmt_file.fmt -T

Data I am loading is huge and I was recomended to use bcp instead of DTS.
I have tried DTS and it could load the data . There is a choise for 'Text qualifier' - you can choose 'double quoted', so it is OK.

format file is attached

Thanks again
Drahos|||format file is attached|||Is there anyone who can help me with that?|||There's a workaround; tell bcp not to import specific columns. In your case, turn the quotes into columns. The example works for me and on the first column only:

8.0
8
1 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "\"," 1 Col001 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"" 0 dummy SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 25 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 25 "," 3 Col003 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 25 "," 4 Col004 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 25 "," 5 Col005 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 25 "\r\n" 6 Col006 SQL_Latin1_General_CP1_CI_AS|||Is there anyone who can help me with that?

either use PERL/PYTHON to srip the quotes or use DTS. DTS will not be much slower than BCP but DTS will br 100 times easier to use and maintain.

Batch Inserts from DataTable

Please help guys,
I have a DataTable filled from the parsing of a csv file by the OleDb text driver.
This DataTable could on occassion contain in excess of 2000 rows.
I want to be able to batch the inserts to my backend sql table and be able to recorver on errors during the insert.
i.e, maybe send the first 500 rows to insert via an insert dynamic text... really don't know the optimal insert technic to use.
but, if I get an error on say the third batch, I want to be able to recorver, and not have to start all over again and continue the inserts from the batch that failed....
Please help... what is the best way to perform the inserts and how can I track these inserts and recorver on errors like power failures or sql server unavailable etc.
Please help...could I batch the reading of data from a csv file, I mean, if the csv file contains hundreds of thousand of records, how do I read say 500 at a time, using the Microsoft Text Driver - please help...|||

You could run dynamic selects

dim i as int32

for each row in datatable.rows

sql += "insert into table;"
i += 1

if i = 500 then

'do connection and things

i = 0

end if

next

Nick

|||

You could use DTS package to move the CVS file but if you want an alternative, try the links below for options. Hope this helps.

http://www.sqldts.com/?220

http://www.users.drew.edu/skass/sql/TextDriver.htm