Hi all,

I am getting the ol' EOF encountered error when trying to BCP into a
table using native format. It works great using character format but
unfortunately I need to use native format due to replication
restrictions (trans replication uses bcp behind the scenes and allows
you to specify pre and post scripts if you use native format - doesn't
give you that option for char format).

Anyway here is the schema for the table I am trying to bcp into:

create TABLE [TestMe1]
(
    [ContactId]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
    [DefaultPriceLevelId] [uniqueidentifier] NULL ,
    [CustomerSizeCode] [int] NULL ,
    [CustomerTypeCode] [int] NULL ,
    [PreferredContactMethodCode] [int] NULL ,
    [LeadSourceCode] [int] NULL ,
    [DeletionStateCode] [int] NOT NULL ,
    [OriginatingLeadId] [uniqueidentifier] NULL ,
    [OwningBusinessUnit] [uniqueidentifier] NULL ,
    [OwningUser] [uniqueidentifier] NULL ,
    [PaymentTermsCode] [int] NULL ,
    [ShippingMethodCode] [int] NULL ,
    [OwningTeam] [uniqueidentifier] NULL ,
    [AccountId] [uniqueidentifier] NULL ,
    [ParticipatesInWorkflow] [bit] NULL ,
    [IsBackofficeCustomer] [bit] NULL ,
    [Salutation] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [JobTitle] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [FirstName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Department] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [NickName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [MiddleName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [LastName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Suffix] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
    [YomiFirstName] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
    [FullName] [nvarchar] (160) COLLATE Latin1_General_CI_AS NULL ,
    [YomiMiddleName] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
    [YomiLastName] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
    [Anniversary] [datetime] NULL ,
    [BirthDate] [datetime] NULL ,
    [GovernmentId] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [YomiFullName] [nvarchar] (450) COLLATE Latin1_General_CI_AS NULL ,
    [Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
    [EmployeeId] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [GenderCode] [int] NULL ,
    [AnnualIncome] [money] NULL ,
    [HasChildrenCode] [int] NULL ,
    [EducationCode] [int] NULL ,
    [WebSiteUrl] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    [FamilyStatusCode] [int] NULL ,
    [FtpSiteUrl] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    [EMailAddress1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [SpousesName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [AssistantName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [EMailAddress2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [AssistantPhone] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [EMailAddress3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [DoNotPhone] [bit] NULL ,
    [ManagerName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
    [ManagerPhone] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [DoNotFax] [bit] NULL ,
    [DoNotEMail] [bit] NULL ,
    [DoNotPostalMail] [bit] NULL ,
    [DoNotBulkEMail] [bit] NULL ,
    [DoNotBulkPostalMail] [bit] NULL ,
    [AccountRoleCode] [int] NULL ,
    [TerritoryCode] [int] NULL ,
    [IsPrivate] [bit] NULL ,
    [CreditLimit] [money] NULL ,
    [CreatedOn] [datetime] NULL ,
    [CreditOnHold] [bit] NULL ,
    [CreatedBy] [uniqueidentifier] NULL ,
    [ModifiedOn] [datetime] NULL ,
    [ModifiedBy] [uniqueidentifier] NULL ,
    [SecurityDescriptor] [text] COLLATE Latin1_General_CI_AS NOT NULL ,
    [NumberOfChildren] [int] NULL ,
    [ChildrensNames] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
    [VersionNumber] [timestamp] NULL ,
    [MobilePhone] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Pager] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Telephone1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Telephone2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Telephone3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Fax] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [Aging30] [money] NULL ,
    [StateCode] [int] NOT NULL ,
    [Aging60] [money] NULL ,
    [StatusCode] [int] NULL ,
    [Aging90] [money] NULL ,
    [ParentContactId] [uniqueidentifier] NULL ,
    [CFPContactStatus] [int] NULL ,
    [CFPSalutationPicklist] [int] NULL ,
    [CFMResourceType1] [ntext] COLLATE Latin1_General_CI_AS NULL ,
    [CFPResourceType1] [int] NULL ,
    [CFPResourceType2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I am using this command to load:

bcp <database>.<owner>.testme1 in c:\contact_test.txt -n -U<userID>
-P<passwd> -S<Server>

and here is the contents of the file (in char format) I am trying to
load. Just 2 tab delimited records. Like I said it works in char
mode..just need to get it working in native mode:

A51212DD-1732-40EE-AC59-00055B9FCAC2        1    1    1    1    0        30493560-43B4-4555-B6D9-C6A5BF6C1858    FC9963B1-922B-41D5-A8CC-F7E8B088A0D7        1        23006F23-180C-48BB-B547-B2C30B286C60    0    0    Ms.        Barbara                Flenniken            Flenniken,
Barbara                        ) + coalesce(inserted.YomiFirstName,
                    1    1    http://www.ljmelody.com            barbara.flenni***@ljmelody.com                        0            0    0    0    0    0        1    0        2005-08-14
02:19:44.000    0    BFA45F24-6405-4CB3-AE8E-87398B4139D4    2006-07-13
18:49:41.000    FC9963B1-922B-41D5-A8CC-F7E8B088A0D7    AQAEhBQCAAAwAgAAAAAAABQAAAACAAACDgAAAAACJABXAA0AAQUAAAAAAAUVAAAA            00000000009F9054            (713)
458-7200        (713) 458-7200    (713) 458-7500        0        1
A1DFFF29-B76D-462B-A3DB-00065B2F02CF        1    1    1    1    0        30493560-43B4-4555-B6D9-C6A5BF6C1858    DFE7B269-4172-4E75-8174-FBD6DCF567E6        1        469F1492-80E7-4C27-9DEC-F301B12B0537    0    0    Mr.    Equity
Partner    Duane                Reindl            Reindl, Duane                        ) +
coalesce(inserted.YomiFirstName,
                    1    1                drei***@netdirect.net                        0            0    0    0    0    0        1    0        2005-08-14
01:38:14.000    0    BFA45F24-6405-4CB3-AE8E-87398B4139D4    2006-07-27
18:41:25.000    BFA45F24-6405-4CB3-AE8E-87398B4139D4    AQAEhBQCAAAwAgAAAAAAABQAAAACAAACDgAAAAACJABXAA0AAQUAAAAAAAUVAAAA            0000000000AF8CDD            (317)
253-5083        (317) 253-5083    (317) 253-5084        0        1

ScottC (scarm***@cohenfinancial.com) writes:
And how did you generate the native-format file?

When you specify -n, that means an implicit format file based on the
table definition, and your data file has to obey to that definition.
Unless you are getting data from the same table, you have to use a
format file when you create the BCP file to force the right format.

I recently learn that there are some interest quirks with native format
in conjunction with queryout. Are you using queryout by chance?

Erland Sommarskog wrote:
Show quoteHide quote

Created two tables using the DDL I specified earlier. Called then Test1
and Test2. I bcp'd into Test1 using the -c option and that worked. So I
now have Test1 with 2 rows and Test2 empty. Same table schema. Now I
bcp'd out of Test1 using the -n option. Then I bcp'd that file into
Test2 using the -n option. This is where I got the error. Not using
queryout at all.


Erland Sommarskog wrote:
Show quoteHide quote

ScottC (scarm***@cohenfinancial.com) writes:
One would certainly expect that to work.

Unfortunately, your sample data got mangled in news transport. I tried to
repair, but my bulk file would not load. Could you post the text file as
an attachment instead? While your at it, the binary file could be a good
thing too. I think the best if you put into a zip file.

NEWSEQUINTIALID()
Slow Stored Procedure Execution
Interesting Question
Join??????
sp_recompile for user defined functions