BULK INSERT error using 'native' or 'widenative'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BULK INSERT error using 'native' or 'widenative'

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-03 : 16:39:00

I'm having a problem using Bulk Insert with the DATAFILETYPE set to 'native' or 'widenative'. Basically, we're adding additional columns to a large table. In order to have this run fast we BCP the current data out to a file on disk using a temporary views that have 'default' (for lack of a better term) values for the new columns. We then drop all indexes (except the clustered index) on the table, truncate the table, add the new columns via alter table and then attempt to import the file from disk using the BULK INSERT statement. The code goes sort of like this:

1) Drop new columns in case they exist (for rerunning over and over if needed).
2) Drop temporary view if it exists (for rerunning over and over if needed).
3) Create a temporary view over the table we want to BULK INSERT into. This view is defined as:
SELECT *, <default for new column a>, <default for new column b>, <default for new column c>, FROM <table>
4) BCP the data out using xp_cmdshell. The actual command executed is:
BCP <temporary view> out C:\BCPTonyTest.dat -n -S<servername> -U<login> -P<password>
5) Drop temporary view since there is no need for it anymore.
6) Remove all foreign key constraints and indexes off of destination table. The clustered index is left intact.
7) Truncate the destination table.
8) Set the recovery model to BULK_LOGGED.
9) BULK INSERT the data into the destination table usind the following code:
BULK INSERT <db.owner.table> FROM 'C:\BCPTonyTest.dat' WITH (DATAFILETYPE='native', KEEPIDENTITY, KEEPNULLS, TABLOCK, ORDER (InternalGUID), MAXERRORS = 0)
10) Recreate indexes and foreign keys.
11) Set the recovery model back to FULL.

When I run this example, the BULK INSERT fails with the error message "Bulk Insert fails. Column is too long in the data file for row 1, column 120. Make sure the field terminator and row terminator are specified correctly.". My first red flag is that there are not 120 columns in this table, therefore somehow BULK INSERT is not using the same column delimiters as BCP created.
I've tried telling BCP to use specific column and/or row terminators and passing the parameters to BULK INSERT to use the same but no luck. The error message changes slightly (which column is erroring out) but they all state the same error. However, if I do the BCP and BULK INSERT using '-c' and 'char' respectively this works fine. Also, the first column in the table is an UNIQUEIDENTIFIER column if that makes any difference.

Any assistance is greatly appreciated.

Thanks in advance,
Tony

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-03 : 18:17:23

Instead of creating the default for export just export the tabe. Add the new columns with defaults or allow nulls. Crete a view without the new columns. Import into the view. Alter the table to remove the defaults.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-03 : 23:03:50

quote:

Instead of creating the default for export just export the tabe. Add the new columns with defaults or allow nulls. Crete a view without the new columns. Import into the view. Alter the table to remove the defaults.

This suggestion might reduce some of the code, but however, I still have the existing problem, the error caused by importing the data via the bulk insert statement. I'll try this to see if I'm overlooking anything but just in case, any other suggestions? The main thing here is I want to benefit from the speed of exporting and importing using native format.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-03 : 23:38:01

Tony,

The "native" format is very sensitive with table structures. By this I mean the data types for each column have to match exactly. In your case, the "defaults" are probably not cast to any specific data type correct?

eg. Select *, 'D' as ColumnX from YourTable.

What data type is 'D'?
Is it CHAR(1), VARCHAR(1000), NVARCHAR(3) etc...????

Try casting the "defaults" to the correct data type first...

HTH

DavidM

"SQL-3 is an abomination.."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-03 : 23:38:09

quote:

Instead of creating the default for export just export the tabe. Add the new columns with defaults or allow nulls. Crete a view without the new columns. Import into the view. Alter the table to remove the defaults.

Actually, I can't use this method because column defaults can only be a literal constant, system function or null.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-03 : 23:53:01

quote:

Tony,

The "native" format is very sensitive with table structures. By this I mean the data types for each column have to match exactly. In your case, the "defaults" are probably not cast to any specific data type correct?

eg. Select *, 'D' as ColumnX from YourTable.

What data type is 'D'?
Is it CHAR(1), VARCHAR(1000), NVARCHAR(3) etc...????

Try casting the "defaults" to the correct data type first...

Well, this got me somewhere. Now I'm getting the same error but stating row 2, column 1. Heck, I'll just post the whole script here, might make it easier to assist me:

Step 10 is where the problem arises:

--This script will create new columns (Supress, PremiumEffectiveDate,
PremiumTerminationDate)
--to ExtractStage0.dbo.ExtractEmployeeBenefit.

------------
-- STEP 1 --
------------
--Drop new columns if they exist.
PRINT 'STEP 1: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Dropping columns on table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

--Drop the columns if they exist, including defaults.
IF EXISTS (SELECT ID FROM dbo.SYSOBJECTS WHERE NAME = 'DF_ExtractEmployeeBenefit_Suppress' AND OBJECTPROPERTY(ID, 'IsDefaultCnst') = 1)
ALTER TABLE dbo.ExtractEmployeeBenefit DROP CONSTRAINT DF_ExtractEmployeeBenefit_Suppress

IF EXISTS (SELECT ID FROM dbo.SYSCOLUMNS WHERE Name = 'Suppress' AND ID =
OBJECT_ID('ExtractEmployeeBenefit'))
ALTER TABLE dbo.ExtractEmployeeBenefit DROP COLUMN Suppress

IF EXISTS (SELECT ID FROM dbo.SYSCOLUMNS WHERE Name = 'PremiumEffectiveDate' AND ID = OBJECT_ID('ExtractEmployeeBenefit'))
ALTER TABLE dbo.ExtractEmployeeBenefit DROP COLUMN PremiumEffectiveDate

IF EXISTS (SELECT ID FROM dbo.SYSCOLUMNS WHERE Name = 'PremiumTerminationDate' AND ID = OBJECT_ID('ExtractEmployeeBenefit'))
ALTER TABLE dbo.ExtractEmployeeBenefit DROP COLUMN PremiumTerminationDate GO

------------
-- STEP 2 --
------------
--Drop the view if it exists.
PRINT 'STEP 2: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Dropping temporary view over table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE tempdb

IF EXISTS (SELECT ID FROM dbo.SYSOBJECTS WHERE ID =
OBJECT_ID('dbo.vw_tmp_ExtractEmployeeBenefit') AND OBJECTPROPERTY(ID,
'IsView') = 1)
DROP VIEW dbo.vw_tmp_ExtractEmployeeBenefit
GO

------------
-- STEP 3 --
------------
--Create the temporary view with defaults specified for the new columns.
PRINT 'STEP 3: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Creating temporary view with defaults for new columns over table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE tempdb
GO

