October 21, 2014 at 1:15 pm
I really like bcp but it really makes me frustrated, I keep getting error:
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
My data file EmployeeData.csv is attached below, the command I am using is:
bcp AdventureWorks2008.dbo.Employees in C:\Data\EmployeeData.csv -c -t, -S -T
It would be appreciated if any one can help out of this, I have tried saving the data file to the following format:
ANSI/ANSII, UTF-8, UTF-8 No BOM
Thanks in advance.
October 21, 2014 at 1:27 pm
Just some possibilities:
It can depend on the table its getting loaded to and the parameters you have in your bcp command and I don't have adventureworks loaded.
Some other comments, specifically you have "-t ," but I didn't see any commas in the text file, there are a few junk characters on the line containing adventure-works\jos... something, also -c sort of implies that tabs delimit fields in the input file, and in both cases, the number of columns in the text file need to line up with the number of columns in the destination table, etc etc, right now the fields in the text file is delimitted by multiple spaces.
If the text file is a result of a query of the destination table, then each line in the source text file for bcp should look more like:
11,974026903,adventure-works\ovidiu0,0x5AE3,3,Senior Tool Designer,1968-02-18,S,M,2001-01-05,0,7,23,1,F68C7C19-FAC1-438C-9BB7-AC33FCC341C3,2004-07-31 00:00:00.000
and thats if the switch "-t ," is in effect, otherwise, instead of the "t ," and commas in your input file, replace the commas with an actual tab character and let -c specify tab delimiters for your fields.
I FORGOT ONE THING, make sure your last line has the carriage return after it.
October 21, 2014 at 2:20 pm
patrickmcginnis59 10839 (10/21/2014)
Just some possibilities:It can depend on the table its getting loaded to and the parameters you have in your bcp command and I don't have adventureworks loaded.
Some other comments, specifically you have "-t ," but I didn't see any commas in the text file, there are a few junk characters on the line containing adventure-works\jos... something, also -c sort of implies that tabs delimit fields in the input file, and in both cases, the number of columns in the text file need to line up with the number of columns in the destination table, etc etc, right now the fields in the text file is delimitted by multiple spaces.
If the text file is a result of a query of the destination table, then each line in the source text file for bcp should look more like:
11,974026903,adventure-works\ovidiu0,0x5AE3,3,Senior Tool Designer,1968-02-18,S,M,2001-01-05,0,7,23,1,F68C7C19-FAC1-438C-9BB7-AC33FCC341C3,2004-07-31 00:00:00.000
and thats if the switch "-t ," is in effect, otherwise, instead of the "t ," and commas in your input file, replace the commas with an actual tab character and let -c specify tab delimiters for your fields.
I FORGOT ONE THING, make sure your last line has the carriage return after it.
Thank you for you reply, in some cases yes I can modify the source data file prior to the import; however, there are cases that does not allow me to do any change;
I tried your suggestion to remove -t and it throws same error;
I also tried to re-generate the data file and made it delimited using comma "," and include -t in the bcp command, same error
October 21, 2014 at 2:29 pm
You could post the updated data file with commas, and the table definition that you're trying to bcp to, and folks might be able to duplicate your error and troubleshoot it. BCP is pretty darn picky if all the lines in your import file don't exactly match the command line flags and the destination table definition.
Obviously I'm assuming you're using the sample data from adventureworks 😉
October 21, 2014 at 9:09 pm
halifaxdal (10/21/2014)
I really like bcp but it really makes me frustrated, I keep getting error:
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
My data file EmployeeData.csv is attached below, the command I am using is:
bcp AdventureWorks2008.dbo.Employees in C:\Data\EmployeeData.csv -c -t, -S -TIt would be appreciated if any one can help out of this, I have tried saving the data file to the following format:
ANSI/ANSII, UTF-8, UTF-8 No BOM
Thanks in advance.
As Patrick previously posted, there are no commas in the file that you attached. This is NOT a CSV file nor is it a TSV file as it also has no tabs in it. It's a "Fixed Field" file and the best bet for performance would be to build a BCP format file to map the fields. See Books Online for how to do that.
The file also has a corruption at line number 284 (Business Entity ID = 282, also previusly stated) in that there's an extra unwanted character in the Login ID column. There's a further corruption in the last line in the file in that it's just a partial line. BCP correctly warned you of the partial line problem.
The best thing to do would be to...
1. Create and use a BCP format file to map the fixed field columns of this file.
2. Turn on the error handling/sequestration functionality of bad rows in BCP or BULK INSERT. The bad rows (occasional good rows, as well,l if the bad rows cause a bleed over) will be collected in error files where you can edit or ignore them for a future import.
3. Always import to a staging table where you can do data/type validations. Never import to the final table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 22, 2014 at 7:13 am
Thanks Patrick and Jeff.
I recreated the data source and specify comma as delimiter, I've attached the data source file here for your convenience.
The data source file is generated this way from AdventureWorks2008:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2008].[HumanResources].[Employee]
In SSMS, specify output to file and specify using comma as delimiter in query options, when saving, specify .csv as the file extension.
I tried using this to do bcp import:
bcp AdventureWorks2008.dbo.Employees in C:\Data\EmployeeDataCommaDelimited.csv -c -t, -S -T -e c:\data\Error.txt
I got error of unexpected EOF, I checked the data file and removed the last a few lines saying:
(290 row(s) affected)
I still received the same EOF error.
I also checked the LoginID in line 284, I see no difference other than it contains a French letter, and there are more French letter occurrences and I replaced it with English letter, I still receive same error.
I will try format file later and report my finding soon.
Thanks lots.
October 22, 2014 at 7:38 am
Here is my finding using a format file to do the bulk insert:
I generated the format file (attached here for your convenient, please change Employee.fmt.txt back to Employee.fmt) using:
bcp AdventureWorks2008.HumanResources.Employee format nul -T -N -f C:\Data\Employee.fmt
Here is the format file:
10.0
16
1 SQLINT 0 4 "" 1 BusinessEntityID ""
2 SQLNCHAR 2 30 "" 2 NationalIDNumber SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 512 "" 3 LoginID SQL_Latin1_General_CP1_CI_AS
4 SQLUDT 8 0 "" 4 OrganizationNode ""
5 SQLSMALLINT 1 2 "" 5 OrganizationLevel ""
6 SQLNCHAR 2 100 "" 6 JobTitle SQL_Latin1_General_CP1_CI_AS
7 SQLDATE 0 3 "" 7 BirthDate ""
8 SQLNCHAR 2 2 "" 8 MaritalStatus SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 2 2 "" 9 Gender SQL_Latin1_General_CP1_CI_AS
10 SQLDATE 0 3 "" 10 HireDate ""
11 SQLBIT 1 1 "" 11 SalariedFlag ""
12 SQLSMALLINT 0 2 "" 12 VacationHours ""
13 SQLSMALLINT 0 2 "" 13 SickLeaveHours ""
14 SQLBIT 1 1 "" 14 CurrentFlag ""
15 SQLUNIQUEID 1 16 "" 15 rowguid ""
16 SQLDATETIME 0 8 "" 16 ModifiedDate ""
I then use the following bcp command:
bcp AdventureWorks2008.HumanResources.Employee in C:\Data\EmployeeDataCommaDelimited.csv -f C:\Data\Employee.fmt -S -T
(For the convenience, I also attached the csv data file here, please change the file extension by removing the .txt)
I received the same EOF error.
Anything I did wrong?:crazy:
Can any one repeat the export and import using bcp?
Thanks.
October 22, 2014 at 9:27 am
The file you have uploaded called EmployeeDataCommaDelimited.csv.txt looks like it should work with the prior bcp command you first tried, AFTER YOU REMOVE THE FIRST LINE FROM YOUR CSV FILE WHICH LOOKS LIKE COLUMN HEADERS:
bcp AdventureWorks2008.dbo.Employees in C:\Data\EmployeeData.csv -c -t, -S -T
Reading back on what Jeff has posted, the FMT file seems like it would be suitable for FIXED width fields in your import file, and NOT the comma delimitted version.
To repeat: try the bcp line above with your latest csv file AFTER REMOVING THE FIRST LINE!
Anyways, its really really worthwhile to create a simple table with a few columns and try to import files with just a few lines of data to it, using both format files and the "-t," version, and getting some successful imports, just to get some familiarity with the bcp command.
I got it to work by dummying up a table based on your FMT file which listed the columns and going back to the version of the bcp command using "-t," AFTER REMOVING THE FIRST LINE! (it actually even imported the first line when I did not use datetime as a column type, but I suspect your adventureworks table has correct datatypes for its columns so it will fail when it encounteres the included column headers in your csv file).
edit: don't forget about that first line! Also, really pay attention to Jeff about that "staging" table, because importing faulty and erroneous data to production tables with bcp can really ruin your day!
October 22, 2014 at 10:04 am
patrickmcginnis59 10839 (10/22/2014)
The file you have uploaded called EmployeeDataCommaDelimited.csv.txt looks like it should work with the prior bcp command you first tried, AFTER YOU REMOVE THE FIRST LINE FROM YOUR CSV FILE WHICH LOOKS LIKE COLUMN HEADERS:.....
To repeat: try the bcp line above with your latest csv file AFTER REMOVING THE FIRST LINE!
....
It absolutely is not working on my side, here is the output:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Text column data incomplete
BCP copy in failed
This is really making me nut
October 22, 2014 at 10:06 am
PS, I already removed the first line as well as the last line from the data file.
October 22, 2014 at 10:19 am
Can you post the table definition, and the collation of your database? This would make for a closer duplication of your efforts.
October 22, 2014 at 10:35 am
Thank you Patrick!
patrickmcginnis59 10839 (10/22/2014)
Can you post the table definition, and the collation of your database? This would make for a closer duplication of your efforts.
SQL_Latin1_General_CP1_CI_AS
Database: AdventureWorks2008
Table: [HumanResources].[Employee]
USE [AdventureWorks2008]
GO
/****** Object: Table [HumanResources].[Employee] Script Date: 10/22/2014 12:34:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
CREATE TABLE [HumanResources].[Employee](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'BusinessEntityID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique national identification number such as a social security number.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'NationalIDNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Network login.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'LoginID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Where the employee is located in corporate hierarchy.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'OrganizationNode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The depth of the employee in the corporate hierarchy.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'OrganizationLevel'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Work title such as Buyer or Sales Representative.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'JobTitle'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date of birth.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'BirthDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Married, S = Single' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'MaritalStatus'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'M = Male, F = Female' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Gender'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee hired on this date.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'HireDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'SalariedFlag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of available vacation hours.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'VacationHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of available sick leave hours.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'SickLeaveHours'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Inactive, 1 = Active' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'CurrentFlag'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'rowguid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time the record was last updated.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'ModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee information such as salary, department, and title.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key (clustered) constraint' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'PK_Employee_BusinessEntityID'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Person_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [Person].[Person] ([BusinessEntityID])
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [FK_Employee_Person_BusinessEntityID]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Foreign key constraint referencing Person.BusinessEntityID.' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'FK_Employee_Person_BusinessEntityID'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_BirthDate] CHECK (([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_BirthDate]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [BirthDate] >= ''1930-01-01'' AND [BirthDate] <= dateadd(year,(-18),GETDATE())' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_BirthDate'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_Gender] CHECK ((upper([Gender])='F' OR upper([Gender])='M'))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_Gender]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [Gender]=''f'' OR [Gender]=''m'' OR [Gender]=''F'' OR [Gender]=''M''' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_Gender'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_HireDate] CHECK (([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate())))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_HireDate]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [HireDate] >= ''1996-07-01'' AND [HireDate] <= dateadd(day,(1),GETDATE())' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_HireDate'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_MaritalStatus] CHECK ((upper([MaritalStatus])='S' OR upper([MaritalStatus])='M'))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_MaritalStatus]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [MaritalStatus]=''s'' OR [MaritalStatus]=''m'' OR [MaritalStatus]=''S'' OR [MaritalStatus]=''M''' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_MaritalStatus'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_SickLeaveHours] CHECK (([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120)))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_SickLeaveHours]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [SickLeaveHours] >= (0) AND [SickLeaveHours] <= (120)' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_SickLeaveHours'
GO
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_VacationHours] CHECK (([VacationHours]>=(-40) AND [VacationHours]<=(240)))
GO
ALTER TABLE [HumanResources].[Employee] CHECK CONSTRAINT [CK_Employee_VacationHours]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check constraint [VacationHours] >= (-40) AND [VacationHours] <= (240)' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'CK_Employee_VacationHours'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_SalariedFlag] DEFAULT ((1)) FOR [SalariedFlag]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 1 (TRUE)' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_SalariedFlag'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 0' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_VacationHours'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_SickLeaveHours] DEFAULT ((0)) FOR [SickLeaveHours]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 0' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_SickLeaveHours'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_CurrentFlag] DEFAULT ((1)) FOR [CurrentFlag]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 1' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_CurrentFlag'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()) FOR [rowguid]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of NEWID()' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_rowguid'
GO
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of GETDATE()' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_ModifiedDate'
GO
October 22, 2014 at 11:34 am
Well its more complicated than I thought, so much for me trying to help LOL
First off, we need the type "flag".
Can you post that type "flag?" Its in your SSMS explorer pane in programmability -> types -> user-defined data types (or possibly just user-defined types, I don't use types much).
Additionally, as it stands, the hierarchyid and .getlevel types (organizationnode and organizationlevel) didn't work for me with the csv file you provided, however when I changed those both to varchar they worked, I'm not sure how bcp does these two types as I'm not up to snuff on those and haven't found any answers during my first few searches, what we could try is to populate a small table with those types and then bcp export and look at what that produces in a text file.
Also, and this is a funky one, the text file I downloaded from here (and I assume it is downloaded as uploaded), is prefixed with three bytes, "EF BB BF", which is listed on wikipedia as "UTF-8 encoded Unicode byte order mark, commonly seen in text files." Once I eliminated those, I did not get any more errors, but here's the wierd part, not only did I have to delete the header in notepad, I had to ditch the next line too before these three bytes dropped. I don't have any hexeditor at the moment to fix that 🙁 I was able to import them however, so possibly its because during my first attempts I cast businessentityid as a varchar and bcp then sucessfully dropped the first 3 bytes and went to town without errors.
So out of curiousity, how are you producing the csv file?
Also, have to get on something else right now so might not be able to post again until this evening, maybe some real bcp experts here can help out too, I'm especially curious about the hierarchy and .getlevel types with bcp!
October 22, 2014 at 1:07 pm
patrickmcginnis59 10839 (10/22/2014)
Well its more complicated than I thought, so much for me trying to help LOLFirst off, we need the type "flag".
Can you post that type "flag?" Its in your SSMS explorer pane in programmability -> types -> user-defined data types (or possibly just user-defined types, I don't use types much).
Additionally, as it stands, the hierarchyid and .getlevel types (organizationnode and organizationlevel) didn't work for me with the csv file you provided, however when I changed those both to varchar they worked, I'm not sure how bcp does these two types as I'm not up to snuff on those and haven't found any answers during my first few searches, what we could try is to populate a small table with those types and then bcp export and look at what that produces in a text file.
Also, and this is a funky one, the text file I downloaded from here (and I assume it is downloaded as uploaded), is prefixed with three bytes, "EF BB BF", which is listed on wikipedia as "UTF-8 encoded Unicode byte order mark, commonly seen in text files." Once I eliminated those, I did not get any more errors, but here's the wierd part, not only did I have to delete the header in notepad, I had to ditch the next line too before these three bytes dropped. I don't have any hexeditor at the moment to fix that 🙁 I was able to import them however, so possibly its because during my first attempts I cast businessentityid as a varchar and bcp then sucessfully dropped the first 3 bytes and went to town without errors.
So out of curiousity, how are you producing the csv file?
Also, have to get on something else right now so might not be able to post again until this evening, maybe some real bcp experts here can help out too, I'm especially curious about the hierarchy and .getlevel types with bcp!
Thank you Patrick.
The Flag is bit, not null;
The three bytes are called BOM (byte order mark), as the csv is saved as UTF-8, The UTF-8 representation of the BOM is the byte sequence 0xEF,0xBB,0xBF. Here is the entry from wiki
The procedure I used to create the csv file:
1. In SSMS->Query->Query Options, changed in Result->Text, output format: Comma Delimited
2. In SSMS->Query->Results to, set to Result to file
3. Select all rows from [AdventureWorks2008].[HumanResources].[Employee] and execute it
4. When prompt for file to save, Save As type: All files (*.*), file name: EmployeeDataCommaDelimited.csv
5. Save with Encoding: UTF-8
6. The csv is generated with comma delimited, I removed the column header line and the last lines showing (290 row(s) affected)
I have an editor that allows me to save as "UTF-No BOM", I used a second tool to verify the three bytes are removed indeed.
Here is the HexEditor I use to verify the BOM has been removed: HxD Hex Editor
Thanks
October 22, 2014 at 1:18 pm
Found a few more issues with the process...
-- first to create a test
create table testtree
(
id1 varchar(10),
orgnode hierarchyid,
orglevel as ([orgnode].[GetLevel]())
)
-- build some rows
insert into testtree select '001',hierarchyid::GetRoot()
insert into testtree select '002',(SELECT orgnode FROM testtree WHERE id1 = '001').GetDescendant(null,null)
insert into testtree select '003',(SELECT orgnode FROM testtree WHERE id1 = '002').GetDescendant(null,null)
Now, bcp out from this table:
bcp testdb.dbo.testtree out testtree1.txt -c -t, -S "testserver\testinstance" -T
Now if you look at the output from that:
C:\Users\patrickmcginnis59\Documents>type testtree1.txt
001, ,0
002,58,1
003,5AC0,2
Notice this is as opposed to what you are trying to import as a column value to a hierarchical field:
11,974026903,adventure-works\ovidiu0,0x5AE3,3,Senior Tool De...
12,480168528,adventure-works\thierry0,0x5AE358,4,Tool Design...
13,486228782,adventure-works\janice0,0x5AE368,4,Tool Designe...
Well lets try the 0x prefix then??!?!?!
Interesting, when I edit my original file that I bcp'ed OUT, theres an invisible value there!
001,nul,0
002,58,1
003,5ac0,2
And the "nul" is inverted in color when viewed with notepad++. Fair enough, it imported ok without the 0x and included that strange "nul" (most likely character 0). Lets give it the 0x and see what happens????
C:\Users\patrickmcginnis59\Documents>bcp testdb.dbo.testtree in testtree2.txt -c -t, -S "testserver\testinstance" -T
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Text column data incomplete
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Text column data incomplete
So I'm guessing that we can't prefix the value "0x" onto the hexcodes representing hierarchial ids but that also looks to be what happened in your text file.
My suggestion at this point is to bcp out your file first, and use that as a reference to bcp'ing back in data. Does that make any sense?
edit: I didn't prefix the invisible "nul" with 0x in my test, so above looks like only two of the lines failed, we get two error messages per line. ALSO it really looks like .GetLevel is actually a computed field, but it didn't block my simplistic import.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply