Over the next few days we are going to start the migration to a new forum application. I'm going to start with high post count and active users. You may receive an invitation to the new forums. It's not spam. It's just me trying to seed the user base. My goal is to open it up over the weekend.

Once we open the site we'll block registrations here. We should be open shortly on the new site.

SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
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  

Starting Member

40 Posts

Posted - 10/17/2012 :  01:27:34  Show Profile  Reply with Quote
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

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

Starting Member

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.06 seconds. Powered By: Snitz Forums 2000