CREATE VIEW dbo.vw_tmp_ExtractEmployeeBenefit
AS
SELECT TOP 1 --Used top 1 for testing.
/*
InternalGuid, ExtractKey, ExtractNumber, EmployeeKey, ClientBenefitKey, ClientBenefitDescription,
EmployeeEventKey, ClientKey, ClientLookupCode, ClientCarrierKey, ClientCarrierKeyChange,
EmployeeSSN, BenefitTypeLookupCode, EnrollmentNumber, EnrollmentNumberChange, EnrollmentDate,
EnrollmentDateChange, EnrollmentChanged, EnrollmentChangedChange, EnrollmentChangedDate,
EnrollmentChangedDateChange, ClientPlanKey, ClientTierKey, TierKey, RequestedPlanDescription,
RequestedPlanDescriptionChange, RequestedTierDescription, RequestedTierDescriptionChange,
PlanDescription, PlanDescriptionChange, PlanCode, PlanCodeChange, TierDescription,
TierDescriptionChange, TierLookupCode, TierLookupCodeChange, BenefitIneligible,
BenefitIneligibleChange, EligibleDate, EligibleDateChange, CoverageAmount, CoverageAmountChange,
RequestedCoverageAmount, RequestedCoverageAmountChange, MinimumCoverage, MinimumCoverageChange,
MaximumCoverage, MaximumCoverageChange, GIAmount, GIAmountChange, SIAmount, SIAmountChange,
GILevel, GILevelChange, SILevel, SILevelChange, DeductionFrequency, DeductionFrequencyChange,
LumpSumAmount, LumpSumAmountChange, BankedAmount, BankedAmountChange, RequestedSellBackAmount,
RequestedSellBackAmountChange, MaximumSellBackAmount, MaximumSellBackAmountChange, ReductionAmount,
ReductionAmountChange, ReductionAge, ReductionAgeChange, PretaxPremium, PretaxPremiumChange,
RequestedPretaxPremium, RequestedPretaxPremiumChange, PosttaxPremium, PosttaxPremiumChange,
RequestedPosttaxPremium, RequestedPosttaxPremiumChange, RequestedCredits, RequestedCreditsChange,
Credits, CreditsChange, RequestedEmployerContribution, RequestedEmployerContributionChange,
EmployerContribution, EmployerContributionChange, IssueDate, IssueDateChange, EffectiveDate,
EffectiveDateChange, TerminationDate, TerminationDateChange, PlanPractice, PlanPracticeChange,
OBGYN, OBGYNChange, DispositionLookupcode, DispositionLookupcodeChange, OptionalCredit1,
OptionalCredit1Change, OptionalCredit2, OptionalCredit2Change, OptionalCredit3, OptionalCredit3Change,
ReasonDescription, MonthlyGrossPremium, MonthlyGrossPremiumChange, ExtractStatus, RecordChanged,
OnlyDependentBenefitTerm, UserAdded, DateAdded, HostAdded, UserModified, DateModified, HostModified,
ConcurrencyStamp,
*/
*,
CAST(0 AS BIT) AS Supress,
CAST(EffectiveDate AS SMALLDATETIME) AS PremiumEffectiveDate,
CAST(TerminationDate AS SMALLDATETIME) AS PremiumTerminationDate
FROM ExtractStage0.dbo.ExtractEmployeeBenefit
GO

------------
-- STEP 4 --
------------
--BCP the data out.
PRINT 'STEP 4: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Executing BCP utility to export table ExtractStage0.dbo.ExtractEmployeeBenefit to file...'
SET NOCOUNT ON USE tempdb

DECLARE @ReturnCode INT
DECLARE @CommandString NVARCHAR(4000)
DECLARE @DatabaseServer NVARCHAR(128)
DECLARE @LoginName NVARCHAR(128)
DECLARE @Password NVARCHAR(128)

SET @LoginName = 'some varbinary string'
SET @Password = 'some varbinary string'

--Copy data out.
SET @CommandString = 'BCP tempdb.dbo.vw_tmp_ExtractEmployeeBenefit out C:\BCPTonyTest.dat -n -S' + CAST(SERVERPROPERTY('ServerName') AS SYSNAME) + ' -U' + @LoginName + ' -P' + @Password EXEC @ReturnCode = master.dbo.xp_cmdshell @CommandString, NO_OUTPUT
IF (@ReturnCode <> 0)
RAISERROR('BCP failed to copy data out. (ReturnCode: ''%u'').', 15, 1, @ReturnCode)
GO

------------
-- STEP 5 --
------------
--Drop the temporary view now that we are done.
PRINT 'STEP 5: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Dropping temporary view over table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE tempdb

IF EXISTS (SELECT ID FROM dbo.SYSOBJECTS WHERE ID =
OBJECT_ID('dbo.vw_tmp_ExtractEmployeeBenefit') AND OBJECTPROPERTY(ID,
'IsView') = 1)
DROP VIEW dbo.vw_tmp_ExtractEmployeeBenefit
GO

------------
-- STEP 6 --
------------
PRINT 'STEP 6: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Dropping primary key, indexes, foreign key constraints and disabling triggers on table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

IF EXISTS (SELECT ID FROM dbo.SYSOBJECTS WHERE NAME = 'FK_ExtractDependentBenefit_ExtractEmployeeBenefit1' AND OBJECTPROPERTY(ID, 'IsForeignKey') = 1)
ALTER TABLE dbo.ExtractDependentBenefit DROP CONSTRAINT FK_ExtractDependentBenefit_ExtractEmployeeBenefit1

IF EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
DROP INDEX dbo.ExtractEmployeeBenefit.IX_ExtractEmployeeBenefit

