Error Creating format file for bcp

Question

Post reply

Error Creating format file for bcp

EIAnd

Right there with Babe

Points: 743
More actions
June 10, 2008 at 10:02 am

#192070

Hello,
I want to instert a .txt file into a table in my database and i want to use bulk insert but i have an error when i am creating a non XML format file, im using sql server 2005 and i am following this page
http://technet.microsoft.com/es-es/library/ms178129.aspx
i have create mi file myTestFormatFiles.Fmt whit the info that apear
9.0
4
1 SQLCHAR 0 7 "," 1 Col1 ""
2 SQLCHAR 0 100 "," 2 Col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r" 4 Col4 SQL_Latin1_General_CP1_CI_AS
the error apear when i execute this instruction in the command of windows:
C:\>bcp Ejemplo1..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.fmt -
T
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Inva
lid Instance()).
thanks for any help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ksullivan SSCrazy Points: 2346 More actions · Answer 1

Is C: the sql server's C: or one on your pc?

If xp_cmdshell is enabled try

master..xp_cmdshell 'bcp Ejemplo1..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.fmt -T'

There should not be a space between - and T at the end.

Try adding the servername

bcp Ejemplo1..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.fmt -T -SMyServerName

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

For those that want to follow along in English...

http://msdn.microsoft.com/en-us/library/ms178129.aspx

There are many problems with the command that you have submitted. First, do you have a table already available called MyTestFormatFiles in the database called Ejemplo1 ?

Also, what are you trying to do with the command? Are you trying to create a format file for a given table?

Also, unless you are using Windows Authentication, you cannot use the -T (trusted connection) parameter from the CmdPrompt. Instead, you would have to use the User (-U) name and Password (-P) parameters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

GreenTea SSC Journeyman Points: 98 More actions · Answer 3

what is your server name?

should it be in bcp command?

bcp.... -S server name.

EIAnd Right there with Babe Points: 743 More actions · Answer 4

thanks for all the help..

i have solute my problem now

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}

{in | out | queryout | format} data_file

[-mmax_errors] [-fformat_file] [-x] [-eerr_file]

[-Ffirst_row] [-Llast_row] [-bbatch_size]

[-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]

[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]

[-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]

[-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]

[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]

i need to put the server name [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]

thanks

MegaZord SSC Enthusiast Points: 159 More actions · Answer 5

Hi,

Thanks for reading this and hope you can help me.

Tried this:

bcp AdventureWorks.dbo.HumanResources.myTeam format nul -c -t , -f \\SomeServer\SomeFolder\myTeam.Fmt -Sserver_name [MySQLServer\MyInstance] -Ulogin_id Mylogin -Ppassword MyPassword -T

it error out with this:

bcp: unknown option s

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n native type] [-c character type] [-w wide character type]

[-N keep non-text native] [-V file format version] [-q quoted identifier]

[-C code page specifier] [-t field terminator] [-r row terminator]

[-i inputfile] [-o outfile] [-a packetsize]

[-S server name] [-U username] [-P password]

[-T trusted connection] [-v version] [-R regional enable]

[-k keep null values] [-E keep identity values]

[-h "load hints"]

so, changed statement to this:

bcp AdventureWorks.dbo.HumanResources.myTeam format nul -c -t , -f \\SomeServer\SomeFolder\myTeam.Fmt -S [MySQLServer\MyInstance] -U Mylogin -P MyPassword -T

and this time I got this err:

SQLState = 08001, NativeError = 17

Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.SQLState = 01000, NativeError = 53

Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()

).

Is there something wrong with the statement and I'm dbo on the local database. What other rights should I have?

TIA!

ksullivan SSCrazy Points: 2346 More actions · Answer 6

Try it without the square brackets around the server name\instance.

Also, -U -P and -T options are redundant. Use either -U with -P or -T by itself (although you can use all of them at the same time and get a result).

If your login works, being dbo should be sufficient.

bcp AdventureWorks.dbo.HumanResources.myTeam format nul -c -t , -f \\SomeServer\SomeFolder\myTeam.Fmt -S MySQLServer\MyInstance -T

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

allan_au (6/30/2009)
Hi,
Thanks for reading this and hope you can help me.
Tried this:
bcp AdventureWorks.dbo.HumanResources.myTeam format nul -c -t , -f \\SomeServer\SomeFolder\myTeam.Fmt -Sserver_name [MySQLServer\MyInstance] -Ulogin_id Mylogin -Ppassword MyPassword -T
it error out with this:
bcp: unknown option s
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
so, changed statement to this:
bcp AdventureWorks.dbo.HumanResources.myTeam format nul -c -t , -f \\SomeServer\SomeFolder\myTeam.Fmt -S [MySQLServer\MyInstance] -U Mylogin -P MyPassword -T
and this time I got this err:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()
).
Is there something wrong with the statement and I'm dbo on the local database. What other rights should I have?
TIA!

What does the 4 part name of "AdventureWorks.dbo.HumanResources.myTeam " refer to? It looks like a column rather than a table. I believe this should be just "AdventureWorks.HumanResources.myTeam" without the DBO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Also, the following parameter of:

-S MySQLServer\MyInstance

...should actually be"

-S"MySQLServer\MyInstance"

... because of the special character in the instance name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MegaZord SSC Enthusiast Points: 159 More actions · Answer 9

MegaZord

SSC Enthusiast

Points: 159

July 1, 2009 at 11:02 am

#1018592

Thanks for your help.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

You bet... thank you for the feedback. Please don't hesitate to post back if you continue to have problems.... we can bring some guns to bear on these types of problems especially since you're also trying to help yourself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions