BCP XML Format File

Question

Post reply

BCP XML Format File

Kevin R Hurst

Mr or Mrs. 500

Points: 522
More actions
March 7, 2006 at 2:02 pm

#171360

Has anyone had any luck with using BCP to create the XML based Format File? I have tried to follow the instructions here but I get an error even using the same exact sample they have (I have the AdventureWorks database sample installed):
bcp AdventureWorks.HumanResources.Department format nul -x -f Department-f-n-x.Xml -n -T
cmd prompt error: unknown option x

Viewing 7 posts - 1 through 6 (of 6 total)

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

peterhe SSChampion Points: 11469 More actions · Answer 1

You have -f-n-x in the format file name. Use quotes around the file name:

bcp AdventureWorks.HumanResources.Department format nul -x -f "Department-f-n-x.Xml" -n -T

Or use a simple file name:

bcp AdventureWorks.HumanResources.Department format nul -x -f Department.Xml -n -T

Kevin R Hurst Mr or Mrs. 500 Points: 522 More actions · Answer 2

the problem actually is that bcp does not recognize the "-x" switch which is the example from MSDN

I just didn't know what the example was getting at

peterhe SSChampion Points: 11469 More actions · Answer 3

That's not the issue. The issue is you have -x as part of the file name. As I said, if you use quotes around the file name, it will work. Or use a simple file name like myformat.xml

Bcp can recoginize the -x and run both the commands in my previous post successfully.

What edition and compatible level you set for the server?

Kevin R Hurst Mr or Mrs. 500 Points: 522 More actions · Answer 4

edition: SQL 2005 Express

compatibility level: not sure I understand what this means

When I run bcp, it gives me an error (cmd prompt error: unknown option x) for the -x switch regardless if the file name is in quotes or not. Running bcp \? also does not list "x" as a legal switch.

Dick Campbell SSChasing Mays Points: 647 More actions · Answer 5

If you have both SQL 2000 and SQL 2005 installed (as I have) you might be running the SQL 2000 bcp. Try typing bcp /? and see if -x appears amongst the options.

The SQL 2005 version can be found in C:\Program Files\Microsoft SQL Server\90\Tools\Binn and seems to work as advertised.

As it seems to be recommmend that SQL 2000 be the default instance when you install both, I suppose it's logical that the SQL 2000 instance of bcp is the one that runs unless you specify otherwise. I am not sure that I agree but it might explain what is happening.

Dick Campbell

Kevin R Hurst Mr or Mrs. 500 Points: 522 More actions · Answer 6

Dick,

You are a freaking genius! I should have thought about having Express & 2000 on the same machine would not necessarily make bcp bcp.

Thanks!