IF EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit_1' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
DROP INDEX dbo.ExtractEmployeeBenefit.IX_ExtractEmployeeBenefit_1

IF EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit_2' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
DROP INDEX dbo.ExtractEmployeeBenefit.IX_ExtractEmployeeBenefit_2

ALTER TABLE dbo.ExtractEmployeeBenefit DISABLE TRIGGER ALL
GO

------------
-- STEP 7 --
------------
--Truncate the existing table.
PRINT 'STEP 7: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Truncating table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

TRUNCATE TABLE ExtractStage0.dbo.ExtractEmployeeBenefit
GO

------------
-- STEP 8 --
------------
--Add new columns.
PRINT 'STEP 8: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Adding new columns to table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

--Add the columns.
ALTER TABLE dbo.ExtractEmployeeBenefit
ADD Suppress BIT NOT NULL CONSTRAINT DF_ExtractEmployeeBenefit_Suppress DEFAULT 0,
PremiumEffectiveDate SMALLDATETIME NULL,
PremiumTerminationDate SMALLDATETIME NULL

--Alter columns that do not have defaults to not allow nulls. The ALTER TABLE ADD <column> does not allow NOT NULL specfication when there is not a default.
ALTER TABLE dbo.ExtractEmployeeBenefit ALTER COLUMN PremiumEffectiveDate SMALLDATETIME NOT NULL GO

------------
-- STEP 9 --
------------
PRINT 'STEP 9: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Setting recovery model to bulk logged for database ExtractStage0...'
SET NOCOUNT ON
USE ExtractStage0

ALTER DATABASE ExtractStage0 SET RECOVERY BULK_LOGGED
GO

-------------
-- STEP 10 --
-------------
--Import the data back in.
PRINT 'STEP 10: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Running bulk insert into table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

BULK INSERT ExtractStage0.dbo.ExtractEmployeeBenefit FROM 'C:\BCPTonyTest.dat'
WITH
(
DATAFILETYPE='native',
KEEPIDENTITY,
KEEPNULLS,
TABLOCK,
ORDER (InternalGUID),
MAXERRORS = 0
)
GO

-------------
-- STEP 11 --
-------------
PRINT 'STEP 11: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Adding primary key, indexes, foreign key constraints and enabling triggers on table ExtractStage0.dbo.ExtractEmployeeBenefit...'
SET NOCOUNT ON
USE ExtractStage0

IF NOT EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
CREATE INDEX IX_ExtractEmployeeBenefit ON dbo.ExtractEmployeeBenefit (ExtractKey, EmployeeKey, BenefitTypeLookupCode)

IF NOT EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit_1' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
CREATE UNIQUE INDEX IX_ExtractEmployeeBenefit_1 ON dbo.ExtractEmployeeBenefit (ExtractKey, ExtractNumber, EmployeeSSN, BenefitTypeLookupCode, ExtractStatus, ClientBenefitKey)

IF NOT EXISTS (SELECT ID FROM dbo.SYSINDEXES WHERE NAME = 'IX_ExtractEmployeeBenefit_2' AND ID = OBJECT_ID('dbo.ExtractEmployeeBenefit'))
CREATE INDEX IX_ExtractEmployeeBenefit_2 ON dbo.ExtractEmployeeBenefit (ExtractKey, ExtractNumber, ExtractStatus)

IF NOT EXISTS (SELECT ID FROM dbo.SYSOBJECTS WHERE NAME = 'FK_ExtractDependentBenefit_ExtractEmployeeBenefit1' AND OBJECTPROPERTY(ID, 'IsForeignKey') = 1)
ALTER TABLE dbo.ExtractDependentBenefit
ADD CONSTRAINT FK_ExtractDependentBenefit_ExtractEmployeeBenefit1 FOREIGN KEY
(ExtractEmployeeBenefitGuid) REFERENCES ExtractEmployeeBenefit (InternalGuid) ON UPDATE CASCADE

ALTER TABLE dbo.ExtractEmployeeBenefit ENABLE TRIGGER ALL
GO

-------------
-- STEP 12 --
-------------
PRINT 'STEP 12: ' + CHAR(39) + CONVERT(NVARCHAR(23), GETDATE(), 121) + CHAR(39) + ' Setting recovery model to full...'
SET NOCOUNT ON
USE ExtractStage0

