BCP in having I/O error

Hi,
I have data coming from mainframes. In sysbase the bcp in works fine using the fromat file however, when i convert the format file for sql compatibility it get the error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file.
Below are the format of my files used in sybase and sql. Please do let me know what could be the cause
Sybase fmt file:
10.0
10
1SYBCHAR0 9""1a
2SYBCHAR06""2b
3SYBCHAR020""3c
4SYBCHAR015""4d
5SYBCHAR 01""5e
6SYBCHAR 0 3""6f
7SYBCHAR 01""7g
8SYBCHAR 08""8h
9 SYBCHAR 0 6 "" 9 i
10 SYBCHAR 0 1 "\n" 15 j
SQL Format file:
8.0
10
1SQLCHAR0 9""1a
2SQLCHAR06""2b
3SQLCHAR 020""3c
4SQLCHAR 015""4d
5SQLCHAR 01""5e
6SQLCHAR 0 3""6f
7SQLCHAR01""7g
8SQLCHAR 08""8h
9 SQLCHAR 0 6 "" 9 i
10 SQLCHAR 0 1 "\n" 15 j
Does someone know why am i getting this error.
Thanks.
Arzan,
The SQL Server format file specification includes an additional column
for the collation. Perhaps you are getting this error because you've
left that column out. Also be sure there is a carriage return at the
end of the final line of the file.
Steve Kass
Drew University
Arzan wrote:

>Hi,
>I have data coming from mainframes. In sysbase the bcp in works fine using the fromat file however, when i convert the format file for sql compatibility it get the error
>SQLState = S1000, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file.
>Below are the format of my files used in sybase and sql. Please do let me know what could be the cause
>Sybase fmt file:
>10.0
>10
>1SYBCHAR0 9""1a
>2SYBCHAR06""2b
>3SYBCHAR020""3c
>4SYBCHAR015""4d
>5SYBCHAR 01""5e
>6SYBCHAR 0 3""6f
>7SYBCHAR 01""7g
>8SYBCHAR 08""8h
>9 SYBCHAR 0 6 "" 9 i
>10 SYBCHAR 0 1 "\n" 15 j
>SQL Format file:
>8.0
>10
>1SQLCHAR0 9""1a
>2SQLCHAR06""2b
>3SQLCHAR 020""3c
>4SQLCHAR 015""4d
>5SQLCHAR 01""5e
>6SQLCHAR 0 3""6f
>7SQLCHAR01""7g
>8SQLCHAR 08""8h
>9 SQLCHAR 0 6 "" 9 i
>10 SQLCHAR 0 1 "\n" 15 j
>Does someone know why am i getting this error.
>Thanks.
>
|||Hi Steve,
Now i get the error as below:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Any clue on this.
Thanks.
"Steve Kass" wrote:

> Arzan,
> The SQL Server format file specification includes an additional column
> for the collation. Perhaps you are getting this error because you've
> left that column out. Also be sure there is a carriage return at the
> end of the final line of the file.
> Steve Kass
> Drew University
> Arzan wrote:
>
|||Arzan,
That sounds like a mismatch between the format file and the data -
have you verified that the lengths of the fields in the data file are
correct? Another possibility is that your data is Unicode, in which
case you'll want SQLNCHAR instead of SQLCHAR.
SK
Arzan wrote:
[vbcol=seagreen]
>Hi Steve,
>Now i get the error as below:
>SQLState = 22001, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>SQLState = 22001, NativeError = 0
>Any clue on this.
>Thanks.
>"Steve Kass" wrote:
>

BCP in having I/O error

Hi,
I have data coming from mainframes. In sysbase the bcp in works fine using t
he fromat file however, when i convert the format file for sql compatibility
it get the error
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP format file.
Below are the format of my files used in sybase and sql. Please do let me kn
ow what could be the cause
Sybase fmt file:
10.0
10
1 SYBCHAR 0 9 "" 1 a
2 SYBCHAR 0 6 "" 2 b
3 SYBCHAR 0 20 "" 3 c
4 SYBCHAR 0 15 "" 4 d
5 SYBCHAR 0 1 "" 5 e
6 SYBCHAR 0 3 "" 6 f
7 SYBCHAR 0 1 "" 7 g
8 SYBCHAR 0 8 "" 8 h
9 SYBCHAR 0 6 "" 9
i
10 SYBCHAR 0 1 "\n" 15
j
SQL Format file:
8.0
10
1 SQLCHAR 0 9 "" 1 a
2 SQLCHAR 0 6 "" 2 b
3 SQLCHAR 0 20 "" 3 c
4 SQLCHAR 0 15 "" 4 d
5 SQLCHAR 0 1 "" 5 e
6 SQLCHAR 0 3 "" 6 f
7 SQLCHAR 0 1 "" 7 g
8 SQLCHAR 0 8 "" 8 h
9 SQLCHAR 0 6 "" 9
i
10 SQLCHAR 0 1 "\n" 15
j
Does someone know why am i getting this error.
Thanks.Arzan,
The SQL Server format file specification includes an additional column
for the collation. Perhaps you are getting this error because you've
left that column out. Also be sure there is a carriage return at the
end of the final line of the file.
Steve Kass
Drew University
Arzan wrote:

>Hi,
>I have data coming from mainframes. In sysbase the bcp in works fine using
the fromat file however, when i convert the format file for sql compatibilit
y it get the error
>SQLState = S1000, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP format file.
>Below are the format of my files used in sybase and sql. Please do let me k
now what could be the cause
>Sybase fmt file:
>10.0
>10
>1 SYBCHAR 0 9 "" 1 a
>2 SYBCHAR 0 6 "" 2 b
>3 SYBCHAR 0 20 "" 3 c
>4 SYBCHAR 0 15 "" 4 d
>5 SYBCHAR 0 1 "" 5 e
>6 SYBCHAR 0 3 "" 6 f
>7 SYBCHAR 0 1 "" 7 g
>8 SYBCHAR 0 8 "" 8 h
>9 SYBCHAR 0 6 "" 9
i
>10 SYBCHAR 0 1 "\n" 15
j
>SQL Format file:
>8.0
>10
>1 SQLCHAR 0 9 "" 1 a
>2 SQLCHAR 0 6 "" 2 b
>3 SQLCHAR 0 20 "" 3 c
>4 SQLCHAR 0 15 "" 4 d
>5 SQLCHAR 0 1 "" 5 e
>6 SQLCHAR 0 3 "" 6 f
>7 SQLCHAR 0 1 "" 7 g
>8 SQLCHAR 0 8 "" 8 h
>9 SQLCHAR 0 6 "" 9
i
>10 SQLCHAR 0 1 "\n" 15
j
>Does someone know why am i getting this error.
>Thanks.
>|||Hi Steve,
Now i get the error as below:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right trunca
tion
SQLState = 22001, NativeError = 0
Any clue on this.
Thanks.
"Steve Kass" wrote:

> Arzan,
> The SQL Server format file specification includes an additional column
> for the collation. Perhaps you are getting this error because you've
> left that column out. Also be sure there is a carriage return at the
> end of the final line of the file.
> Steve Kass
> Drew University
> Arzan wrote:
>
>|||Arzan,
That sounds like a mismatch between the format file and the data -
have you verified that the lengths of the fields in the data file are
correct? Another possibility is that your data is Unicode, in which
case you'll want SQLNCHAR instead of SQLCHAR.
SK
Arzan wrote:
[vbcol=seagreen]
>Hi Steve,
>Now i get the error as below:
>SQLState = 22001, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]String data, right trunc
ation
>SQLState = 22001, NativeError = 0
>Any clue on this.
>Thanks.
>"Steve Kass" wrote:
>
>