bulk insert and openrowset

i'm having the strangest problem right now and can't seem to figure out
what's going on- here's the deal:

i'm trying to use the openrowset command, in combination with the bulk
insert command to load data from a text file into sql server. the text file
im using has a field that can have a length when i execute the following, i
receive a "Bulk load data conversion error (truncation) ":

INSERT INTO tTempImport
SELECT *
FROM OPENROWSET(
BULK 'E:\temp\myimportfile.txt',
FORMATFILE = 'E:\temp\myformatfile.fmt'
) as t1


however, this works just fine:

BULK INSERT tTempImport
FROM 'E:\temp\myimportfile.txt'
WITH (FORMATFILE = 'E:\temp\myformatfile.fmt')


** the field in the import file that is causing the error has a length of
13988 characters
** the "Bulk load data conversion error (truncation) " error has nothing to
do with the insert, as i get the same thing when just running the select *
from openrowset... portion

*** so what is it about the select * from openrowset command, that is
causing sql server to do some sort of conversion on the data in the text
file???

--========================================================
here are the contents of my format file:
--========================================================
9.0
8
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "|" 2 col2
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "|" 3 col3
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "|" 4 col4
SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "|" 5 col5
SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "|" 6 col6
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "|" 7 col7
SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 "|\"\r\n" 8 col8
SQL_Latin1_General_CP1_CI_AS


--========================================================
--here is the table definition for tTempImport:
--========================================================
CREATE TABLE [dbo].[tTempImport](
[id] [int] IDENTITY(1,1) NOT NULL,
[col2] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col4] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col6] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col7] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col8] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tTempImport] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


TIA!!!
jt

Did you enable the openrowset option in the Surface Area Configuration for
Features?

"JTL" wrote:

> i'm having the strangest problem right now and can't seem to figure out
> what's going on- here's the deal:
>
> i'm trying to use the openrowset command, in combination with the bulk
> insert command to load data from a text file into sql server. the text file
> im using has a field that can have a length when i execute the following, i
> receive a "Bulk load data conversion error (truncation) ":
>
> INSERT INTO tTempImport
> SELECT *
> FROM OPENROWSET(
> BULK 'E:\temp\myimportfile.txt',
> FORMATFILE = 'E:\temp\myformatfile.fmt'
> ) as t1
>
>
> however, this works just fine:
>
> BULK INSERT tTempImport
> FROM 'E:\temp\myimportfile.txt'
> WITH (FORMATFILE = 'E:\temp\myformatfile.fmt')
>
>
> ** the field in the import file that is causing the error has a length of
> 13988 characters
> ** the "Bulk load data conversion error (truncation) " error has nothing to
> do with the insert, as i get the same thing when just running the select *
> from openrowset... portion
>
> *** so what is it about the select * from openrowset command, that is
> causing sql server to do some sort of conversion on the data in the text
> file???
>
> --========================================================
> here are the contents of my format file:
> --========================================================
> 9.0
> 8
> 1 SQLCHAR 0 0 "\"" 0 "" ""
> 2 SQLCHAR 0 0 "|" 2 col2
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 0 "|" 3 col3
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLCHAR 0 0 "|" 4 col4
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLCHAR 0 0 "|" 5 col5
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 0 "|" 6 col6
> SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 0 "|" 7 col7
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 0 "|\"\r\n" 8 col8
> SQL_Latin1_General_CP1_CI_AS
>
>
> --========================================================
> --here is the table definition for tTempImport:
> --========================================================
> CREATE TABLE [dbo].[tTempImport](
> [id] [int] IDENTITY(1,1) NOT NULL,
> [col2] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col4] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col6] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col7] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [col8] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> CONSTRAINT [PK_tTempImport] PRIMARY KEY CLUSTERED
> (
> [id] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> TIA!!!
> jt
>
>
>
>
>