Prev: SQL Server cluster failover slow problem
Next: Spurious data being inserted in field
From: Mitchell_Collen via SQLMonster.com on 21 Jul 2010 12:18
Hi SQL Monsters
I am having an issue using the format file that I generated using bcp. I am
using SQL Server 2005. I cannot figure out why it doesn't want to import
anything in based on its own bcp formatted file. Please look at what I am
doing below and see if you see anything that stands out. I am guessing there
is a version issue with using its own file but not sure.
Thanks in advance! MC

This creates the format file
bcp dbo.testCSV format nul -T -n -f c:\csv\myformattest2-n.fmt-U myusername -
P mypassword

I run this command to use the format file:
bcp dbo.testCSV IN c:\csv\testCSV.csv -f c:\csv\myformattest2-n.fmt -U
myusername -P mypassword

This is the error message:
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

The format file contents:
9.0
4
1 SQLNCHAR 2 120 "" 1 f1
SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 120 "" 2 f2
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 120 "" 3 f3
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 120 "" 4 f4
SQL_Latin1_General_CP1_CI_AS

--
Misty :-)

Message posted via http://www.sqlmonster.com

From: Erland Sommarskog on 21 Jul 2010 17:51
Mitchell_Collen via SQLMonster.com (u33726(a)uwe) writes:
> I am having an issue using the format file that I generated using bcp. I
> am using SQL Server 2005. I cannot figure out why it doesn't want to
> import anything in based on its own bcp formatted file. Please look at
> what I am doing below and see if you see anything that stands out. I am
> guessing there is a version issue with using its own file but not sure.
> Thanks in advance! MC
>
> This creates the format file
> bcp dbo.testCSV format nul -T -n -f c:\csv\myformattest2-n.fmt-U myusername - P mypassword

I don't know if it's just a problem with the formatting of the
post, but here the file is name .fmt-U at the end.

> I run this command to use the format file:
> bcp dbo.testCSV IN c:\csv\testCSV.csv -f c:\csv\myformattest2-n.fmt -U
> myusername -P mypassword
>
> This is the error message:
> Starting copy...
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

Very helpful message, indeed.

Did you at some point edit the file?

I search on Google, and I found
http://forums.databasejournal.com/showthread.php?t=18211

By the way, this format file (when you get it working) will produce a
native file. That's as far from a CSV as it can be.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx

From: Mitchell_Collen via SQLMonster.com on 22 Jul 2010 12:28
Thanks for your help. I added an additional quote shown below but it didn't
work - same error again.

9.0
4
1 SQLNCHAR 2 120 "" 1 f1
SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 2 120 "" 2 f2
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 120 "" 3 f3
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 120 "" 4 f4 < I added an additional
quote but still getting error. :(
SQL_Latin1_General_CP1_CI_AS

--
Misty :-)

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1

From: Erland Sommarskog on 22 Jul 2010 16:27
Mitchell_Collen via SQLMonster.com (u33726(a)uwe) writes:
> Thanks for your help. I added an additional quote shown below but it
> didn't work - same error again.

So you are editing the file yourself? How do you save it? Make sure that
it is saved as an ANSI file and not a Unicode file.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL Server cluster failover slow problem
Next: Spurious data being inserted in field