pcb out file datetime format

#41

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#42

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#43

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#44

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#45

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#46

Harolds (harolds (AT) nospam (DOT) nospam) writes:

Quote:

I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#47

SQL SERVER 2005

The same thing happens when doing an out as with queryout.

Format:
9.0
8
1 SQLUNIQUEID 1 16 "" 1 ActivityID
""
2 SQLDATETIME 0 8 "" 2 DateTime
""
3 SQLCHAR 2 100 "" 3 PageName
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "" 4 Querystring
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 4 0 "" 5 FormVariables
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 4 0 "" 6 SessionVariables
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 4 0 "" 7 ServerVariables
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 100 "" 8 CustomValue
SQL_Latin1_General_CP1_CI_AS

Create Table:
/****** Object: Table [dbo].[ActivityDetailBackup200809] Script Date:
09/04/2008 15:36:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Activity200809](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) NULL,
[Querystring] [varchar](255) NULL,
[FormVariables] [text] NULL,
[SessionVariables] [text] NULL,
[ServerVariables] [text] NULL,
[CustomValue] [varchar](100) NULL,
CONSTRAINT [ActivityIDDate200809] PRIMARY KEY NONCLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Activity200809] WITH CHECK ADD CHECK
(([DateTime]>=CONVERT([datetime],'09/01/2008',(0)) AND
[DateTime]<CONVERT([datetime],'10/01/2008',(0))))
GO

--
Harolds

"Erland Sommarskog" wrote:

Quote:

Harolds (harolds (AT) nospam (DOT) nospam) writes:
I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#48

SQL SERVER 2005

The same thing happens when doing an out as with queryout.

Format:
9.0
8
1 SQLUNIQUEID 1 16 "" 1 ActivityID
""
2 SQLDATETIME 0 8 "" 2 DateTime
""
3 SQLCHAR 2 100 "" 3 PageName
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "" 4 Querystring
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 4 0 "" 5 FormVariables
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 4 0 "" 6 SessionVariables
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 4 0 "" 7 ServerVariables
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 100 "" 8 CustomValue
SQL_Latin1_General_CP1_CI_AS

Create Table:
/****** Object: Table [dbo].[ActivityDetailBackup200809] Script Date:
09/04/2008 15:36:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Activity200809](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) NULL,
[Querystring] [varchar](255) NULL,
[FormVariables] [text] NULL,
[SessionVariables] [text] NULL,
[ServerVariables] [text] NULL,
[CustomValue] [varchar](100) NULL,
CONSTRAINT [ActivityIDDate200809] PRIMARY KEY NONCLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Activity200809] WITH CHECK ADD CHECK
(([DateTime]>=CONVERT([datetime],'09/01/2008',(0)) AND
[DateTime]<CONVERT([datetime],'10/01/2008',(0))))
GO

--
Harolds

"Erland Sommarskog" wrote:

Quote:

Harolds (harolds (AT) nospam (DOT) nospam) writes:
I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#49

SQL SERVER 2005

The same thing happens when doing an out as with queryout.

Format:
9.0
8
1 SQLUNIQUEID 1 16 "" 1 ActivityID
""
2 SQLDATETIME 0 8 "" 2 DateTime
""
3 SQLCHAR 2 100 "" 3 PageName
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "" 4 Querystring
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 4 0 "" 5 FormVariables
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 4 0 "" 6 SessionVariables
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 4 0 "" 7 ServerVariables
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 100 "" 8 CustomValue
SQL_Latin1_General_CP1_CI_AS

Create Table:
/****** Object: Table [dbo].[ActivityDetailBackup200809] Script Date:
09/04/2008 15:36:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Activity200809](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) NULL,
[Querystring] [varchar](255) NULL,
[FormVariables] [text] NULL,
[SessionVariables] [text] NULL,
[ServerVariables] [text] NULL,
[CustomValue] [varchar](100) NULL,
CONSTRAINT [ActivityIDDate200809] PRIMARY KEY NONCLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Activity200809] WITH CHECK ADD CHECK
(([DateTime]>=CONVERT([datetime],'09/01/2008',(0)) AND
[DateTime]<CONVERT([datetime],'10/01/2008',(0))))
GO

--
Harolds

"Erland Sommarskog" wrote:

Quote:

Harolds (harolds (AT) nospam (DOT) nospam) writes:
I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#50

SQL SERVER 2005

The same thing happens when doing an out as with queryout.

Format:
9.0
8
1 SQLUNIQUEID 1 16 "" 1 ActivityID
""
2 SQLDATETIME 0 8 "" 2 DateTime
""
3 SQLCHAR 2 100 "" 3 PageName
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "" 4 Querystring
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 4 0 "" 5 FormVariables
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 4 0 "" 6 SessionVariables
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 4 0 "" 7 ServerVariables
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 100 "" 8 CustomValue
SQL_Latin1_General_CP1_CI_AS

Create Table:
/****** Object: Table [dbo].[ActivityDetailBackup200809] Script Date:
09/04/2008 15:36:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Activity200809](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) NULL,
[Querystring] [varchar](255) NULL,
[FormVariables] [text] NULL,
[SessionVariables] [text] NULL,
[ServerVariables] [text] NULL,
[CustomValue] [varchar](100) NULL,
CONSTRAINT [ActivityIDDate200809] PRIMARY KEY NONCLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Activity200809] WITH CHECK ADD CHECK
(([DateTime]>=CONVERT([datetime],'09/01/2008',(0)) AND
[DateTime]<CONVERT([datetime],'10/01/2008',(0))))
GO

--
Harolds

"Erland Sommarskog" wrote:

Quote:

Harolds (harolds (AT) nospam (DOT) nospam) writes:
I used you bcp out on a table in my db, and then tried bcp in as well as
your bulk select and both returned an error.

bcp out:
bcp "select top 1000 * from activity.dbo.activity200807" queryout
f:\testdata.dat -n -T

format:
bcp "activity.dbo.activity200807" format nul -n -f f:\testdata.fmt -T

select:
SELECT a.* FROM OPENROWSET(BULK N'f:\TestData.dat',FORMATFILE
='f:\testdata.fmt') as a

error for bulk select:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row
20, column 6. Verify that the field terminator and row terminator are
specified correctly.

Could you post the CREATE TABLE statement for the table, and the format
file?

What happens if you specify the table as source instead of the query?

If your able to track down the row that causes a problem, could you
give an INSERT statement for it?

Please also specify which version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx