SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 To bcp out a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arthiasha
Starting Member

India
40 Posts

Posted - 10/17/2012 :  01:27:34  Show Profile  Reply with Quote
Hi,
I need to do bcp out of a table which does not hold any data. i need to take the columns alone and store it as a .fmt file.

C:\inetpub\wwwroot\db>bcp temp out .\datafiles\temp.dat -S ACER-PC\SQLEXPRESS -U sa -P sa

i get the error as
SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'temp'.

I'm able to do bcp for all other tables in the db with the same path but not for this table.
please help me

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/17/2012 :  06:45:27  Show Profile  Reply with Quote
You don't need to have any data in the table to generate a format file. To generate a format file you need to specify the format keyword. So it should be something like this:
bcp YourDatabaseName.dbo.temp format nul -f .\datafiles\temp.dat -S ACER-PC\SQLEXPRESS -U sa -P sa
But, couple of things:

1. The above statement will generate the format file in plain text format. Usually I prefer XML - it is easier to read and edit.

2. I almost always also specify the -c option so I don't have to provide the format information for each column while running the bcp command.

So, I would prefer:
bcp YourDatabaseName.dbo.temp format nul -f .\datafiles\temp.dat -S ACER-PC\SQLEXPRESS -U sa -P sa -c -x 
Go to Top of Page

arthiasha
Starting Member

India
40 Posts

Posted - 10/17/2012 :  08:17:13  Show Profile  Reply with Quote
thank u so much it worked out
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000