ALTER DATABASE ExtractStage0 SET RECOVERY FULL
GO

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-04 : 03:01:54

Tony,

Can you try 2 things for me?

1) Create the view in the actual database and not tempdb

2) Use BCP for the load instead of BULK INSERT

DavidM

"SQL-3 is an abomination.."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-04 : 06:46:45

quote:

Can you try 2 things for me?

1) Create the view in the actual database and not tempdb

2) Use BCP for the load instead of BULK INSERT

Ok, did those 2 things. Now what happens is the row imports (recall the test view is using a TOP 1) but the last two columns which are small datetime contain incorrect data AND I receive the error from BCP: Unexpected EOF encountered in BCP-data file. I believe I saw this one in the MSKB. I'll check it out when I get to work. The BCP command looks like this:

BCP ExtractStage0.dbo.ExtractEmployeeBenefit in C:\BCPTonyTest.dat -n -E -k -S<server> -U<login> -P<password>

Edited by - tfountain on 02/04/2003 06:49:09

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-04 : 09:59:52

quote:

Tony,

Can you try 2 things for me?

1) Create the view in the actual database and not tempdb

2) Use BCP for the load instead of BULK INSERT

Ok, the only MSKB article I found related to this error was when importing image data, which I have none of. The weird thing is that if I only import 1 row it imports just fine. Anything more than one row generates string truncation errors. The 1 row however has the values for the last two columns (SMALLDATETIME datatypes) wrong. I'll look into why that is so and should lead me to an answer.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-05 : 17:46:58

Ok, I have the problem resolved but I do not understand exactly why so let me bounce my theory off the gurus here. The problem was resolved by moving a line of code AFTER the BCP in (ALTER TABLE dbo.ExtractEmployeeBenefit ALTER COLUMN PremiumEffectiveDate SMALLDATETIME NOT NULL) in STEP 8 (code is posted in prior post). My theory is that some sort of metadata is stored when you export in native format. This new column was actually derived from an existing column for it's default, thus it's "metadata" contained the fact that it allowed nulls. When the column was added and set to not allow nulls there was a conflict of the import data compared to the new column, thus SQL Server not interpretting it correctly.

Ok, how's that for somewhat of a theory? I could be all wrong too!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-05 : 17:54:43

Tony,

Glad you fixed it..

As I said initially, the "native" formats are very sensitive to table structures.

By the way, have you tried it with the BULK INSERT syntax after changing the script?

DavidM

"SQL-3 is an abomination.."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-05 : 19:27:43

Yes, it runs fine now using BULK INSERT. I'd like to understand why it is so touchy. What are the factors regarding such details. However, I've found no such information regarding the internal workings of each datafiletype.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-02-05 : 19:46:23

I can only deduce how it works by a bit of common sense and an assumption...

Assumption:
The word "native" implies that the format and content of the ouput is tightly coupled to SQL Server's internal storage formats. Obviously metadata is stored in the output file that reflects its structure.

Each column data type has its own representation. This seems obvious but is VERY important in regards to variable length strings and precision. eg. CHAR(1) is a completely different type to CHAR(2).
And I would hazard a guess CHAR(1) NOT NULL is completely different to CHAR(1) NULL.

They are my best guesses and have kept me out of any real trouble with BCP native formats.

HTH

DavidM

"SQL-3 is an abomination.."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-05 : 20:04:04

quote:

I can only deduce how it works by a bit of common sense and an assumption...

Assumption:
The word "native" implies that the format and content of the ouput is tightly coupled to SQL Server's internal storage formats. Obviously metadata is stored in the output file that reflects its structure.

Each column data type has its own representation. This seems obvious but is VERY important in regards to variable length strings and precision. eg. CHAR(1) is a completely different type to CHAR(2).
And I would hazard a guess CHAR(1) NOT NULL is completely different to CHAR(1) NULL.

They are my best guesses and have kept me out of any real trouble with BCP native formats.

HTH

DavidM

"SQL-3 is an abomination.."

Well, at least my mindset isn't too far off from someone else's ;).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources