It is better to specify your table structure as DDL such as:
CREATE TABLE AGR ( AGR_AgreementType Int,
CONSTRAINT PK_AGR PRIMARY KEY ( AGR_AgreementType, AGR_EffectiveDate )
With BCP specifying -T and the -U and -P parameters is not a combination to
Your problem with using
bcp myDatabaseName..AGR FORMAT a.txt -SmyServer\myInstance T -t***EOL***
is that your data has line ending ***EOL******EOF*** which implies and
extra column in your table.
You could add this column, or use a format file such as (this may wrap!
colations should be at the end of the line!!)
1 SQLCHAR 0 12 "***EOL***" 1 AGR_AgreementType ""
2 SQLCHAR 0 24 "***EOL***" 2 AGR_EffectiveDate ""
3 SQLCHAR 0 0 "***EOL***" 3 AGR_AgreementText SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "***EOL***" 4 AGR_Enabled ""
5 SQLCHAR 0 12 "***EOL***" 5 AGR_AgreementID ""
6 SQLCHAR 0 0 "***EOF***" 0 dummy SQL_Latin1_General_CP1_CI_AS
if the format file is called agreement.fmt then to run this use the command
(this may also wraps!)
bcp myDatabaseName..AGR IN agreement.txt -SmyServer\myInstance -T
> It turns out you can't specify a unique non-printable ascii value as a
> delimiter. But, you can specify a unique phrase for up to 10
> characters to replace the default end-of-field and end-of-row
> delimiters using the -t and -r BCP operators.
> I tried running BCP to import my text:
> The BCP command I used is:
> bcp myDatabaseName.dbo.AGR in agreement.txt -SmyServer\myInstance -
> Ulogin -Ppassword -t ***EOL*** -r ***EOF*** -T -c
> I am using the administrator account on the SQL server machine which
> has ownership of all databases.
> The table being written into is: AGR
> with the following layout: AGR_AgreementType Int (pk)
> AGR_Enabled bit
> AGR_AgreementID int
> The text being written into the database record comes from
> It contains:
> 1***EOL***02/14/2008***EOL***This is line one of text
> This is line two of text
> This is line three of text
> This is last line of text***EOL***1***EOL***1***EOL******EOF***
> BCP reported the following:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
> Could this be because of the AGR_Enabled bit field because I'm passing
> in 1?
> >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP